Solved

reading 100 rows at a time in a text file

Posted on 2007-04-10
8
173 Views
Last Modified: 2010-04-23
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.?
0
Comment
Question by:mstucchi
  • 3
  • 3
8 Comments
 
LVL 8

Expert Comment

by:Autoeforms
ID: 18886543
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
 
LVL 27

Accepted Solution

by:
VBRocks earned 125 total points
ID: 18887396
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
 

Author Comment

by:mstucchi
ID: 18900641
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:mstucchi
ID: 18900836
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 18902706
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 19258443
I'm still interested.

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

Thanks!

0
 

Author Comment

by:mstucchi
ID: 19259417
thank you , my appologies for the delayed response, busy, your solution was great.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Change data in datatable 8 31
Spacing between controls 4 30
More on Time zones in vb 2010 12 37
Allow user to hide and show datagridview columns 4 17
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now