Solved

reading 100 rows at a time in a text file

Posted on 2007-04-10
8
170 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

21 Experts available now in Live!

Get 1:1 Help Now