?
Solved

Accessing data in a .CSV File using Io.Streamreader

Posted on 2012-04-02
9
Medium Priority
?
474 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

649 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