Solved

Accessing data in a .CSV File using Io.Streamreader

Posted on 2012-04-02
9
466 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37798434
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
ID: 37798498
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 75

Expert Comment

by:käµfm³d 👽
ID: 37798508
And are you sure you are looking at the same file that your code is?
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:TysonAllen
ID: 37798534
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
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
ID: 37798581
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
ID: 37798584
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
ID: 37803736
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 75

Expert Comment

by:käµfm³d 👽
ID: 37804103
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
ID: 37807988
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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