null values when importing from CSV

[Matt Michael]

I have a large csv file (3000+ records) that needs to be imported into our
SQL Server 2000 database, and whenever I try to import, I receive null
values in certain columns for which data exists whenever it is opened in
Excel. If I import through MS Access, I receive the same null values, and
the values exist in the excel spreadsheet. The null values generated are
random, with no noticable pattern, except the places in which the null
values show up is the same every time you do an import. Whenever I have
data in my dataset, I bind it to a datagrid and that is when the random null
values show. Due to the confidentiality of the data, I cannot include the
CSV file that I'm importing. The code that I'm using to import data into
the database is included. Are there any known quirks with using the
Microsoft Jet provider, and ADO .NET with importing CSV files?

Dim name As String = System.IO.Path.GetFileName(filename)

Dim pathname As String = System.IO.Path.GetDirectoryName(filename)

Dim f As System.IO.File

If f.Exists(IO.Path.Combine(pathname, name)) Then

Dim dbCSV_ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & pathname & ";Extended
Properties=""text;HDR=Yes;FMT=Delimited"""

Dim dbCSVConnection As New OleDb.OleDbConnection(dbCSV_ConnectionString)

Dim daCSV As New OleDb.OleDbDataAdapter("SELECT * FROM [" & name & "]",
dbCSVConnection)

daCSV.Fill(csvImport, "CSVImport")

End If

-Matt

 

[Paul Clement]

¤ I have a large csv file (3000+ records) that needs to be imported into our
¤ SQL Server 2000 database, and whenever I try to import, I receive null
¤ values in certain columns for which data exists whenever it is opened in
¤ Excel. If I import through MS Access, I receive the same null values, and
¤ the values exist in the excel spreadsheet. The null values generated are
¤ random, with no noticable pattern, except the places in which the null
¤ values show up is the same every time you do an import. Whenever I have
¤ data in my dataset, I bind it to a datagrid and that is when the random null
¤ values show. Due to the confidentiality of the data, I cannot include the
¤ CSV file that I'm importing. The code that I'm using to import data into
¤ the database is included. Are there any known quirks with using the
¤ Microsoft Jet provider, and ADO .NET with importing CSV files?
¤
¤ Dim name As String = System.IO.Path.GetFileName(filename)
¤
¤ Dim pathname As String = System.IO.Path.GetDirectoryName(filename)
¤
¤ Dim f As System.IO.File
¤
¤ If f.Exists(IO.Path.Combine(pathname, name)) Then
¤
¤ Dim dbCSV_ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;
¤ Data Source=" & pathname & ";Extended
¤ Properties=""text;HDR=Yes;FMT=Delimited"""
¤
¤ Dim dbCSVConnection As New OleDb.OleDbConnection(dbCSV_ConnectionString)
¤
¤ Dim daCSV As New OleDb.OleDbDataAdapter("SELECT * FROM [" & name & "]",
¤ dbCSVConnection)
¤
¤ daCSV.Fill(csvImport, "CSVImport")
¤
¤ End If

Probably a case of mixed mode characters (numeric and alphanumeric) that is causing some columns to
be Null. I would recommend using a schema.ini to define the file structure and data types:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)