Solved

Accessing data in a .CSV File using Io.Streamreader

Posted on 2012-04-02
9
460 Views
Last Modified: 2012-04-04
Good afternoon,

I have a .CSV file with about 30 columns of data and about 8,000 rows.  I'm able to access the data if I access columnes 1-8, if I switch it to try to read 9 or further, it says that there is a null reference in the array.  I've searched through the data and it is pretty straight forward data, numbers, decimals, times, etc....  Is this a limitation on the streamreader?  My code that works on columns 1-8.

    Public Sub GetTakeoffLatitude(ByVal FilePath As String)
        Dim folderPath As String = Server.MapPath("csvFiles") & "\" & System.IO.Path.GetFileName(FilePath)
        Dim sr As System.IO.StreamReader = System.IO.File.OpenText(folderPath)
        Dim record As String = sr.ReadLine()
        Do Until record Is Nothing
            Dim fields() As String = record.Split(",")
            Dim s As String = fields(4)
            If s.Contains("_") Or s.Contains("Latitude") Or s.Contains("degrees") Or s.ToString.Trim.Length < 1 Then
                'Keep going for first numerical record
            Else
                _TakeOffLat = s.Trim.ToString()
                Exit Do
            End If
            record = sr.ReadLine()
        Loop
        sr.Close()
        sr.Dispose()
    End Sub

Open in new window


But if I change this line: Dim s As String = fields(4)

To say 8 or 9:

System.IndexOutOfRangeException: Index was outside the bounds of the array.



Thanks in advance.
0
Comment
Question by:TysonAllen
  • 4
  • 3
  • 2
9 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
The only reason you should get that error is if your call to Split returned less than the 30 fields. This would happen if there weren't enough commas in the line. Can you confirm you have 29 commas in the offending line?
0
 

Author Comment

by:TysonAllen
Comment Utility
I opened up the file in notepad, and each row does have more than 8 or 9 commas, by far.  It also displays properly when opened up in excel, with 32 columns of data and a header at the top of each one.  I believe if it was having a problem with not enough comma's, excel would read it wrong and some of the data would be forced into the same columns.

I've looped through the file numerous times to pull data from other columns, but as I mentioned they weren't in anything past column 8.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
And are you sure you are looking at the same file that your code is?
0
 

Author Comment

by:TysonAllen
Comment Utility
Positive, I only have two .csv files in the folder I'm uploading from and they are both identical in size and columns.  I also commented out the code opening the file and had it just tell me the file name and directory to make 100% sure, and yes it's uploading the correct file and trying to access it.  *Ponder*
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
Comment Utility
Generally, the streamreader is quite solid. But like you say in your question, some lines have 8 or 9 commas, so the error you are gettingis because you are referencing an element in the array that doe not exist.
You can address this by placing a check for fields' count to see if there are more than 8 fields, and / or a check when the line in the file is blank (I actually made it 2 items), aka
    Public Sub GetTakeoffLatitude(ByVal FilePath As String)
        Dim folderPath As String = Server.MapPath("csvFiles") & "\" & System.IO.Path.GetFileName(FilePath)
        Using sr As New FileIO.TextFieldParser(folderPath)
            While Not sr.EndOfData
                Dim fields = sr.ReadFields()

                'put a check here for field length
                If Not fields.Count > 8 Then Continue While
                If Not fields.Count < 2 Then Continue While

                Dim s As String = fields(4)
                If s.Contains("_") Or s.Contains("Latitude") Or s.Contains("degrees") Or s.ToString.Trim.Length < 1 Then
                    'Keep going for first numerical record
                Else
                    _TakeOffLat = s.Trim.ToString()
                End If
            End While
        End Using

    End Sub

Open in new window

0
 
LVL 17

Expert Comment

by:nepaluz
Comment Utility
PS. I also changed from streamreader to textfieldparser since I believe it is more suited to reading / parsing CSV files (not in any way a confirmation of your suspicion of a bugin streamreader!)
0
 

Author Closing Comment

by:TysonAllen
Comment Utility
That worked, found out one row in the middle only had 9 columns.... so checking the column count and skipping that row worked.  Thanks.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
That worked, found out one row in the middle only had 9 columns....
Oh gosh, why didn't I think to say check that there were an appropriate number of columns...

The only reason you should get that error is if your call to Split returned less than the 30 fields. This would happen if there weren't enough commas in the line. Can you confirm you have 29 commas in the offending line?
Ooops!
0
 

Author Comment

by:TysonAllen
Comment Utility
Laugh, it wouldn't give me the line in the .csv file it was erroring out in, just vb.net line that was set to check the data.  Pretty weird that 2 lines out of about 8,000 in the csv file didn't have commas for an empty column data like the rest did.

Oh well, not my job to make the csv file - just read data from it so the concept you provided worked, thanks again. :)
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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
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…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

6 Experts available now in Live!

Get 1:1 Help Now