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.
TysonAllenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.