Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: problem in export of csv file into sql server

    I am trying to export a CSV file into sql server. I wrote a query to perform select fields from csv file using OPENROWSET. I am getting all the values in a single column, but I need to get in three different columns. Any can body can help me



    The query is as follows

    Select * from OpenRowset ('MSDASQL','Driver= {Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=” Directory name” \;Extended properties=''ColNameHeader=True; Format=Delimited;''','select * from Sample.csv')



    Sample.csv

    col1, col2, col3 ------------ all the values are in single cell of excel csv file
    1, a, abc
    2, sfasf, sdgagas



    Output of query is

    col1,col2,col3 -------- all the values are coming in one column and with comma between them
    1,a,abc
    2,sfasf,sdgagas

    can any body help me where i am going wrong?

    Thanks in advance
    surya
    Last edited by suryany72jaipur; 07-10-07 at 08:28.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    might be easier to just import the entire file to a staging table and the work with it from there.

  3. #3
    Join Date
    Jul 2007
    Posts
    2
    would u like to give me some sample code

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Doh - something wrong with my pc, please ignore 2 duplicate answers and refer the top one.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.
    See this http://www.sql-server-helper.com/tip...-file-p01.aspx fyi.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •