locked
Importing CSV file RRS feed

  • Question

  • Hi,

    I'm using SQL -2014 to import csv file into my staging table....

    Error

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
    Msg 7303, Level 16, State 1, Line 9
    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

    My Code

    SELECT *
    FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};', 'SELECT * FROM filepath\FileName.CSV');
    
    SELECT * 
    FROM OPENROWSET
    ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=\\filepath','SELECT * FROM FileName.csv')

    Not able to get around.... any help is highly appreciated.

    thanks

    S

    Tuesday, August 14, 2018 12:33 PM

Answers

  • Thanks V

    Are you using windows authentication or sql authentication to connect to SQL? - Windows Authentication

    Is the local admin user mapped to an admin user in  SQL?

    If I logon to the remote server and run SSMS as admin this piece of code works fine... But if I run this code from my local machine i.e. laptop it fails with above error... Local user(mylaptop is also sysadmin on SQL server but not a admin on remote machine-vm)

    Hi StSingh,

    Thanks for your reply.

    Could you please put the file "FileName.CSV" under the local machine path? Then try to execute the query and see if it works.

    --Move the file to the local machine path, then specific the file path in the query
    SELECT *
    FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};', 'SELECT * FROM [Local Path]\FileName.CSV');
    
    SELECT * 
    FROM OPENROWSET
    ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=[Local path]','SELECT * FROM FileName.csv')

    If it doesn't work, you need to configure file system permissions for database engine access, for detailed configuration, please refer to:

    Configure File System Permissions for Database Engine Access

    If it works, then move the file back to the shared path, test it as the steps above again.

    If you have any other questions, please feel free to let me know.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by StSingh Thursday, August 16, 2018 11:57 AM
    Wednesday, August 15, 2018 8:17 AM

All replies

  • check if this helps

    http://calyansql.blogspot.com/2013/02/fix-cannot-initialize-data-source.html

    https://blogs.msdn.microsoft.com/sqlforum/2010/12/20/faq-why-cannot-64-bit-msdasql-access-a-csv-text-file/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, August 14, 2018 12:38 PM
  • Thanks Visakh

    Followed the steps and uninstalled 32bit ... then installed 64 bit...

    and then comes a detailed error... but looks the same error message

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5d8 Thread 0xd3c DBC 0x1cb8158                                                             Text'.".
    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5d8 Thread 0xd3c DBC 0x1cb8158                                                             Text'.".
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

    Tuesday, August 14, 2018 1:49 PM
  • If I start ssms in admin mode then all of this works fine... so this has to something with permissions.. but I'm the SYSadmin on the server so what permission am I missing?

    Any help or reference links will be highly appreciated. 

    Tuesday, August 14, 2018 4:04 PM
  • If I start ssms in admin mode then all of this works fine... so this has to something with permissions.. but I'm the SYSadmin on the server so what permission am I missing?

    Any help or reference links will be highly appreciated. 

    Are you using windows authentication or sql authentication to connect to SQL?

    Is the local admin user mapped to an admin user in  SQL?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, August 14, 2018 4:10 PM
  • Thanks V

    Are you using windows authentication or sql authentication to connect to SQL? - Windows Authentication

    Is the local admin user mapped to an admin user in  SQL?

    If I logon to the remote server and run SSMS as admin this piece of code works fine... But if I run this code from my local machine i.e. laptop it fails with above error... Local user(mylaptop is also sysadmin on SQL server but not a admin on remote machine-vm)

    Tuesday, August 14, 2018 5:34 PM
  • Thanks V

    Are you using windows authentication or sql authentication to connect to SQL? - Windows Authentication

    Is the local admin user mapped to an admin user in  SQL?

    If I logon to the remote server and run SSMS as admin this piece of code works fine... But if I run this code from my local machine i.e. laptop it fails with above error... Local user(mylaptop is also sysadmin on SQL server but not a admin on remote machine-vm)

    Hi StSingh,

    Thanks for your reply.

    Could you please put the file "FileName.CSV" under the local machine path? Then try to execute the query and see if it works.

    --Move the file to the local machine path, then specific the file path in the query
    SELECT *
    FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};', 'SELECT * FROM [Local Path]\FileName.CSV');
    
    SELECT * 
    FROM OPENROWSET
    ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=[Local path]','SELECT * FROM FileName.csv')

    If it doesn't work, you need to configure file system permissions for database engine access, for detailed configuration, please refer to:

    Configure File System Permissions for Database Engine Access

    If it works, then move the file back to the shared path, test it as the steps above again.

    If you have any other questions, please feel free to let me know.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by StSingh Thursday, August 16, 2018 11:57 AM
    Wednesday, August 15, 2018 8:17 AM
  • Thanks Will.

    File is available locally... so i think it could be with file system permission. let me try and comback to you guys. Thanks

    Thursday, August 16, 2018 11:49 AM
  • Yes it was a folder permission issue
    Thursday, August 16, 2018 11:58 AM