Solved

vb.net reading CSV file - null exception

Posted on 2014-11-13
6
464 Views
Last Modified: 2014-11-14
Hello Experts
I am trying to parse CSV file which has 2 rows of data, its reading the first line correctly, storing in the structure variable but when I loop it through the second time, it errors when I try to read the first column (fields(0))

"Object reference not set to an instance of an object."

please can someone advice what is wrong with this code?

  Private Function ParseCSVs() As Boolean

        Dim LocalFilePath As String = String.Empty

        For Each aCSV As xCSV In _CSVs
            LocalFilePath = My.Settings.Item("GRFiles") & "DailyOrders\" & aCSV.CSVFileName
        
            Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(LocalFilePath)

                MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
                MyReader.Delimiters = New String() {","}  '{vbTab}
                Dim currentRow As String()
                'Loop through all of the fields in the file.  
                'If any lines are corrupt, report an error and continue parsing.  
                Dim OrderRequest As New cCSVOrderRequest
                OrderRequest.Items = New List(Of cCSVProductItem)

                Dim Product As ProductClass = Nothing
                Dim Item As cCSVProductItem = Nothing
                While Not MyReader.EndOfData
                    Try

                        currentRow = MyReader.ReadFields()
                        ' Include code here to handle the row. 
                        Dim fields As String() = MyReader.ReadFields()
                        Item = New cCSVProductItem()
                        Item.OrderItemNumber = fields(0)
                        Item.PartNumber = fields(2)
                        Item.Description = fields(3)
                        Item.Quantity = fields(4)
                        Item.ShipTitle = fields(17)
                        Item.ShipToName = fields(18)
                        Item.ShipAddress1 = fields(19)
                        Item.ShipAddress2 = fields(20)
                        Item.ShipAddress3 = fields(21)
                        Item.ShipAddress4 = fields(22)
                        If fields(23).Contains("GB") Then
                            Item.ShipCountry = "238"
                        Else
                            Item.ShipCountry = ""
                            'CommonFunctions.SendErrorEmail("")
                        End If
                        Item.ShipPostCode = fields(24)
                        Item.PhoneNumber = fields(28)
                        OrderRequest.Items.Add(Item)
                    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                        'CommonFunctions.SendErrorEmail("")
                        Return False
                    End Try
                End While
                aCSV.OrderRequest = OrderRequest
            End Using
        Next
        Return True
    End Function

Open in new window

Untitled.png
0
Comment
Question by:mehmast
  • 3
  • 2
6 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40440689
That is because you are reading both rows in the first pass and therefore when you try to get the next row there is no more data, fields(0) is null and you are trying to assign that to a variable in this line of code.

 Item.OrderItemNumber = fields(0)

This is where you are reading in both lines in the file in the first pass. Modify the code to read once per pass.

Try
    '' First time you read the line from the file
    currentRow = MyReader.ReadFields()
    ' Include code here to handle the row. 
    
    '' Now you fread the second line in from the file 
    Dim fields As String() = MyReader.ReadFields()

Open in new window

0
 

Author Comment

by:mehmast
ID: 40440847
Hello
thank you for picking this up for me
i have headers in first row, so this line will ignore the headers
currentRow = MyReader.ReadFields()

the second and the third rows has data which I want to extract,

how can i make sure i only run the headers once in the loop? please advice
0
 

Assisted Solution

