• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Accessing data in a .CSV File using Io.Streamreader

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
TysonAllen
Asked:
TysonAllen
  • 4
  • 3
  • 2
1 Solution
 
käµfm³d 👽Commented:
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
 
TysonAllenAuthor Commented:
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
 
käµfm³d 👽Commented:
And are you sure you are looking at the same file that your code is?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
TysonAllenAuthor Commented:
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
 
nepaluzCommented:
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
 
nepaluzCommented:
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
 
TysonAllenAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
TysonAllenAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now