Post reply

Import Excel Data into Multiple Tables

  • July 26, 2005 at 12:55 pm

    #67027

    Hi

    I have a SQL Server database which has many tables.

    At the moment, all of the data for each table is held in one single excel file, one single excel sheet. Each column in the excel file represents a field, each row represents an record.

    My question is this, how can i import all of the data from the excel file, into multiple tables in one attempt?

    Many thanks

    Autumn1008

  • July 26, 2005 at 5:40 pm

    #577340

    If the field structure is the same for each table, then you could try this.

    In the datapump source, you could try using an SQL query instead of just selecting the sheet name. It'd be something like 

    SELECT F1, F2, F3, etc... FROM [Sheet1$] WHERE F1 = 'blah...'

    Then you would have multiple datapumps from the same source inserting into each destination table with different criteria.

     

    --------------------
    Colt 45 - the original point and click interface

  • July 29, 2005 at 4:13 am

    #578133

    you could start by creating a stag table..

    export your excel sheet into a new table in your database..

    then from there you could easily do some query stuff...

    then use the dts package.. you could simply use the execute sql query and transform data task..

    btw, could you give examples of the files you want to import..

    tnx

    Glad to Help,

    Crischell Olegario

  • August 1, 2005 at 6:09 am

    #578471

    thanks Crischell.  Actually I am trying the similar approach to what you said.  I export the excel file into a temp table which has all the same fields as the spreadsheet and distribute the data from the temp table into the destination tables.  I want to create a stored procedure to do the distribution but had a hard time reading the data row by row.  May be you can help me with the syntax. 

    Let me explain the nature of the business a bit.  We need to record each project and the sampling events for each project and for each sampling event there are more than one sample collected.  There are three tables "Projects" (with ProjectID as Identity column), "SamplingEvents" (with SamplingEventID as Identity column and Project ID as PK) and "Samples" (with SampleID as Identiy column and SamplingEventID as PK).  Now my question is how to get the record row by row for distribution.  

    Thanks a lot for the help.

    Autumn1008

Viewing 4 posts - 1 through 3 (of 3 total)

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