convert excel worksheet to sql script

Question

I have a excel worksheet (.xls). I need to convert it into sql script. The single excel worksheet consists of multiple tables. So the resultant script should have multiple create table and insert statements. I tried various tools such as http://www.sqlconverter.com/ but I am unable to get a proper solution. Any other way I can do it?

Answer 1

I noticed your comment that using the import wizard was more complicated of a solution than you wanted, so you must be trying to load data.

You can try BULK INSERT:

First, do a SAVE AS on each sheet and convert them to CSV files. You should have one CSV file for each sheet you want to import.

Next, make a table with the similar data types and length that you'll be bringing in. A typical Excel cell is a VARCHAR(255), (probably more like NVARCHAR(255) if you want to be specific, but we'll avoid unicode for this solution).

So, if your excel sheet had 5 columns:

CREATE TABLE Sheet1
(Column1 VARCHAR(255)
, Column2 VARCHAR(255)
, Column3 VARCHAR(255)
, Column4 VARCHAR(255)
, Column5 VARCHAR(255)
)

Then you can write a simple bulk insert to the table PROVIDED you have the file on network share or local the to server/machine where the SQL instance is. For example, if you had the file on your machine and wanted to try and push to a server out on the network, SQL would think the C:\ in the script below was on the server and not your machine. You would have to share a folder and access it over the network: \\MyMachineName\SharedFolder\Sheet1.csv

BULK INSERT dbo.Sheet1
FROM 'C:\LocalFolder\WhereTheFileIs\Sheet1.csv'
WITH (
FIELDTERMINATOR = ','
, ROWTERMINATOR = '\n'
)

This should get the data into that table provided the same number of columns exist in the file and table.

It's not pretty, but it's simple. The BULK INSERT is a tried and true method of basic and quick loading.