Create sql insert script from excel sheet

Question

I have a large excel worksheet that I want to add to my database.

Is it possible to create a SQL insert script from this excel sheet?

Answer 1

I think that importing using one of the mentioned methods is ideal if it is a really big file, but you can use Excel to create insert statements:

="INSERT INTO table_name VALUES('"&A1&"','"&B1&"','"&C1&"')" 

In MS SQL you can use:

 SET NOCOUNT ON 

Discard all comments affected by "1 row affected". And if you make a lot of lines, and these are errors, put GO between statements every once in a while

Answer 2

You can create the appropriate table through the management studios interface and insert the data into the table, as shown below. This may take some time depending on the amount of data, but it is very convenient.

Answer 3

There is a handy tool that saves a lot of time on

http://tools.perceptus.ca/text-wiz.php?ops=7

You just need to specify the table name, field names and data tab - and click Go!

Answer 4

Depending on the database, you can export to CSV and then use the import method.

MySQL - http://dev.mysql.com/doc/refman/5.1/en/load-data.html

PostgreSQL - http://www.postgresql.org/docs/8.2/static/sql-copy.html

Answer 5

You can use VB to write something that will be output to the file line by line, adding sql around your data to the corresponding statements. I have done this before.

Answer 6

You can use the following excel statement:

 ="INSERT INTO table_name(`"&$A$1&"`,`"&$B$1&"`,`"&$C$1&"`, `"&$D$1&"`) VALUES('"&SUBSTITUTE(A2, "'", "\'")&"','"&SUBSTITUTE(B2, "'", "\'")&"','"&SUBSTITUTE(C2, "'", "\'")&"', "&D2&");" 

This is better than Hart CO's answer as it takes column names into account and eliminates compilation errors due to quotation marks in the column. The end column is an example of a numeric value column without quotes.

Answer 7

Here is another tool that works very well ...

http://www.convertcsv.com/csv-to-sql.htm

It can take values ​​separated by tabs and generate an INSERT script. Just copy and paste and in the parameters in step 2 check the box "The first row is the column names"

Then scroll down and in step 3, enter the name of your table in the "Schema.Table or View Name:" field

Pay attention to the deletion and creation of the table flags, and also make sure that you looked at the generated script before running.

This is the fastest and most reliable way I've found.

Answer 8

Here is a link to an online machine for converting CSV files to SQL Insert Into statements:

CSV-to-SQL

Answer 9

This query, which I created to insert Excel data files into a database. This identifier and price contain numerical values ​​and a date field. This query summarized all the types that I need. It may be useful to you too.

 ="insert into product (product_id,name,date,price) values("&A1&",'" &B1& "','" &C1& "'," &D1& ");" Id Name Date price 7 Product 7 2017-01-05 15:28:37 200 8 Product 8 2017-01-05 15:28:37 40 9 Product 9 2017-01-05 15:32:31 500 10 Product 10 2017-01-05 15:32:31 30 11 Product 11 2017-01-05 15:32:31 99 12 Product 12 2017-01-05 15:32:31 25 

Answer 10

There are tools that convert files on the Internet, but be careful which ones you use - some of them will publish the converted files on the Internet.

SQLizer.io is an SQL tool that does not store your data and does not publish it anywhere on the Internet.

Answer 11

I often had to create SQL scripts and add them to the original control and send them to the database administrator. I used this ExcelIntoSQL application from the Windows store https://www.microsoft.com/store/apps/9nblggh4v5gm It creates a complete script using "CREATE TABLE" and INSERTS.