Create SQL Table from Excel spreadsheet

Question

Is there an easy way to create a table in SQL Server (2005) from an Excel spreadsheet. I'm thinking maybe some tool?

Thanks in advance.

Answer 1

SSIS, but dealing with Excel always has a lot of quirks.

Answer 2

SQL Server 2005 Import/Export wizard is no good... actually I hate it, and it's not just me.
If you can, try SQL Server 2000's DTS Import/Export wizard

Answer 3

If the data is not that big and if it is a simple table, easiest way is to create and open the table in SQL Server Management Studio and copy paste the excel data into it.

Other solutions are using DTS or using SSIS..

Answer 4

You can write VBA code in Excel:

• Open a connection to the database
• Build the table
• Start filling records by looping through the rows of the spreadsheet Excel

Answer 5

You can treat the Excel document itself as a database using the builtin ODBC driver for that purpose. The fastest way is to open the ODBC Data Source Administrator, Configure the existing Excel Files data source and select a workbook. That gives you an Excel Database mapped to ODBC now all you need is a tool to read data from your ODBC database into your SQL Server database.

Answer 6

In your SQL Server enterprise manager there is an “import data” utility. It can take data in a variety of formats (.mdb, .XLS, .CSV, etc.). It is a simple job to point to the file that you want imported (into a new table) and start the upload of the data and structure. On the other hand the comments on how quirky Excel is accurate…. My suggestion is to cut and paste you data in an MS Acess table (All the data if it is a small table, about 10 records if it is very large). The MS Access data dump will identify any “quirk” with the data. Once the SQL table is created, uploading data into it via the MS Access table is just a matter of using the import manager (Source the MS Access table, Destination the SQL table)