Read parcial text file into SQL table

[aspfun]

I got a project to read a text file in which there are about 5000 records. Below is a sample of data file.

@@@,35,20120527,WQ,1900.38*RRR,1K,300507861*RRR,D9, 090499904949*RRR,BLT,111*DDD,472,RD8,20130821

@@@,220,20131020,QQ,454*RRR,1K,300507862*RRR,D9, 010947700046*RRR,BLT,131*DDD,472,RD8,20131021

1) each record start with "@@@"

2) each column separate by ","

3) my project only need to read first 4 columns. That is, if see first "*" then go next record.

For the sample text above, I need to read

35,20120527,WQ,1900.38

220,20131020,QQ,454

Can some expert help me?

My big problem is how to code to "Go next record when see the first '*' "? (get rid of the rest and start new line)

[dday9]

Try doing this:

''' <summary>
''' Converts a text file to a data table.
''' </summary>
''' <param name="path">The path where the file is located.</param>
''' <returns>DataTable</returns>
Private Function Txt_to_Data(ByVal path As String) As DataTable
    'The DataTable to return
    Dim dt As DataTable = New DataTable

    'Add the first four columns
    dt.Columns.AddRange({New DataColumn, New DataColumn, New DataColumn, New DataColumn})

    'Read all the lines in the file
    For Each line As String In IO.File.ReadAllLines(path)
        'Get each columns
        Dim columns() As String = line.Split({","}, StringSplitOptions.RemoveEmptyEntries)

        'Declare a new row
        Dim r As DataRow = dt.NewRow

        'Only add the first four columns(skipping the @@@)
        For x As Integer = 1 To 4
            'Set the value of the cell
            r(x - 1) = columns(x)
        Next

        'Add the row
        dt.Rows.Add(r)
    Next

    'Return the table
    Return dt
End Function

[Pyth007]

I believe one more bit of parsing is needed:

        'Only add the first four columns(skipping the @@@)
        For x As Integer = 1 To 4
            'Set the value of the cell
            r(x) = columns(x).Split({"*"}, StringSplitOptions.RemoveEmptyEntries)(0)
        Next

dday's suggestion would include the * as well as the following data as the last entry...

[aspfun]

I tested it but got an error said "can not find column 4"

[aspfun]

I modified as below (adding one more column in range)it works but first column is " ".

Dim dt As New System.Data.DataTable()

'Add the first four columns
dt.Columns.AddRange({New DataColumn, New DataColumn, New DataColumn, New DataColumn, New DataColumn})

'Read all the lines in the file
For Each line As String In IO.File.ReadAllLines(myTextFilePath)
'Get each columns
Dim columns() As String = line.Split({","}, StringSplitOptions.RemoveEmptyEntries)

'Declare a new row
Dim r As DataRow = dt.NewRow

'Only add the first four columns(skipping the @@@)
For x As Integer = 1 To 4
'Set the value of the cell
r(x) = columns(x).Split({"*"}, StringSplitOptions.RemoveEmptyEntries)(0)

Next

'Add the row
dt.Rows.Add(r)
Next

DataGridView1.DataSource = dt

[dday9]

I tested it but got an error said "can not find column 4"

That was my fault. I was looping from 1 - 4 and I forgot to subtract one to account for the 0 based index. I've edited the code.

[techgnome]

Search the forums... there's a thread I particiapated in that might be of value. The discussion surrousnded using TextFieldParser to read text data, parse it into a datatable, from which you could then use to populate a table in the database. I find that method better than using split, becaue it can do it a little more intelligently.. you open a stream to the file, feed it to the parser, and it spits back an array for each .Read you ask it to do... if you're only interested in the first 4, then you copy only the 4 elements you want to where ever else you want.

I wish I could remember who the OP on those threads were, but there was some sample code where I showed a stripped down version of a process I'm currently using to generically process files.

-tg