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.

import a text file into the database

hello,
i want to import a textfile in the database (SQL)
For access.database the code like:
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"
sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My Documents\TextFiles].[Sample2.txt]"
cnn.Execute sqlString
works ok
How can i use such a command for the sql server
sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My Documents\TextFiles].[Sample2.txt]"
i get the error
"Invalid object name 'Text;HDR=NO;DATABASE=e:\My
Documents\TextFiles\Sample2.txt'."
best regardsYou can use a passthrough query like:
INSERT tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\My Documents\TextFiles].[Sample2.txt]";
User ID=Admin;Password=;Extended properties=text )
Another option is to use xp_cmdshell directly like:
INSERT tbl
EXEC master..xp_cmdshell 'TYPE e:\My Documents\TextFiles].[Sample2.txt]" ;
For complex ascii files you should consider using BULK INSERT/BCP IN or
DTS( Data transformation Services ). See SQL Server Books Online for details
on these utilities.
Anith|||hello Anith,
i tryed to run in the query analyser the command
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\transfer\Input\t1.txt";Extended properties=text')
but i get the syntax error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
any idea?
thanks
Xavier
"Anith Sen" wrote:

> You can use a passthrough query like:
> INSERT tbl
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="e:\My Documents\TextFiles].[Sample2.txt]";
> User ID=Admin;Password=;Extended properties=text )
> Another option is to use xp_cmdshell directly like:
> INSERT tbl
> EXEC master..xp_cmdshell 'TYPE e:\My Documents\TextFiles].[Sample2.txt]" ;
> For complex ascii files you should consider using BULK INSERT/BCP IN or
> DTS( Data transformation Services ). See SQL Server Books Online for detai
ls
> on these utilities.
> --
> Anith
>
>