Convert Excel Worksheet to SQL Script

convert excel worksheet to sql script

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.

Generate sql insert script from excel worksheet

I think importing using one of the methods mentioned is ideal if it truly is a large 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

To forego showing all the '1 row affected' comments. And if you are doing a lot of rows and it errors out, put a GO between statements every once in a while

How do I convert Excel data into a SQL insert?

You have different options depending on which server you are using, but with your current info you could do the following.

1. click on the first empty column in the first row i.e. if data is in row 2 and all columns go from a to k, then you select L2

2. Paste the following code

   ="INSERT INTO Address (AddressTypeID, Address1,City, State, Zip, Phone, PersonName, DisplayName, IsDefault, StatusID, UserID) VALUES '"&A2&"', '"&B2&"', '"&C2&"', '"&D2&"', '"&E2&"', '"&F2&"', '"&G2&"', '"&H2&"', '"&I2&"', '"&J2&"', '"&K2&"'"
   

3. Check to see if the query is correct, it's calculated with the fields from a2-k2)

4. copy -> paste special values only to the next column, so you keep your code, but you can select all rows with the calculated results to your sql query tool

how to convert an Excel file to SQL insert statements

An easy way to do that is to convert the excel file to a format that's easy to read, like a CSV file. To do so, open your file in excel and export it to a CSV file.

After that, you will be able to read it in PHP using fopen() and fgetcsv() to insert them using regular SQL access commands.

Extras:

As CSV files are like this,

"Text field",12,45,87.32,"Another text field"
"I am another row",43,12,98.2,"And another text field"

if you are sure your data is safe from any potential injection attempt you can use basic string operations to generate SQL statements as well with plain old fread():

$sql = 'INSERT INTO `MyTable` VALUES('.$fileRow.')';

Which will make something like this:

INSERT INTO `MyTable` VALUES("Text field",12,45,87.32,"Another text field")
INSERT INTO `MyTable` VALUES("I am another row",43,12,98.2,"And another text field")

Example (untested) script:

$file = fopen('data.csv', 'r');

while( ($line = fread($file)) !== false ) {
    $sql = 'INSERT INTO `MyTable` VALUES('.$line.')';
    // Do something here, like a PDO query or whatever you use
}

How to import data from excel file into sql server DB by script

After many google searches and my many attempts I found the correct code:

declare @SQL nvarchar(max) = '

CREATE TABLE #TempTable
( [Field1] nvarchar(max) NULL,
    [Field2] nvarchar(max) NULL,
    [Field3] nvarchar(max) NULL ); 

BULK INSERT #TempTable  FROM ''<FullPath>\FileName.csv'' WITH --if the path is in the network - need to write the Full-path of the drive
(
KEEPIDENTITY,
FIELDTERMINATOR = '','',
MAXERRORS = 10000,
KEEPNULLS, 
ROWTERMINATOR=''\n'',
FIRSTROW = 2,
CODEPAGE = ''1255''
);

select * from #TempTable
Insert into TableNameInDB(Field1,Field2,Field3)
select * from #TempTable
'

EXEC sp_executesql @SQL

Convert Excel File to CSV Then Load to SQL Table Using SSIS

My package had no problem. The issue is with the excel file . For small data the extension .xlsx works ( excel 2007) . For the large data i have to convert the data to Excel 97 version which is (.xls). Doing so help load the data

---

Related Topics