Openrowset .csv file error

Question

Openrowset .csv file error

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
March 4, 2014 at 8:02 am

#396364

Hi I am receiving the following error while trying to query a .csv file using OpenRowset. I've tried every query style I can think of. I'm also open to other ways to do this?
select * from openrowset
('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Data\Projects\'
,'select * from compliance.csv')
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 30
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
Thank you in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

CKX SSCrazy Points: 2519 More actions · Answer 1

Marcus Farrugia (3/4/2014)
Hi I am receiving the following error while trying to query a .csv file using OpenRowset. I've tried every query style I can think of. I'm also open to other ways to do this?
select * from openrowset
('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Data\Projects\'
,'select * from compliance.csv')
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 30
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
Thank you in advance.

Is there any reason why you can't just BULK INSERT the .csv file into a SQL Server Table and then just query

the Table?

If you've never done that before, here's a simple guide:

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 2

Marcus Farrugia (3/4/2014)
Hi I am receiving the following error while trying to query a .csv file using OpenRowset. I've tried every query style I can think of. I'm also open to other ways to do this?
select * from openrowset
('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Data\Projects\'
,'select * from compliance.csv')
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 30
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
Thank you in advance.

Is this compliance.csv file continuously updated, where a BULK INSERT, a SSIS package or import/export tool is not a workable solution?

Do you lack permissions to do ALTER TABLE or BULK operations?

If not, then you are making work for yourself with no need.

If you absolutely must do this, have you tried enclosing 'compliance.csv' in quotes?

Thanks

John

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 3

Hi ck2,

If I were to use a Bulk Insert, using the link you provided as an example. The .csv is in an excel file, what would the values be for the Fieldterminator and Rowterminator properties. I agree that this is a better solution, just not sure how to implement it.

Thank you,

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 4

Marcus Farrugia (3/17/2014)
Hi ck2,
If I were to use a Bulk Insert, using the link you provided as an example. The .csv is in an excel file, what would the values be for the Fieldterminator and Rowterminator properties. I agree that this is a better solution, just not sure how to implement it.
Thank you,

Row terminator is likely CR and LF, ASCII 13 and 10.

Field terminator is likely comma, although I strongly recommend that, if you can change it to a pipe ( | ) or a tab, to do so to avoid the possibility of bad data if a field contains a comma and is not enclosed in quotation marks.

As always, play in test carefully.

Thanks

John.