Post reply

Importing csv file into SQL table

  • March 24, 2020 at 8:55 pm

    #3736766

    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.

     

  • March 24, 2020 at 9:03 pm

    #3736775

    Sometimes new additonal columns too.

  • March 24, 2020 at 9:27 pm

    #3736778

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 25, 2020 at 12:37 am

    #3736793

    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.

     

  • March 25, 2020 at 1:39 am

    #3736798

    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.

  • March 30, 2020 at 11:47 am

    #3738908

    See Chad Miller's Powershell function Write-DataTable

     

     

  • March 30, 2020 at 2:02 pm

    #3738947

    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.

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

  • March 30, 2020 at 2:03 pm

    #3738950

    Looks like this one

    https://github.com/RamblingCookieMonster/PowerShell/blob/master/Out-DataTable.ps1

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

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