9

I have a very Simple excel sheet:

enter image description here

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?

Improve this question
4
  • 2
    Go into SQL Server Management Studio. Right click on the database where you want the table, click on Tasks --> Import Data, and use the import wizard. Commented Apr 17, 2013 at 13:21
  • 1
    you should put this as the answer so i can accept it Commented Apr 17, 2013 at 13:30
  • 1
    . . An odd comment since you already accepted someone else's answer. Commented Apr 17, 2013 at 13:32
  • 1
    I read and commented on your comment first. then I read the other answers Commented Apr 17, 2013 at 13:37

6 Answers 6

Reset to default
10

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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

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.

Improve this answer

1 Comment

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

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.

Improve this answer

Comments

1

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.

Improve this answer

1 Comment

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!
0

It can be also be done by creating a batch file.

For this you already need to have a table created on server with the same data structure as you have in excel.

Now using BCP you can import that data from the excel file and insert it into sql table.

BCP utility function

sqlcmd -S IP -d databasename -U username -P passwd -Q "SQL query mostly it should be truncate query to truncate the created table"
bcp databasename.dbo.tablename in "Excel file path from where you need to input the data" -c -t, -S Server_IP -U Username -P passwd -b provide batch size

You can refer to the link below for more options on the bcp utility:

https://msdn.microsoft.com/en-IN/library/ms162802.aspx

Improve this answer

Comments

-2

Open your SQL server interface software, add the date field to the table.

Go to excel, add the date column, copy the excel data.

Go to your SQL server interface software and use the functionality to import the data from the clipboard. (SQL server interface software that has this is for example Database4.net, but if you have another package with the functionality then use that.)

Alternatively use VBA with DOA or ADO to interact with the SQL server database and use SQL statements to add the field and copy the data into your table

Improve this answer

3 Comments

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?
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.
another downvote, apparently people think it more vague then I do. Although I think it is pretty similar to the accepted answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.