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,Description,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=Microsoft.Jet.OLEDB.4.0; " _
        + "Data Source=" + "C:\" + ";" + "Extended Properties=""text; HDR=Yes; FMT=Delimited""")

        'Dim v_connection_str As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").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

 

[crvasquez]

Delete this - Dim v_sql_connection As SqlConnection --> You are not using this.

I ran your code and it worked fine (in winforms - I know you are doing this in ASP).

**Try this in your response write -> v_size_code.ToString()