by:john hill
john hill earned 100 total points
ID: 40440867
something like this?


 Private Function ParseCSVs() As Boolean

        Dim LocalFilePath As String = String.Empty

        For Each aCSV As xCSV In _CSVs
            LocalFilePath = My.Settings.Item("GRFiles") & "DailyOrders\" & aCSV.CSVFileName
        
            Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(LocalFilePath)

                MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
                MyReader.Delimiters = New String() {","}  '{vbTab}
                Dim currentRow As String()
                'Loop through all of the fields in the file.  
                'If any lines are corrupt, report an error and continue parsing.  
                Dim OrderRequest As New cCSVOrderRequest
                OrderRequest.Items = New List(Of cCSVProductItem)

                Dim Product As ProductClass = Nothing
                Dim Item As cCSVProductItem = Nothing
  Dim Cntr As Integer = 1
                While Not MyReader.EndOfData
                    Try

                      If Cntr = 1 Then
                            currentRow = MyReader.ReadFields()
                        End If
                        ' Include code here to handle the row. 
                        Dim fields As String() = MyReader.ReadFields()
                        Item = New cCSVProductItem()
                        Item.OrderItemNumber = fields(0)
                        Item.PartNumber = fields(2)
                        Item.Description = fields(3)
                        Item.Quantity = fields(4)
                        Item.ShipTitle = fields(17)
                        Item.ShipToName = fields(18)
                        Item.ShipAddress1 = fields(19)
                        Item.ShipAddress2 = fields(20)
                        Item.ShipAddress3 = fields(21)
                        Item.ShipAddress4 = fields(22)
                        If fields(23).Contains("GB") Then
                            Item.ShipCountry = "238"
                        Else
                            Item.ShipCountry = ""
                            'CommonFunctions.SendErrorEmail("")
                        End If
                        Item.ShipPostCode = fields(24)
                        Item.PhoneNumber = fields(28)
                        OrderRequest.Items.Add(Item)
Cntr = Cntr + 1
                    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                        'CommonFunctions.SendErrorEmail("")
                        Return False
                    End Try
                End While
                aCSV.OrderRequest = OrderRequest
            End Using
        Next
        Return True
    End Function

                                

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 400 total points
ID: 40440916
Hi mehmast;

Then move the first read line just before the while loop and shown below.

Private Function ParseCSVs() As Boolean

    Dim LocalFilePath As String = String.Empty

    For Each aCSV As xCSV In _CSVs
        LocalFilePath = My.Settings.Item("GRFiles") & "DailyOrders\" & aCSV.CSVFileName
    
        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(LocalFilePath)

            MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            MyReader.Delimiters = New String() {","}  '{vbTab}
            Dim currentRow As String()
            'Loop through all of the fields in the file.  
            'If any lines are corrupt, report an error and continue parsing.  
            Dim OrderRequest As New cCSVOrderRequest
            OrderRequest.Items = New List(Of cCSVProductItem)

            Dim Product As ProductClass = Nothing
            Dim Item As cCSVProductItem = Nothing
            
            '' Read the header row, first line in the file, this is done once per file
            currentRow = MyReader.ReadFields()             
            
            While Not MyReader.EndOfData
                Try

                    ' Include code here to handle the row. 
                    Dim fields As String() = MyReader.ReadFields()
                    Item = New cCSVProductItem()
                    Item.OrderItemNumber = fields(0)
                    Item.PartNumber = fields(2)
                    Item.Description = fields(3)
                    Item.Quantity = fields(4)
                    Item.ShipTitle = fields(17)
                    Item.ShipToName = fields(18)
                    Item.ShipAddress1 = fields(19)
                    Item.ShipAddress2 = fields(20)
                    Item.ShipAddress3 = fields(21)
                    Item.ShipAddress4 = fields(22)
                    If fields(23).Contains("GB") Then
                        Item.ShipCountry = "238"
                    Else
                        Item.ShipCountry = ""
                        'CommonFunctions.SendErrorEmail("")
                    End If
                    Item.ShipPostCode = fields(24)
                    Item.PhoneNumber = fields(28)
                    OrderRequest.Items.Add(Item)
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    'CommonFunctions.SendErrorEmail("")
                    Return False
                End Try
            End While
            aCSV.OrderRequest = OrderRequest
        End Using
    Next
    Return True
End Function

Open in new window

0
 

Author Closing Comment

by:mehmast
ID: 40442607
thank you
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40442619
Not a problem mehmast, glad to help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

12 Experts available now in Live!

Get 1:1 Help Now