CODEWITHSUNDEEP
sqlsql-serverexceldate
jboy12
jboy12

Reputation: 3916

Moving data from Excel to SQL Server table

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?

Upvotes: 9

Views: 52480

Answers (6)

Mike
Mike

Reputation: 638

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.

Upvotes: 1

Bond_007
Bond_007

Reputation: 1

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

Upvotes: 0

K_B
K_B

Reputation: 3678

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

Upvotes: -2

granadaCoder
granadaCoder

Reputation: 27884

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.

Upvotes: 2

Travis
Travis

Reputation: 146

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.

Upvotes: 10

OliC
OliC

Reputation: 175

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.

Upvotes: 6

Related Questions