Moving data from Excel to SQL Server table

Question

I have a very Simple excel sheet:

I am wanting to put this data into a table in SQL Server. I also wanted to add a field that contains a date.

what is the best way to do this?

Solution 1

Create a table in SQL server with the same number of fields that you have in the spreadsheet.

In SQL Server Management Studio Object Explorer you can right click the table you created and select "Edit top 200 rows". Highlight the data you want to copy in Excel and right click -> copy. Right click the space to the left of the first row in the SSMS edit tabe widow and paste.

After the import check the SQL table to make sure it has the same amount of rows as the spreadsheet.

You can add the date column to the SQL table after you do the data import. Or add it in the spreadsheet before you do the import.

Solution 2

You can first create the table in SQL Server with the added field and then use the import wizard in the Mangement Studio for importing the excel file. Or you create the table during the import task, and you add the new field later.

Solution 3

Option 1:

Read the data in an IDataReader, and then call a stored procedure to insert the data.

http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

I use the above when I have ~~alot~~ of rows to import and I want to validate them outside of the database.

Option 2:

http://support.microsoft.com/kb/321686

or search for:

Select  FROM OPENDATASOURCE Excel

Option N:

There are other options out there.

It depends what you like, how much time you want to put into it, is it a "one off" or you gotta do it for 333 files every day.

Solution 4

My solution was to convert .xlsx to .csv and then use this site to convert .csv to .sql. I then ran the sql file in sql server and generated my tables.

View more solutions

Comments

[Pondlife]

I didn't downvote you, but I'm guessing it's because your answer is too vague to be useful: it can be summarized as "find a tool that can import your data and use it". What does the "SQL server interface software" mean? What is "Database4"? You said "use the functionality to import the data", but which functionality is that?

[K_B]

ah, that might be it. Well it wasnt clear to me you had SQL Server Management Studio at your disposal so I kept that pretty general.

[K_B]

another downvote, apparently people think it more vague then I do. Although I think it is pretty similar to the accepted answer.

[btm86042]

just to add to this, the second sentence is accomplished by right clicking on the database -> Tasks -> Import Data... From here you can select an Excel document and SSMS will intelligently derive data types based on the data you have in your spreadsheet. i.e. if the column in Excel is of type "Currency" it will be created with type "Money".

[user1568901]

Unable to get the import wizard to work, but this works like a charm!

[Shn]

This worked for me. Converted my xlsx to csv and used the converter. Took a little while of playing with the settings on the website, but not longer than 10-15 minutes. Thanks!