Solved

reading 100 rows at a time in a text file

Posted on 2007-04-10
8
177 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Expression Evaluater 3 37
vb.net and creating a class 5 20
Help with error in Query 2 31
Iteration Help (Asp.net VB) 5 24
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

790 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