Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Import Excel SpreadSheets Into Excel

    How can I use code (wither it be SQL or .Net Framework) to programmatically import 8 different Excel Sheets into One SQL Table (that currently does not exist)?

    This is SQL Server 2005. Excel 2007.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm a wussie that goes for the simplest answer possible. I would use SQL Server Integration Services, which makes it trivial (GUI supplied) to do the basic functionality and gives you scripting to get as fancy as you can possibly want.

    The fact that SSIS is licensed along with SQL Server it so costs nothing more doesn't hurt anything either!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    I'm a wussie that goes for the simplest answer possible. I would use SQL Server Integration Services, which makes it trivial (GUI supplied) to do the basic functionality and gives you scripting to get as fancy as you can possibly want.

    The fact that SSIS is licensed along with SQL Server it so costs nothing more doesn't hurt anything either!

    -PatP
    Very nice, thank you for the info & the link.

  4. #4
    Join Date
    Aug 2008
    Posts
    147
    Another option, which gives you good control , is to use Powershell. You can export Excel worksheets into SQL Server table(s). Powershell can manage the connection to SQL Server and the various methods required.
    Export Excel data to SQL Server with Powershell
    Last edited by JackVamvas; 05-02-14 at 02:51. Reason: link change
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by JackVamvas View Post
    Another option, which gives you good control , is to use Powershell.
    I'm generally a big fan of PowerShell, but I usually prefer SSIS as an ETL tool.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by JackVamvas View Post
    Another option, which gives you good control , is to use Powershell. You can export Excel worksheets into SQL Server table(s). Powershell can manage the connection to SQL Server and the various methods required.
    Export Excel data to SQL Server with Powershell
    I know I am late on a response, sorry for that. How would I use this to import multiple spreadsheets? I see it shows how to easily do one, but what about multiple?

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    I'm a wussie that goes for the simplest answer possible. I would use SQL Server Integration Services, which makes it trivial (GUI supplied) to do the basic functionality and gives you scripting to get as fancy as you can possibly want.

    The fact that SSIS is licensed along with SQL Server it so costs nothing more doesn't hurt anything either!

    -PatP
    Also -- using SSIS, the option to import to an existing table is grayed out, and I need all of these imports to go to a pre-created table or to a table that will be created by the 1st import then the subsequent imports will go to the same table.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as the schemas match (the Excel spreadsheet has the same number of columns and the columns are of compatible data types), the import wizard ought to allow you to set a table as the destination of the transformation.

    Check the number of columns first, then check to be sure that the data could go into the table's columns. As an example the string "DOB" can't be stored in a DATETIME column, those would be incompatible.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    As long as the schemas match (the Excel spreadsheet has the same number of columns and the columns are of compatible data types), the import wizard ought to allow you to set a table as the destination of the transformation.

    Check the number of columns first, then check to be sure that the data could go into the table's columns. As an example the string "DOB" can't be stored in a DATETIME column, those would be incompatible.

    -PatP
    Each spreadsheet has different number of columns, but I would only want to import the same data from each spreadsheet 4 varchar & 1 date-time.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I see... I don't know for certain if the wizard can handle that conversion, but an SSIS package certainly could.

    If you need this quickly, I'd suggest that you hire someone to do it for you. If you have some time (a few hours) to put into the process, it isn't hard to learn but it does take a bit of study and experimentation.

    Data has a spectrum of organization... A text file is basically chaos on wheels, anything goes. A spreadsheet is a bit more organized in the sense that the cells have structure and presentation, but those are fluid and can be redefined pretty quickly and easily. A database table is much more structured in that tables are made of columns and those columns have hard and fast data types. As you go from more structured to less structured (like a table to a spreadsheet), the transition is easy because the structure won't get in your way. As you go from less structured to more structured (like a spreadsheet to a table), then the additional structure can make things complicated.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    I see... I don't know for certain if the wizard can handle that conversion, but an SSIS package certainly could.

    If you need this quickly, I'd suggest that you hire someone to do it for you. If you have some time (a few hours) to put into the process, it isn't hard to learn but it does take a bit of study and experimentation.-PatP
    Thanks for the advice there. I'll take the time to learn it myself as this will benefit me more in the long run if I learn from the ground up. And it's not an urgent thing, just something I would like to do to automate a current repetitive task.

    One additional question...In your experience would I be better off inserting into a pre-made SQL Table or using the SSIS to create the table?
    Last edited by jo15765; 06-04-14 at 12:05.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Whenever I go from less structured to more structured like Excel to SQL Server, I always prefer to manually create the destination on the more structured side. This requires a bit more geek factor, but it also allows you to make better choices than the defaults that you'll get when you let the tools do things as they please.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Aug 2008
    Posts
    147
    Quote Originally Posted by jo15765 View Post
    I know I am late on a response, sorry for that. How would I use this to import multiple spreadsheets? I see it shows how to easily do one, but what about multiple?
    For importing multiple worksheets into a single SQL Server table - read this post Export multiple Excel worksheets to a single SQL Server table .

    If you're specifically looking at multiple spreadsheets - would it be just one worksheet per spreadsheet ?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  14. #14
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by JackVamvas View Post
    For importing multiple worksheets into a single SQL Server table - read this post Export multiple Excel worksheets to a single SQL Server table .

    If you're specifically looking at multiple spreadsheets - would it be just one worksheet per spreadsheet ?
    Yes it is only one worksheet per spreadsheet. And only at the VERY most 50 rows of data that would be imported.

  15. #15
    Join Date
    Aug 2008
    Posts
    147
    Just add an extra wrapper around the Loop , which iterates through the list of spreadheets .
    Alternatively you could create a script that passes a parameter to this script. The parameter being the spreadsheet name.
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •