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

V

6

26

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.

Viscosity answered 7/10, 2008 at 15:56 Comment(0)

C

11

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

Corneliuscornell answered 7/10, 2008 at 16:0 Comment(3)

My experience with SSIS... sgomez.blogspot.com/2007/10/… – Mithridate 7/10, 2008 at 16:3

thanks for the link to the tech republic article: blogs.techrepublic.com.com/datacenter/?p=205 – Hairdo 26/11, 2008 at 18:22

what about with a non enterprise server, like Sqlite? i haven't found any documentation on this. – Subphylum 26/6, 2009 at 18:23

A

25

SQL Server 2008R2+

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

Appetitive answered 21/5, 2014 at 18:15 Comment(1)

This works in most modern versions of SQL Admin, and is by far the easiest solution. The UI is a little maddening, but usable. – Fidellia 13/9, 2016 at 15:37

B

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.

Buenrostro answered 11/3, 2012 at 21:54 Comment(1)

what does "from all programs" mean? – Bendwise 2/2, 2016 at 15:15

C

11

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

Corneliuscornell answered 7/10, 2008 at 16:0 Comment(3)

My experience with SSIS... sgomez.blogspot.com/2007/10/… – Mithridate 7/10, 2008 at 16:3

thanks for the link to the tech republic article: blogs.techrepublic.com.com/datacenter/?p=205 – Hairdo 26/11, 2008 at 18:22

what about with a non enterprise server, like Sqlite? i haven't found any documentation on this. – Subphylum 26/6, 2009 at 18:23

I

1

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)

Imprison answered 13/10, 2008 at 20:32 Comment(0)

S

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

Say answered 7/10, 2008 at 16:2 Comment(0)

M

0

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.

Miss answered 13/10, 2008 at 20:18 Comment(0)

Recommended topics

Hot tags