none
Issue Loading CSV file using OPENROWSET

    Question

  • Hi All,

    I have a csv file that has a value like -200 when i tried open the file in SQL using OPENROWSET that value is read as null.

    select * from
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\TEST\;',
    'SELECT * FROM ABC.csv')

    when i save teh fiel as .xlsx and open using the OPENROWSET syntax for xlsx file it shows the correct value.

    I am not sure if it is the behaviour of teh file or withe SQL.Can some one tell me what would be the possible reasons for that.

    Thanks in advance.


    Raghav

    Friday, February 24, 2012 10:22 PM

Answers

All replies

  • Check this link and sample:

    http://www.databasejournal.com/features/mssql/article.php/10894_3331881_2

    select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
      DefaultDir=C:\External;','select top 6 * from
    MyCsv.csv')


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Friday, February 24, 2012 10:28 PM
  • Hi Arbi,

    Thanks for your Quick response . the link that you sent  helped me to learn more information . I am using SQL 2008 on 64 bit machine. The link that you posted tells about sql server 2000.

    however i failed to get answer that i am looking for . I know to work around to fix my issue but i want to know why the negative values in the file are shown as 'NULL' when i use this query

    select * from
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\TEST\;',
    'SELECT * FROM ABC.csv')

    Anyone else with more suggestions Please?

    Thanks!


    Raghav



    • Edited by Raghav4Sql Friday, February 24, 2012 11:15 PM
    Friday, February 24, 2012 11:13 PM
  • If you want to be real happy, use the very excellent SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Friday, March 02, 2012 12:03 PM
    Moderator
  • I know this is an old post but thought I'd post in case someone else is having the same problem.  Try using IMEX=1, which sets up the IMPORT mode and causes mixed columns to all be treated like text.

    select * from
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\TEST\;IMEX=1',
    'SELECT * FROM ABC.csv')


    --Jeff Moden

    Wednesday, June 25, 2014 2:06 AM
  • Hi Jeff,

    thanks for the post. I tried IMEX=1 but still getting following error. SQL Server 2012, Win 8

    Msg 7302, Level 16, State 1, Line 1
    Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    My query is as following:

    select * from
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\testScore.csv;IMEX=1', 
    'SELECT top 1 * FROM test.csv')

    Please help.

    Thanks.

    Best-

    Monday, July 07, 2014 2:01 PM