[sql] Generate sql insert script from excel worksheet

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

Can I generate an SQL insert script from this excel worksheet?

There is a handy tool which saves a lot of time at

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

You just have to feed in the table name, field names and the data - tab separated and hit Go!

Use the ConvertFrom-ExcelToSQLInsert from the ImportExcel in the PowerShell Gallery

NAME
    ConvertFrom-ExcelToSQLInsert
SYNTAX
    ConvertFrom-ExcelToSQLInsert [-TableName] <Object> [-Path] <Object> 
      [[-WorkSheetname] <Object>] [[-HeaderRow] <int>] 
      [[-Header] <string[]>] [-NoHeader] [-DataOnly]  [<CommonParameters>]
PARAMETERS
    -DataOnly
    -Header <string[]>
    -HeaderRow <int>
    -NoHeader
    -Path <Object>
    -TableName <Object>
    -WorkSheetname <Object>
    <CommonParameters>
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer, PipelineVariable, and OutVariable. For more information, see
        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).
ALIASES
    None
REMARKS
    None

This query i have generated for inserting the Excel file data into database In this id and price are numeric values and date field as well. This query summarized all the type which I require It may useful to you as well

="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

Here is another tool that works very well...

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

It can take tab separated values and generate an INSERT script. Just copy and paste and in the options under step 2 check the box "First row is column names"

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

Pay attention to the delete and create table check boxes as well, and make sure you examine the generated script before running it.

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

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 improves upon Hart CO's answer as it takes into account column names and gets rid of compile errors due to quotes in the column. The final column is an example of a numeric value column, without quotes.

I have a reliable way to generate SQL inserts batly,and you can modify partial parameters in processing.It helps me a lot in my work, for example, copy one hundreds data to database with incompatible structure and fields count. IntellIJ DataGrip , the powerful tool i use. DG can batly receive data from WPS office or MS Excel by column or line. after copying, DG can export data as SQL inserts.

Depending on the database, you can export to CSV and then use an 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

You can use the below C# Method to generate the insert scripts using Excel sheet just you need import OfficeOpenXml Package from NuGet Package Manager before executing the method.

public string GenerateSQLInsertScripts() {

        var outputQuery = new StringBuilder();
        var tableName = "Your Table Name";
        if (file != null)
        {
            var filePath = @"D:\FileName.xsls";

            using (OfficeOpenXml.ExcelPackage xlPackage = new OfficeOpenXml.ExcelPackage(new FileInfo(filePath)))
            {
                var myWorksheet = xlPackage.Workbook.Worksheets.First(); //select the first sheet here
                var totalRows = myWorksheet.Dimension.End.Row;
                var totalColumns = myWorksheet.Dimension.End.Column;

                var columns = new StringBuilder(); //this is your columns

                var columnRows = myWorksheet.Cells[1, 1, 1, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());

                columns.Append("INSERT INTO["+ tableName +"] (");
                foreach (var colrow in columnRows)
                {
                    columns.Append("[");
                    columns.Append(colrow);
                    columns.Append("]");
                    columns.Append(",");
                }
                columns.Length--;
                columns.Append(") VALUES (");
                for (int rowNum = 2; rowNum <= totalRows; rowNum++) //selet starting row here
                {
                    var dataRows = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());

                    var finalQuery = new StringBuilder(); 
                    finalQuery.Append(columns);

                    foreach (var dataRow in dataRows)
                    {
                        finalQuery.Append("'");
                        finalQuery.Append(dataRow);
                        finalQuery.Append("'");
                        finalQuery.Append(",");
                    }
                    finalQuery.Length--;

                    finalQuery.Append(");");

                    outputQuery.Append(finalQuery);

                  }

            }
        }

return outputQuery.ToString();}

I had to make SQL scripts often and add them to source control and send them to DBA. I used this ExcelIntoSQL App from windows store https://www.microsoft.com/store/apps/9NH0W51XXQRM It creates complete script with "CREATE TABLE" and INSERTS.

You can create an appropriate table through management studio interface and insert data into the table like it's shown below. It may take some time depending on the amount of data, but it is very handy.

Here is a link to an Online automator to convert CSV files to SQL Insert Into statements:

CSV-to-SQL

You could use VB to write something that will output to a file row by row adding in the appropriate sql statements around your data. I have done this before.