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.

score 18 · Answer 1 · edited Jun 29 '16 at 06:32

18

SQL Server 2008R2+

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

edited Jun 29 '16 at 06:32

LaBracca

13,941
34
129
224

answered May 21 '14 at 18:15

bird2920

313
2
8

This works in most modern versions of SQL Admin, and is by far the easiest solution. The UI is a little maddening, but usable. – Maury Markowitz Sep 13 '16 at 15:37

score 17 · Answer 2 · answered Mar 11 '12 at 21:54

17

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.

answered Mar 11 '12 at 21:54

Abhinava Shukla

179
1
2

what does "from all programs" mean? – kmote Feb 02 '16 at 15:15

score 10 · Accepted Answer · edited Aug 20 '15 at 04:40

10

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

edited Aug 20 '15 at 04:40

Community

1
1

answered Oct 07 '08 at 16:00

Gulzar Nazim

50,518
24
125
170

My experience with SSIS... http://sgomez.blogspot.com/2007/10/sql2005-importexport-wizard-sucks.html – sebagomez Oct 07 '08 at 16:03
thanks for the link to the tech republic article: http://blogs.techrepublic.com.com/datacenter/?p=205 – Nathan Prather Nov 26 '08 at 18:22
what about with a non enterprise server, like Sqlite? i haven't found any documentation on this. – happythenewsad Jun 26 '09 at 18:23

score 1 · Answer 4 · answered Oct 13 '08 at 20:32

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)

score 0 · Answer 5 · answered Oct 07 '08 at 16:02

0

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

answered Oct 07 '08 at 16:02

score 0 · Answer 6 · answered Oct 13 '08 at 20:18

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.

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

6 Answers6