Query csv file on x64 machine using opendatasource

Question

64 bit OS and sql 2008 when I run the below query to copy csv data via an ad-hoc opendatasource connection throws following error.

Query:

SELECT *      
FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Test Data\;Extended properties=Text')...File#csv

error message:

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Answer 1

Have you tried SSIS? I have doubts that MS released JET text driver for 64 bit..

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=D:\txtsrv;','select  * from Textfile.txt')

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Maybe: http://technet.microsoft.com/en-us/library/bb742412.aspx

Here's the relevant part:
« If you don't intend to connect to Access databases with ADO, you can
enhance your application's performance by changing the threading model for
the main ADO components from "Apartment" to "Both" in the registry.
Caution Do not use a registry editor to edit the registry directly unless
you have no alternative. The registry editors bypass the standard safeguards
provided by administrative tools. These safeguards prevent you from entering
conflicting settings or settings that are likely to degrade performance or
damage your system. Editing the registry directly can have serious,
unexpected consequences that can prevent the system from starting and
require that you reinstall Windows 2000. To configure or customize Windows
2000, use the programs in Control Panel or Microsoft Management Console
(MMC) whenever possible.

By default, the ADO objects are assigned the Apartment threading model in
the registry. This model guarantees that each object is allocated a
dedicated thread for the life of the object, and all calls to the object
execute on the same thread. Although the Apartment model provides
significant improvements over the single-threading model (in which many
objects share one thread), and works with providers that are not
free-threaded (like Access), it also has its performance drawbacks. For
instance, if you store ADO components, such as the Connection object, in the
ASP Session object, IIS 5.0 will enforce a limit of one thread per user
session.

To switch ADO to a Both threading model, open Windows Explorer and
double­click Makefre15.bat in the ADO installation folder (C:\Program
Files\Common Files\System\Ado, by default). To reverse the process (that is,
to return the threading model to the Apartment model), double-click
Makeapt15.bat in the ADO installation folder. »

Personally, I have no idea if this will be helpful or not in your case and this is also about some old stuff: we are talking here about IIS 5.0. Furthermore; I don't know if the latest versions of the Access OLEDB
provider still have this problem with the "Both threading" model.  However, this is the only thing that I know of about ADO and the single apartment threaded model problem.

You might also be hit with the 32/64 bit problem.  As I don't have a 64 bit machine at hand at this moment, I cannot tell you if there are two versions of the above batch files for the 32 and 64 bit platform.

---

Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server (French)

Answer 3

Hi,

 

Using the import export wizard is serving my purpose but I get 100 different CSV files which need to be loaded to corresponding tables is there a way i can get this done.

when bulk insert is used:

 

data:

First row has columns: "Code","60500","60501","60502","60503","60504","60505","60506","60507","60508","60509","60510","60511","60512","60513","60514","60515","60516","60517","60518","60519","60520","60521","60522","60523","60524","60525","60526","60527","60528","60529","60530","60531","60532","60533","60534","60535","60536","60537","60538","60539","60540"
Second row on data: "00XXX0001",0,11,8,0,3,0,0,0,0,0,0,3,3,0,0,0,0,0,0,0,0,3,3,0,0,0,14,11,3,0,0,11,11,0,0,0,3,0,3,0,0

Query:

BULK INSERT temp
FROM 'C:\Folder\CSV.csv'
WITH (
FIELDTERMINATOR  = '',
ROWTERMINATOR = '\r\n',
FirstRow = 2
)

 

error:

Msg 4866, Level 16, State 8, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

Answer 4

The RowTerminator should be '\n', not '\n\r'.

Also, I'm not sure about the field terminator but it should be ',' instead of the empty string ''.

One easy way of going through this is simply to read a full line at a time in a single field and do yourself the parsing of the line into its multiple fields:

create table #temp1 (i varchar (500))

BULK INSERT #temp1
FROM 'C:\Folder\CSV.csv'
WITH (
FirstRow = 2
)

select len (i), * from #temp1

The rules for the terminators are very complicated.  For example, if you want to scan the fist line, probably that the FieldTerminator should be '","' and the RowTerminator '"\n' (or '"\r\n' ?) and even, you end up with a " for the first character of the first field.

---

Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server (French)

Answer 5

Using the import export wizard is serving my purpose but I get 100 different CSV files which need to be loaded to corresponding tables is there a way i can get this done.

Yes, an SSIS package can do the job. For more assistance visit:

SQL Server Integration Services

 

---

Kalman Toth, SQL Server & BI Training; SQLUSA.com