Post reply

Copy multiple CSV files into database using stored proc , Need to Create new databases everytime

  • February 9, 2015 at 1:38 pm

    #397995

    hi

    I need to import multiple csv files and load into table and everytime new database has to be created .

    I was able to create new databases using stored proc

    How do i do a bulk insert for all the files at once to insert into tables .

    i want to load all the files at once .

  • February 9, 2015 at 6:44 pm

    #1776518

    Using SSIS

  • February 12, 2015 at 7:44 pm

    #1777389

    nikki123 (2/9/2015)

    hi

    I need to import multiple csv files and load into table and everytime new database has to be created .

    I was able to create new databases using stored proc

    How do i do a bulk insert for all the files at once to insert into tables .

    i want to load all the files at once .

    BULK INSERT is pretty easy if the files are in good shape. What do the file names and content of files look like.

    --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)

  • February 12, 2015 at 7:45 pm

    #1777390

    CooLDBA (2/9/2015)

    Using SSIS

    Or not. 😉

    --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)

  • February 12, 2015 at 8:20 pm

    #1777397

    I had done it in the past with similar requirements.

  • February 15, 2015 at 5:21 pm

    #1777742

    CooLDBA (2/12/2015)

    I had done it in the past with similar requirements.

    Ah... I'm sorry. Please accept my apologies. I didn't mean to be sarcastic on my last response. Long story that doesn't belong on this thread.

    --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)

  • February 15, 2015 at 5:30 pm

    #1777743

    nikki123 (2/9/2015)

    hi

    I need to import multiple csv files and load into table and everytime new database has to be created .

    I was able to create new databases using stored proc

    How do i do a bulk insert for all the files at once to insert into tables .

    i want to load all the files at once .

    Apologies to you to. Got side tracked.

    There's a lot of information missing from your request. How many files are there? Is there a naming convention associated with the files. Will each group of files that you want to create a separate database from be in separate folders or in a common staging area? If so, how to identify just the folders you want? Are the files all structured the same way? What types of files are they, True CSV, CSV, TSV, some other delimiter, fixed field, mixed, XML, EDI, spreadsheets, spreadsheet data, or ??? Do the files have column headers? Do the files have spurious data that isn't supposed to be loaded like file header information or a "Total Rows" line?

    Based on so little information, my answer would be to get a list of the files using xp_DirTree "path",1,1 or something else if file date and size were important, Bulk Insert the data using a file-control loop either with or without a BCP format file based on requirements, and, if necessary, move processed files to an archive folder.

    Similar steps to be had in SSIS but, of course, slightly different methodology in identifying source files and loop, etc.

    --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 7 posts - 1 through 6 (of 6 total)

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