Generate sql insert script from excel worksheet

Question

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

User · Answer

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   amp A1 amp       amp B1 amp         amp C1 amp        amp D1 amp             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

User · Answer

You can use the following excel statement    quot INSERT INTO table name   quot  amp  A 1 amp  quot     quot  amp  B 1 amp  quot     quot  amp  C 1 amp  quot      quot  amp  D 1 amp  quot    VALUES   quot  amp SUBSTITUTE A2   quot   quot    quot    quot   amp  quot     quot  amp SUBSTITUTE B2   quot   quot    quot    quot   amp  quot     quot  amp SUBSTITUTE C2   quot   quot    quot    quot   amp  quot     quot  amp D2 amp  quot    quot   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

User · Answer

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    amp A1 amp       amp B1 amp       amp C1 amp        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

User · Answer

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

User · Answer

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   gt  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  lt   totalRows  rowNum      selet starting row here                                       var dataRows   myWorksheet Cells rowNum  1  rowNum  totalColumns  Select c   gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Use the ConvertFrom-ExcelToSQLInsert from the ImportExcel in the PowerShell Gallery  NAME     ConvertFrom-ExcelToSQLInsert SYNTAX     ConvertFrom-ExcelToSQLInsert  -TableName   lt Object gt   -Path   lt Object gt           -WorkSheetname   lt Object gt     -HeaderRow   lt int gt            -Header   lt string   gt    -NoHeader   -DataOnly     lt CommonParameters gt   PARAMETERS     -DataOnly     -Header  lt string   gt      -HeaderRow  lt int gt      -NoHeader     -Path  lt Object gt      -TableName  lt Object gt      -WorkSheetname  lt Object gt       lt CommonParameters gt          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

User · Answer

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

[sql] Generate sql insert script from excel worksheet

Examples related to sql

Examples related to excel