Import csv files to Sql Server problem

Hi,

I try to import csv files to Sql Server using .net. The code is as following:

string strCsvConn =@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";

using (OleDbConnection cn =newOleDbConnection(strCsvConn))

{

string strSQL ="SELECT * FROM " + strFileName;

OleDbCommand cmd =newOleDbCommand(strSQL, cn);

cn.Open();

using (OleDbDataReader dr = cmd.ExecuteReader())

{

// Bulk Copy to SQL Server

using (SqlBulkCopy bulkCopy =newSqlBulkCopy(strSqlConn))

{

bulkCopy.DestinationTableName = strSqlTable;

bulkCopy.WriteToServer(dr);

}

}

}

And the data is as following (simplified):

Model,Serial

AFICIO 3045,K9464900965

AFICIO 3045,K9464900932

Fax 5510L,A3761290041

Fax 2210L,A4978800008

AFICIO 3025,K8565201014

AFICIO 3025,K8565102398

The result of the 2nd column is: 9464900965, 9464900932, null, null, 8565201014, 8565102398 - either the first character is missing or the whole entry is missing.

One more weird thing is that some other files work fine, though I am not able to tell any difference between them.

Any idea is hoghly appreciated.

shz

Hmmm... what's the datatype / size of the second column in your destination database?

In your example, the two rows that get null in second column contains a space in the first column. Is it maybe because space is used as a separator too in some way?|||

Thanks johram,

The datatype is varchar(50). However, I am afraid it has nothing to with the database, because it is the DataReader that retrieves wrong data. I test the DataReader with the following code:

while (dr.Read())

{

string str =Convert.ToString(dr[1]);

}

And all entries in the 1st column contain a space. The file contains more than 10 columns actually, all other columns are good.

Some more findings:

I have some "good" files that work fine and some "bad" files that have this problem - I cannot tell any difference between them in terms of data format. If I copy some records from a "good" file to a "bad" file, those records become bad. If I copy some records from a "bad" file to a "good" file, those records become good.

If I use TDS to import the files to SQL Server, it works fine. Excel can open the files properly too.

Thanks,

shz

|||

It seems to be the problem in the header (columns) of the cvs file.

Please make sure thay are okay.

Good luck.

|||

Fixed - need a Schema.ini file to define the Extended Properties of the driver.

Thanks to everyone.