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,Color_Code,SIZE_code,SKU,UPC,Descriptio n,Customer_Code,CUSTOMER_SKU
46400,Snow,S ,,776512634247,Cardigan/Snow,CUSTOMER_CODE,252675
46400,Snow,M,,776512634254,Cardigan/Snow,CUSTOMER_CODE,253965
46400,Snow,L,,776512634261,Cardigan/Snow,CUSTOMER_CODE,253969
46500,sand,2,,776512634360,Blouse/sand,CUSTOMER_CODE,252679
46500,sand,4,,776512634377,Blouse/sand,CUSTOMER_CODE,253979
46500,sand,6,,776512634384,Blouse/sand,CUSTOMER_CODE,253980
46400,Snow,XL,,776512634278,Cardigan/Snow,CUSTOMER_CODE,253970
46400,Mint,S ,,776512634285,Cardigan/Mint,CUSTOMER_CODE,253971
46400,Mint,M,,776512634292,Cardigan/Mint,CUSTOMER_CODE,253972
46400,Mint,L,,776512634308,Cardigan/Mint,CUSTOMER_CODE,253973




-----------------------------------------------------------------------------
Here's my VB.Net code
-----------------------------------------------------------------------------
Dim v_sql_connection As SqlConnection
Dim v_connection_csv As System.Data.OleDb.OleDbConnection
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_csv = New System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0; " + "Data Source=" + "C:\" + ";" + "Extended Properties=""text; HDR=Yes; FMT=Delimited""")

'Dim v_connection_str As String = ConfigurationManager.ConnectionStrings("LocalSqlSe rver").ConnectionString
'v_sql_connection = New SqlConnection(v_connection_str)

v_connection_csv.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_csv)
v_reader = v_sql_command.ExecuteReader()

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.HasRows Then

While v_reader.Read

v_customer_code = v_reader("CUSTOMER_CODE").ToString
v_customer_sku = v_reader("CUSTOMER_SKU").ToString
v_style_code = v_reader("STYLE_CODE").ToString
v_color_code = v_reader("COLOR_CODE").ToString
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