Using OPENROWSET to import a textfile

[tezza98]

Help please, im trying to use open OPENROWSET to import a csv file into my database
Here is the code im using

--Read CSV using OpenRowSet
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=D:\MSSQL\Data\FUELBCP\;','select top 6 * from
TRANS.csv')

and i get this error

OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::G etColumnsInfo returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.


I am using OPENROWSET because i can the compare the text file to a table and the update the table base on what is in the text file but not in the table

If anyone knows why i am getting this error please put me out of my misery and help me out

Thanks

[iburyak]

I am not in a position to solve your problem but try to select not * but one column, then add one column at a time. It looks like top row that has column headers have invalid character or maybe just extra space or tab between columns which is enough to make server confused.

Good Luck.

[tezza98]

I found the soultion and here it is
It works perfectly

OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\w2ks1\MSSQL\DATA\FUELBCP',
'select  * from TRANS.csv')