How to create an SQL table and and populate it with Excel spreadsheet data?

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.

Solution 1

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

Solution 2

SQL Server 2008R2+

1. Right click the database
2. Tasks
3. Import Data
4. Excel as Source
5. choose an XLXS, XLS, etc... Good to go :)

Solution 3

Save excel file as text(unicode) file and then use "sql import and export data" from all programs then select "flat file source" as file source and browse the txt file.Thats it.Simple and clear.

Solution 4

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)

View more solutions

Comments

[sebagomez]

My experience with SSIS... sgomez.blogspot.com/2007/10/…

[Nathan Prather]

thanks for the link to the tech republic article: blogs.techrepublic.com.com/datacenter/?p=205

[happythenewsad]

what about with a non enterprise server, like Sqlite? i haven't found any documentation on this.

[kmote]

what does "from all programs" mean?

[Maury Markowitz]

This works in most modern versions of SQL Admin, and is by far the easiest solution. The UI is a little maddening, but usable.