reading 100 rows at a time in a text file

if i want to process a 40000 record csv file 100 records(rows) at a time,
is there a way to load the first 100 records into an array, do something with them,
then read 101 - 200 in and so forth all the while keeping track of where i am in the csv file, until all rows have been processed.?
mstucchiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AutoeformsCommented:
goto the www.connectionstrings.com

there you find a string for opening your file and then you can read it into a dataset and process the records 1 at a time

greg
0
VBRocksCommented:
Here you are.  Just drop a Button on your form, insert this code, change the file path, customize a sub, and you are on your way:


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strFilePath As String = "c:\temp\Myfile.txt"    '*** Enter your file path here
        Dim sr As New System.IO.StreamReader(strFilePath)   'Open the file for reading
        Dim inLine As String    'Store the current line from the file
        Dim iLineCount As String = 1    'Count the number of lines in the file.
        Dim dt As New DataTable()       'Create a new DataTable
        Dim row As DataRow              'Create a DataRow
        Dim bEOF As Boolean             'Create an End Of File flag.

        'Read the first line from the file.
        inLine = sr.ReadLine

        'Add columns to the datatable (no data) - Assuming your 1st line has all the columns in it.
        For i As Integer = 0 To inLine.Split(",").Length - 1
            dt.Columns.Add(New DataColumn())

        Next

        'Loop until the end of the file
        Do Until sr.EndOfStream = True

            'Update the End Of File flag
            bEOF = sr.EndOfStream

            'Create a new DataRow
            row = dt.NewRow()

            'Since row is comma separated, add it as an array to the row
            row.ItemArray = inLine.Split(",")

            'Add row to rows collection
            dt.Rows.Add(row)

            'If the LineCount is evenly divisible by 100 (with no remainder), or you are at the end of the file
            '    then do something with your records
            If iLineCount Mod 100 = 0 OrElse _
                bEOF = True Then

                'Do something with your 100 records
                DoSomethingWithRecords(dt)

            End If   'If iLineCount Mod 1000 = 0

            'If we are not at the end of the file...
            If bEOF = False Then
                'Read another line
                inLine = sr.ReadLine

                'Increment Counter
                iLineCount += 1

            End If  'bEOF

        Loop     'Do Until sr.EndOfStream = True

        'Cleanup
        sr.Close()
        sr.Dispose()

    End Sub     'Button1_Click


    'Customize this sub for the handling of your 100 records.
    Private Sub DoSomethingWithRecords(ByRef dt As DataTable)
        'For each datarow in the table
        For Each dr As DataRow In dt.Rows

            'For each column in the row
            For i As Integer = 0 To dt.Columns.Count - 1

                'write out the data in every column in the row
                Console.WriteLine(dr.Item(i))

            Next     'For i As Integer = 0
        Next     'For Each dr As DataRow

        'Remove all rows from the table
        dt.Rows.Clear()

    End Sub     'DoSomethingWithRecords

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mstucchiAuthor Commented:
this is pretty awesome, what if the csv file has no header or column info in the first row?
this data table is an in memory construct, isnt it?
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

mstucchiAuthor Commented:
sorry for being dense, i almost get it  now, i really only need the 1 st element (column) of that 100 element data array,
and i could reference it by its index, couldnt i, i want to then process those 100 1 st elements as query parameters for this 40K record csv file, which i would pass into your dosomething sub.
0
VBRocksCommented:
Hi.  If the first line has column headers in it, then you can read the line with the column headers, and use those headers to create your columns (if you do, then you'll have to replace the code above with this).

        Dim sColumnName as String

        'Read the first line from the file.
        inLine = sr.ReadLine

        'Add columns to the datatable (no data) - Assuming your 1st line has all the columns in it.
        For Each sColumnName In inLine.Split(",")
            dt.Columns.Add(New DataColumn(sColumnName ))

        Next

        'Then, for simplicitie's sake, you can just read the next line before starting your Do Loop.
        inLine = sr.ReadLine

0
VBRocksCommented:
I'm still interested.

I gave msstucchi an extensive working example above, and he seemed to be pleased with the results.

Thanks!

0
mstucchiAuthor Commented:
thank you , my appologies for the delayed response, busy, your solution was great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.