Error while reading CSV file

[paulnamroud]

Hi Guys,

I have a weird problem.
While reading my csv file some data a returned as null.

When the routine reads values in the column "Size_Code" (like "S",, "M"...) it returns "NULL".
When routine reads the other values in the same column "Size_Code" (like 2, 4, 6 ...) it returns the right value.

Any clue ? Any help?
Shall i have to force a string conversion ? If yes how can i do it the sql statement ?

Thank you
Paul


-----------------------------------------------------------------------------
Here's some data from my CSV file:
-----------------------------------------------------------------------------
Style_code,Colo r_Code,SIZE_cod e,SKU,UPC,Descr iption,Customer _Code,CUSTOMER_ SKU
46400,Snow,S ,,776512634247, Cardigan/Snow,CUSTOMER_C ODE,252675
46400,Snow,M,,7 76512634254,Car digan/Snow,CUSTOMER_C ODE,253965
46400,Snow,L,,7 76512634261,Car digan/Snow,CUSTOMER_C ODE,253969
46500,sand,2,,7 76512634360,Blo use/sand,CUSTOMER_C ODE,252679
46500,sand,4,,7 76512634377,Blo use/sand,CUSTOMER_C ODE,253979
46500,sand,6,,7 76512634384,Blo use/sand,CUSTOMER_C ODE,253980
46400,Snow,XL,, 776512634278,Ca rdigan/Snow,CUSTOMER_C ODE,253970
46400,Mint,S ,,776512634285, Cardigan/Mint,CUSTOMER_C ODE,253971
46400,Mint,M,,7 76512634292,Car digan/Mint,CUSTOMER_C ODE,253972
46400,Mint,L,,7 76512634308,Car digan/Mint,CUSTOMER_C ODE,253973




-----------------------------------------------------------------------------
Here's my VB.Net code
-----------------------------------------------------------------------------
Dim v_sql_connectio n As SqlConnection
Dim v_connection_cs v As System.Data.Ole Db.OleDbConnect ion
Dim v_sql_command As OleDbCommand
Dim v_reader As OleDbDataReader

Dim v_file_name As String = "test.csv"

' Copy data from CSV file to SQL table
v_connection_cs v = New System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0; " + "Data Source=" + "C:\" + ";" + "Extended Properties=""te xt; HDR=Yes; FMT=Delimited"" ")

'Dim v_connection_st r As String = ConfigurationMa nager.Connectio nStrings("Local SqlServer").Con nectionString
'v_sql_connecti on = New SqlConnection(v _connection_str )

v_connection_cs v.Open()


Dim v_sql As String

' --------------------------------------------------------------------------------------------------
' The engine should read the CSV file line by line
' We don't upload the line if we find it invalid, meaning the following are OK per line.
' - Warehouse code: Should NOT be null
' - Customer code: Should NOT be Null AND should exist in the Customers Table
' - Currency code: Should NOT be Null AND should exist in the Currencies table
' Only Rejected records are displayed in the grid to notify user about records that were not uploaded
' ----------------------------------------------------------------------------------------------------
v_sql = " SELECT Customer_Code AS CUSTOMER_CODE "
v_sql &= " , Customer_SKU AS CUSTOMER_SKU "
v_sql &= " , Style_Code AS STYLE_CODE "
v_sql &= " , Color_Code AS COLOR_CODE "
v_sql &= " , char(Size_Code) AS SIZE_CODE "
v_sql &= " , SKU AS SKU "
v_sql &= " , UPC AS UPC "
v_sql &= " FROM (" + v_file_name + ") "

v_sql_command = New OleDbCommand(v_ sql, v_connection_cs v)
v_reader = v_sql_command.E xecuteReader()

Dim v_message As String
Dim v_nb_error As Integer = 0

Dim v_customer_code As String
Dim v_customer_sku As String
Dim v_style_code As String
Dim v_color_code As String
Dim v_size_code As String
If v_reader.HasRow s Then

While v_reader.Read

v_customer_code = v_reader("CUSTO MER_CODE").ToSt ring
v_customer_sku = v_reader("CUSTO MER_SKU").ToStr ing
v_style_code = v_reader("STYLE _CODE").ToStrin g
v_color_code = v_reader("COLOR _CODE").ToStrin g
v_size_code = v_reader("SIZE_ CODE").ToString .Trim

Response.Write( "Item = " & v_customer_code & " - " & v_customer_sku & " - " & v_style_code & " - " & v_color_code & " - " & v_size_code & "<BR>")

End While
End If

[Torgg]

Here is code I used in a project where I needed to open a csv file and parse it. My code (which I've tested with your data) opens the csv file and populates a datatable. I simply run through the datatable and show all your data in the output window.

Here is the Method...

    
Public Function ParserCSVData(ByVal sCSV_Path As String, ByVal sCSV_Name As String, Optional ByVal sCSV_Header As Boolean = True) As DataTable
        'This function simply takes the raw .csv file and imports it to a DataTable
        'sCSV_Path = "C:\Documents and Settings\user\Desktop\" 'without the file name and ext.
        'sCSV_Name = "test.csv" just the file name and ext. 
        'sCSV_Header = Does the .csv file have a header row

        Dim tmpDataTable As New DataTable
        Try
            Dim sHeader As String = "HDR=YES;"
            If sCSV_Header = False Then
                sHeader = "HDR=NO;"
            End If

            Dim sConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sCSV_Path & ";Extended Properties=;text;" & sHeader & ""
            Using oConnection As New System.Data.OleDb.OleDbConnection(sConnection)
                Dim tmpDataAdapter As New OleDb.OleDbDataAdapter
                tmpDataAdapter.SelectCommand = New OleDb.OleDbCommand("Select * From [" & sCSV_Name & "]", oConnection)

                tmpDataAdapter.Fill(tmpDataTable)
                tmpDataTable.TableName = "CSVData"
            End Using
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        Return tmpDataTable
    End Function

Here is the usage...

       
Dim dtTestDataTable = ParserCSVData("C:\Users\Torgg\Desktop", "test.csv", True)

        For Each row As DataRow In dtTestDataTable.Rows
            Console.WriteLine("Item = " & row("CUSTOMER_CODE") & " - " & row("CUSTOMER_SKU") & " - " & row("STYLE_CODE") & " - " & row("COLOR_CODE") & " - " & row("SIZE_CODE"))
            'Response.Write("Item = " & row("CUSTOMER_CODE") & " - " & row("CUSTOMER_SKU") & " - " & row("STYLE_CODE") & " - " & row("COLOR_CODE") & " - " & row("SIZE_CODE") & "<BR>")
        Next

I hope this is helpful,
Torgg