如何把.TXT文件读入到SQL数据库中?

主题：如何把.TXT文件读入到SQL数据库中?

海上土拨鼠 [专家分：60] 发布于 2005-10-11 11:30:00

这是VB6里面的语句:
Private Sub CmdData_Click()
Dim Count, Fsum As Integer

Comm1.Filter = "TEXT FILES(*.TXT)|*.TXT"
Comm1.FilterIndex = 2
Comm1.ShowOpen

FileName = Comm1.FileName
If FileName = "" Then Exit Sub
    Open FileName For Input As #1
        Do While Not EOF(1)
            Line Input #1, SumS
            Count = Count + 1 '统计文本的行数
        Loop
    Close #1

    Open FileName For Input As #1
    Do While Not EOF(1)
        Input #1, CARD_NO, CARD_DATE, CARD_CLOCK, CARD_FALG             '抓出文本里的数据
        CARD_NO = "000000" & CARD_NO                                      '因为CARD_NO抓出来是数字型的,所以在前面加上4个0


        txtSQL = "SELECT * FROM CLOCK_CARD"
        Set mrc = ExecuteSql_CARD(txtSQL, MsgText)
        '读入数据
            mrc.AddNew
            mrc!CARD_NO = Right(CARD_NO, 8)                            '写入数据库时,抓右边10个字符
            mrc!CARD_NO_NEW = Right(CARD_NO, 10)
            mrc!CARD_DATE = Left(CARD_DATE, 4) & "-" & Mid(CARD_DATE, 5, 2) & "-" & Mid(CARD_DATE, 7, 2)
            mrc!CARD_TIME = Mid(CARD_DATE, 9, 2) & ":" & Mid(CARD_DATE, 11, 2)
            mrc!CARD_CLOCK = Right("00" & CARD_CLOCK, 3)
            mrc!CARD_FLAG = CARD_FALG

            mrc.Update
        Me.Caption = "正在处理...." & Fsum & "/" & (Count - 1)
        Fsum = Fsum + 1
    Loop
    Close #1
    Me.Caption = "打卡资料处理"

    '把名字和卡号关联起来
        txtSQL = "UPDATE CLOCK_CARD INNER JOIN EMPLOYEE ON CLOCK_CARD.CARD_NO_NEW=EMPLOYEE.card_id SET CLOCK_CARD.CARD_ID = EMPLOYEE.EMPL_ID"
        Set mrc1 = ExecuteSql_CARD(txtSQL, MsgText)

    '把处理好的TXT文件备份起来
    Dim FSO As New FileSystemObject, Fill
    Set Fill = FSO.GetFile(FileName)
    Fill.Move ("C:\CARD\BACKUP\" & Right(Comm1.FileName, 12))

MsgBox "处理完成"
End Sub

我对上面的语句用VB.NET进行了修改,变成这样,可是再下去我就不会了,请高手老师帮我修改好吗,谢谢
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class FrmMain
    Inherits System.Windows.Forms.Form

    Public txtSQL As String
    Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
        Dim strData As String
        Dim myStream As Stream
        Dim openFileDialog1 As New OpenFileDialog

        openFileDialog1.InitialDirectory = "c:\CARD\BACKUP\"
        openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
        openFileDialog1.FilterIndex = 2
        openFileDialog1.RestoreDirectory = True

        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            myStream = openFileDialog1.OpenFile()
            If Not (myStream Is Nothing) Then
                ' Insert code to read the stream here.
                Dim objreader As StreamReader = New StreamReader(myStream)
                strData = objreader.ReadToEnd()

                objreader.Close()
                objreader = Nothing
                myStream.Close()
            End If
        End If

    End Sub
End Class
[em1][em1]

本帖地址： http://bbs.pfan.cn/post/111603.html

回复列表（共6个回复）

沙发

海上土拨鼠 [专家分：60] 发布于 2005-10-11 14:27:00

Imports System.IO
Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class FrmMain
    Inherits System.Windows.Forms.Form
    Public txtSQL As String
    Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
        Try
            Dim strData As String
            Dim myStream As Stream
            Dim openFileDialog1 As New OpenFileDialog
            Dim CARD_NO, CARD_DATE, CARD_CLOCK, CARD_FLAG As String

            openFileDialog1.InitialDirectory = "c:\CARD\BACKUP\"
            openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
            openFileDialog1.FilterIndex = 2
            openFileDialog1.RestoreDirectory = True

            If openFileDialog1.ShowDialog() = DialogResult.OK Then
                myStream = openFileDialog1.OpenFile()
                If Not (myStream Is Nothing) Then
                    ' Insert code to read the stream here.
                    Dim objreader As StreamReader = New StreamReader(myStream)
                    'strData = objreader.ReadToEnd()
                    Dim x As String
                    While objreader.Peek <> -1
                        x = objreader.ReadLine

                        If myCon.State = ConnectionState.Closed Then
                            myCon.Open()
                        End If
                        myCmd.CommandText = "select * from CLOCK_CARD"
                        Console.WriteLine(x)
                        myCmd.Connection = myCon    '等价于 Dim MyCom As New SqlClient.SqlCommand(SQL, mycon)
                        myCmd.ExecuteNonQuery()
                        MyDa.SelectCommand = myCmd
                        myDataSet.Clear()
                        MyDa.Fill(myDataSet)

                        Dim row As DataRow = myDataSet.Tables(0).NewRow
                        CARD_NO = "00" & CARD_NO
                        row!CARD_NO = Microsoft.VisualBasic.Right(CARD_NO, 8)                            '写入数据库时,抓右边10个字符
                        row!CARD_NO_NEW = Microsoft.VisualBasic.Right(CARD_NO, 10)
                        ' row!CARD_DATE = Microsoft.VisualBasic.Left(CARD_DATE, 4) & "-" & Microsoft.VisualBasic.Mid(CARD_DATE, 5, 2) & "-" & Microsoft.VisualBasic.Mid(CARD_DATE, 7, 2)
                        row!CARD_TIME = Mid(CARD_DATE, 9, 2) & ":" & Mid(CARD_DATE, 11, 2)
                        row!CARD_CLOCK = Microsoft.VisualBasic.Right("00" & CARD_CLOCK, 3)
                        row!CARD_FLAG = CARD_FLAG

                        myDataSet.Tables(0).Rows.Add(row)
                        Dim myBuilder As New SqlClient.SqlCommandBuilder(MyDa)

                        MyDa.Update(myDataSet)
                    End While
                    Console.WriteLine("资料处理完毕……！")
                    objreader.Close()
                    objreader = Nothing
                    myStream.Close()
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(Err.Description)
        End Try


    End Sub
End Class

到了这边就不会了,不知道怎么读到SQL里面去

板凳

海上土拨鼠 [专家分：60] 发布于 2005-10-11 16:16:00

这是.TXT文件:
0021560857            20051009172949    001    0
0017644904            20051009172950    001    0
0017635334            20051009172952    001    0
0021568748            20051009172954    001    0
0005095552            20051009173115    001    0
0021568048            20051009173116    001    0
0009019139            20051009180301    001    0
0021555988            20051009180302    001    0
0011851774            20051009180313    001    0
0011859329            20051009180314    001    0
0011860130            20051009180316    001    0
0005427008            20051009180425    001    0
0017638991            20051009180429    001    0
0017638991            20051009180432    001    0
0017635192            20051009180441    001    0
0017650148            20051009180442    001    0
0006940519            20051010065953    001    0

3 楼

海上土拨鼠 [专家分：60] 发布于 2005-10-12 08:54:00

Dim row As DataRow = myDataSet.Tables(0).NewRow
                        'CARD_NO = "00" & CARD_NO
                        'row!CARD_NO = Microsoft.VisualBasic.Right(CARD_NO, 8)                            '写入数据库时,抓右边10个字符
                        row!CARD_NO_NEW = Microsoft.VisualBasic.Mid(x, 1, 10)
                        ' row!CARD_DATE = Microsoft.VisualBasic.Mid(x, 33, 4) & "-" & Microsoft.VisualBasic.Mid(x, 37, 2) & "-" & Microsoft.VisualBasic.Mid(x, 39, 2)
                        row!CARD_TIME = Mid(x, 41, 2) & ":" & Mid(x, 43, 2)
                        row!CARD_CLOCK = Microsoft.VisualBasic.Mid(x, 49, 3)
                        'row!CARD_FLAG = CARD_FLAG
到了这边只能抓到第一列的数据，怎么回事，要怎么改呢？

4 楼

海上土拨鼠 [专家分：60] 发布于 2005-10-12 16:31:00

现在可以把数据读到SQL了，那怎么把名字和卡号关联起来呢？

Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
        Try
            'Dim strData As String
            Dim myStream As Stream
            Dim openFileDialog1 As New OpenFileDialog
            openFileDialog1.InitialDirectory = "c:\CARD\BACKUP\"
            openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
            openFileDialog1.FilterIndex = 2
            openFileDialog1.RestoreDirectory = True

            If openFileDialog1.ShowDialog() = DialogResult.OK Then
                myStream = openFileDialog1.OpenFile()
                If Not (myStream Is Nothing) Then
                    ' Insert code to read the stream here.
                    Dim objreader As StreamReader = New StreamReader(myStream)
                    'strData = objreader.ReadToEnd()
                    Dim x As String
                    While objreader.Peek <> -1
                        x = objreader.ReadLine

                        If myCon.State = ConnectionState.Closed Then
                            myCon.Open()
                        End If
                        myCmd.CommandText = "select * from CLOCK_CARD"
                        Console.WriteLine(x)
                        myCmd.Connection = myCon
                        myCmd.ExecuteNonQuery()
                        MyDa.SelectCommand = myCmd
                        myDataSet.Clear()
                        MyDa.Fill(myDataSet)

                        Dim row As DataRow = myDataSet.Tables(0).NewRow
                        row!CARD_NO_NEW = x.Substring(0, 10)
                        row!CARD_DATE = x.Substring(13, 4) & "-" & x.Substring(17, 2) & "-" & x.Substring(19, 2)
                        row!CARD_TIME = x.Substring(21, 2) & ":" & x.Substring(23, 2)
                        row!CARD_CLOCK = x.Substring(28, 3)

                        myDataSet.Tables(0).Rows.Add(row)
                        Dim myBuilder As New SqlClient.SqlCommandBuilder(MyDa)
                        MyDa.Update(myDataSet)
                    End While
                    'Console.WriteLine("资料处理完毕……！")
                    objreader.Close()
                    objreader = Nothing
                    myStream.Close()

                End If
                MsgBox("资料处理完毕……！")
            End If
        Catch ex As Exception
            MessageBox.Show(Err.Description)
        End Try
    End Sub
End Class

5 楼

海上土拨鼠 [专家分：60] 发布于 2005-10-21 10:09:00

请问：
如何在这段代码之后把C：\CARD\BACKUP里面的文件移动到D：\BACKUP呢？
请教高手代码！！！！！急急急

6 楼

海上土拨鼠 [专家分：60] 发布于 2005-10-21 10:12:00

代码如下：
Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
        Try
            Dim row1 As DataRow
            Dim DS As New DataSet
            Dim myStream As Stream
            Dim GH As String

            Dim openFileDialog1 As New OpenFileDialog
            openFileDialog1.InitialDirectory = "c:\CARD\BACKUP\"
            openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
            openFileDialog1.FilterIndex = 2
            openFileDialog1.RestoreDirectory = True

            If openFileDialog1.ShowDialog() = DialogResult.OK Then

                myStream = openFileDialog1.OpenFile()
                If Not (myStream Is Nothing) Then
                    Dim objreader As StreamReader = New StreamReader(myStream)
                    Dim x As String
                    If myCon.State = ConnectionState.Closed Then
                        myCon.Open()
                    End If
                    While objreader.Peek <> -1
                        Label1.Visible = True
                        x = objreader.ReadLine
                        '把工号与卡号关联起来
                        myCmd.CommandText = "SELECT EMPL_ID FROM EMPLOYEE WHERE (card_id = N'" & x.Substring(0, 10) & "')"
                        myCmd.Connection = myCon
                        myCmd.ExecuteNonQuery()
                        MyDa.SelectCommand = myCmd
                        MyDa.Fill(DS)
                        Console.WriteLine(DS.Tables(0).Rows.Count)
                        If DS.Tables(0).Rows.Count > 0 Then
                            For Each row1 In DS.Tables(0).Rows
                                GH = row1!EMPL_ID & ""
                            Next
                            DS.Clear()
                        End If
                        '把采集的数据放到SQL数据库中
                        myCmd.CommandText = "INSERT INTO CLOCK_CARD(card_no_new, card_date_time, card_clock, card_flag, empl_id) " & _
                                            " VALUES (N'" & x.Substring(0, 10) & "', '" & x.Substring(13, 4) & "-" & x.Substring(17, 2) & "-" & x.Substring(19, 2) & " " & _
                                            x.Substring(21, 2) & ":" & x.Substring(23, 2) & ":" & x.Substring(25, 2) & "', N'" & x.Substring(28, 3) & "', N'" & x.Substring(32, 1) & "', N'" & GH & "')"
                        Console.WriteLine(myCmd.CommandText)
                        myCmd.Connection = myCon
                        myCmd.ExecuteNonQuery()
                        MyDa.SelectCommand = myCmd
                    End While
                    objreader.Close()
                    objreader = Nothing
                    myStream.Close()

                End If
                MsgBox("资料处理完毕……！")
            End If
Catch ex As Exception
            MessageBox.Show(Err.Description & ex.ToString)
        End Try

我来回复

您尚未登录，请登录后再回复。点此登录或注册

主题：如何把.TXT文件读入到SQL数据库中?

回复列表（共6个回复）

我来回复

程序员工具箱 new

代码片段

本版新帖

主题：如何把.TXT文件读入到SQL数据库中?

回复列表 （共6个回复）

我来回复

程序员工具箱 new

代码片段

本版新帖

回复列表（共6个回复）