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