Importing csv file into SQL table

  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

    Looking for something like BULK insert CSV file to SQL table. But BULK insert assumes there is table already exist.

    Is there any other option to directly import csv file into SQL server (may be into temp table)...kinnda like SELECT * into .

    Problem is csv contains about 200 columns and sometimes they send less columns and sometimes more columns so I can't have static temp table.

    Any help would be appreciated.

     

  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

    Sometimes new additonal columns too.

  • ScottPletcher

    SSC Guru

    Points: 98552

    What I usually do in those situations, if the file's not way too extremely large (2GB max file size; for any size up to 1.5GB, I've found that it takes awhile, but not too long for me to use this method, at least on my system), is to load the file into a variable using OPENROWSET ... BULK ... SINGLE_CLOB.

    After that, you can can the parse the first few rows of data yourself to determine the number of columns, then create an appropriate table, and finally do a standard BULK into that table.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

    Thanks. Do you have example (script) by any chance?

    My problem is OPENROWSET ... BULK ... SINGLE_CLOB retruns everything in 1 column and I wouldn't know where the data begins from? Or column name ends.

     

  • frederico_fonseca

    SSChampion

    Points: 14743

    try this from Brad Shulz http://bradsruminations.blogspot.com/2011/01/so-you-want-to-read-csv-files-huh.html

    it may do what you need

    Attachments:
    You must be logged in to view attached files.
  • schleep

    SSChampion

    Points: 12551

    See Chad Miller's Powershell function Write-DataTable

     

     

  • Jeff Moden

    SSC Guru

    Points: 997112

    schleep wrote:

    See Chad Miller's Powershell function Write-DataTable

    Got a link?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

Viewing 8 posts - 1 through 8 (of 8 total)

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