Solved

reading 100 rows at a time in a text file

Posted on 2007-04-10
8
176 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

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…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
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…

810 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