none
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.
    Sunday, March 06, 2011 1:21 AM

Answers

  • 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)

    • Marked as answer by KJian_ Tuesday, March 15, 2011 3:17 AM
    Sunday, March 06, 2011 7:33 PM

All replies

  • 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/
    Sunday, March 06, 2011 8:48 AM
    Answerer
  • 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)

    Sunday, March 06, 2011 9:12 AM
  • 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)".

    Sunday, March 06, 2011 6:49 PM
  • 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)

    • Marked as answer by KJian_ Tuesday, March 15, 2011 3:17 AM
    Sunday, March 06, 2011 7:33 PM
  • 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
    Friday, March 11, 2011 6:56 AM
    Moderator