Post reply

Using OPENROWSET to import CSV files

  • November 5, 2009 at 6:05 am

    #136108

    Hi

    I am trying to import a CSVs file using OPENROWSET and import this into a database

    my problem is the CSV files may contain a varying number of columns

    i have tried using

    SELECT BulkColumn

    FROM OPENROWSET (BULK '\\10.1.2.107\rp_uploaded_files\file.csv', SINGLE_CLOB) MyFile

    but this puts all the values into one fields

    i have also tried

    set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'

    exec (@string)

    but this requires the destintation table to have the same number of columns as the import file

    Can anyone advise

    thanks

    Simon

  • November 5, 2009 at 7:26 am

    #1075327

    Have you tried using OPENROWSET as SELECT INTO which will create the table in the database.

    SELECT * INTO [i]you select the name of the table to be created[/i] FROM OPENROWSET( ...........

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 5, 2009 at 7:53 am

    #1075347

    You could use Select ... Into ... and dump the data into a temp table. If you have row names in the first row, you can use those to then generate a table, or to generate an XML definition and store the data that way. (I prefer storing sem-structured data as XML, since that's what it's best at.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 5, 2009 at 7:55 am

    #1075352

    Hi thanks for that

    i have tried

    SELECT * into Mytable

    FROM OPENROWSET (BULK '\\10.1.2.107\rp_uploaded_files\file.csv', SINGLE_CLOB) MyFile

    but this puts all the data into one column and one row

    what i need it for it to be place in the database in seperate columns and seperate rows,

    can you help

    thanks

    Simon

  • November 5, 2009 at 8:07 am

    #1075366

    Unzip the file I attached here, and try this:

    select *

    into #T

    from openrowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};

    DefaultDir={path to file, not including file name};Extensions=csv;',

    'select * from CSV1_4_Cols.csv') Test;

    select *

    from #T;

    Once it's in the temp table, you can work with it as needed. You can query tempdb.sys.columns, using object_id(), to get the column names and definitions if you need those for something (in a dynamic data-source situation, you probably will need those).

    You can use IMEX in the OpenRowset definition if you have intermixed data types in the csv file columns (another common situation).

    You'll have to plug your actual file path into the query. Let me know if what I typed there isn't clear enough.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 5, 2009 at 8:28 am

    #1075393

    Thanks for that

    i seem to be getting this 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 1

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"

    obviously i need to create somesort of linked server, but not sure how to go about it

    can you help

    thanks

    simon

  • November 5, 2009 at 8:41 am

    #1075403

    What operating system are you running this on? Is it 64-bit? If so, you might need to get a driver for the ODBC connection. Google/Bing that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 5, 2009 at 9:42 pm

    #1075704

    Stop trying to use OPENROWSET to do imports. Use BULK INSERT instead.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 5, 2009 at 9:57 pm

    #1075708

    Hi

    Use bulk insert instead.

    BULK INSERT <table name>

    FROM <path name where the csv resides>.

    You have varying options that You can use by setting the parameters for the bulk insert option correspondingly.

  • November 6, 2009 at 2:21 am

    #1075764

    Hi

    yes im using a 64bit sever

    im having trouble locating to odbc drivers for this and can only find 32bit

    can you point me in the right direction

    thanks

    simon

  • November 6, 2009 at 2:23 am

    #1075766

    Hi

    i have tried using BULK INSERT

    using

    set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'

    exec (@string)

    but this requires the destintation table to have the same number of columns as the import file

    The files i want to import have an unknown number of coulmns

    Can you help at all?

    Simon

  • November 6, 2009 at 6:39 am

    #1075870

    Simon Parry (11/6/2009)

    Hi

    yes im using a 64bit sever

    im having trouble locating to odbc drivers for this and can only find 32bit

    can you point me in the right direction

    thanks

    simon

    http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 6, 2009 at 10:52 am

    #1076032

    Hi thanks for that

    unfortunately i still get the same error

    sorry if im missing something here

    can you help?

    thank you

    simon

  • November 6, 2009 at 11:14 am

    #1076046

    If you have the driver correctly installed, then the error is most likey to be from not having the correct filename and/or path in the openrowset command.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 6, 2009 at 9:18 pm

    #1076221

    Simon Parry (11/6/2009)

    Hi

    i have tried using BULK INSERT

    using

    set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'

    exec (@string)

    but this requires the destintation table to have the same number of columns as the import file

    The files i want to import have an unknown number of coulmns

    Can you help at all?

    Simon

    Yep... attach a copy of one of the files (unless it has private info in it) to your next post and tell me what you think you'd like to do with it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply