none
Import multiple recordsets from excel into SQL table

Answers

  • Don't know much about Excel but you could import all these records into a single table with varchar fields.  After that, it would be relatively easy to read back this table into its multiple components.

    Also, in Excel, if I remember correctly, you can create List or Named Ranges.  Because of their names, they can be imported directly from SQL-Server because they will be seen as individual objects; so if the recordsets on this Excel spreadsheet have been individually defined as named ranges or lists, it shouldn't be a problem to import them individually.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Monday, February 14, 2011 9:05 AM

All replies

  • One way: cut and paste into new single-record set Excel files. Then you can use SSIS import/export Wizard to import them.

    Related links: http://www.youtube.com/watch?v=9Wmdhnx1niU

    http://www.databasejournal.com/features/mssql/article.php/3875421/SSIS-2008-Import-and-Export-Wizard-and-Excel-based-Data.htm


    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM

    Monday, February 14, 2011 8:32 AM
    Moderator
  • separate the tables in the excel into two different sheets and then import the data
    Monday, February 14, 2011 8:42 AM
  • Don't know much about Excel but you could import all these records into a single table with varchar fields.  After that, it would be relatively easy to read back this table into its multiple components.

    Also, in Excel, if I remember correctly, you can create List or Named Ranges.  Because of their names, they can be imported directly from SQL-Server because they will be seen as individual objects; so if the recordsets on this Excel spreadsheet have been individually defined as named ranges or lists, it shouldn't be a problem to import them individually.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Monday, February 14, 2011 9:05 AM
  • Yes Sylvain, this is what I'm looking for.

    But after creating those Lists ranges how can I import them independently into SQL table, anybody?


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Monday, February 14, 2011 10:12 AM
  • Thanks again Sylvian,

    How can you say you don't know much about EXCEL... your idea worked!!!

    I created 2 different ranges for Customer & Order... and while importing it shows me 2 different tables/sheets in import wizard where you have to select the sheet and I got 2 diff tables with single import.

     

    Superb thanks :)


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Monday, February 14, 2011 10:26 AM
  • I tested the following way:

    1. Created 2 named ranges in an Excel sheet1: selecting range with mouse, right click menu, name a range...

    2. I executed SSIS Import/Export Wizard from Object Explorer (RC database, tasks, import data...)

    3. It offered the named ranges for copying in top of sheet1, sheet2, sheet3 for checkmark selection

    4. It created two new tables and populated them according to the spreadsheet content

    Brilliant Sylvain!


    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM
    Monday, February 14, 2011 2:57 PM
    Moderator