blocks|key|91180|text|我认为如果它确实是一个大文件，那么使用上述方法之一导入是理想的，但您可以使用Excel创建insert语句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91181|="INSERT+INTO+table_name+VALUES('"&A1&"','"&B1&"','"&C1&"')"|code-block|syntax|javascript|91182|在MS+SQL中，您可以使用：|91183|SET+NOCOUNT+ON|91184|放弃显示所有“1行受影响”的注释。如果您正在执行大量的行操作，并且错误输出，请在语句之间每隔一段时间使用GO|91185|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre><code>="INSERT INTO table_name VALUES('"&amp;A1&amp;"','"&amp;B1&amp;"','"&amp;C1&amp;"')"
</code></pre>

In MS SQL you can use:

<pre><code>SET NOCOUNT ON
</code></pre>

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

blocks|key|308433|text|有一个方便的工具，它可以节省大量的时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|308434|http://tools.perceptus.ca/text-wiz.php?ops=7|offset|length|308435|您只需输入表名、字段名和数据制表符，然后点击Go！|308436|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|18|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

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

<a href="http://tools.perceptus.ca/text-wiz.php?ops=7" rel="noreferrer">http://tools.perceptus.ca/text-wiz.php?ops=7</a>

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

blocks|key|91273|text|您可以通过management+studio界面创建一个合适的表，并将数据插入到表中，如下所示。根据数据量的不同，这可能需要一些时间，但这是非常方便的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91274|​|91275|📷|atomic|offset|length|91276|91277|91278|91279|91280|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/87a7af041bb97c7880ffb635043c83be.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/c55fe7196fbf3bef2adbe4d5d2fc54b7.png^0|0|0|0|1|0|0|0|0|0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@$G|10|H|11|1|12]]|A|$]]|$1|I|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|J|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|K|3|E|5|F|7|15|8|@]|9|@$G|16|H|17|1|18]]|A|$]]|$1|L|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T|U|-4]]|V|$5|P|Q|R|A|$S|W|U|-4]]]]

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.

<img src="https://i.stack.imgur.com/MoV88.png" alt="enter image description here">

<img src="https://i.stack.imgur.com/RrP56.png" alt="enter image description here">

blocks|key|308608|text|您可以使用以下excel语句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|308609|="INSERT+INTO+table_name(`"&$A$1&"`,`"&$B$1&"`,`"&$C$1&"`,+`"&$D$1&"`)+VALUES('"&SUBSTITUTE(A2,+"'",+"\'")&"','"&SUBSTITUTE(B2,+"'",+"\'")&"','"&SUBSTITUTE(C2,+"'",+"\'")&"',+"&D2&");"|code-block|syntax|javascript|308610|这改进了Hart+CO的回答，因为它考虑了列名，并消除了由于列中的引号而导致的编译错误。最后一列是不带引号的数值列的示例。|308611|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can use the following excel statement:
<pre><code>=&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;
</code></pre>
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.

blocks|key|308274|text|根据数据库的不同，您可以导出到CSV，然后使用导入方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|308275|MySQL+-+http://dev.mysql.com/doc/refman/5.1/en/load-data.html|offset|length|308276|PostgreSQL+-+http://www.postgresql.org/docs/8.2/static/sql-copy.html|308277|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/load-data.html|1|http://www.postgresql.org/docs/8.2/static/sql-copy.html^0|0|8|1H|0|0|D|1J|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]|P|$5|K|L|M|A|$N|Q]]]]

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

MySQL - <a href="http://dev.mysql.com/doc/refman/5.1/en/load-data.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.1/en/load-data.html</a>

PostgreSQL - <a href="http://www.postgresql.org/docs/8.2/static/sql-copy.html" rel="noreferrer">http://www.postgresql.org/docs/8.2/static/sql-copy.html</a>

blocks|key|91155|text|您可以使用VB编写一些内容，逐行输出到文件中，并在数据周围添加适当的sql语句。我以前也这么做过。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91156|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|308627|text|使用ConvertFrom-ExcelToSQLInsert库中ImportExcel中的PowerShell|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|308628|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
EXAMPLE
++++ConvertFrom-ExcelToSQLInsert+MyTable+.\testSQLGen.xlsx|code-block|syntax|javascript|308629|entityMap|0|LINK|mutability|MUTABLE|url|https://www.powershellgallery.com/packages/ImportExcel^0|2|S|W|B|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Use the <code>ConvertFrom-ExcelToSQLInsert</code> from the <a href="https://www.powershellgallery.com/packages/ImportExcel" rel="nofollow noreferrer">ImportExcel</a> in the PowerShell Gallery
<pre><code>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
EXAMPLE
 ConvertFrom-ExcelToSQLInsert MyTable .\testSQLGen.xlsx
</code></pre>

blocks|key|91390|text|以下链接指向在线自动化程序，可将CSV文件转换为SQL+Insert+Into语句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91391|CSV-to-SQL|offset|length|91392|entityMap|0|LINK|mutability|MUTABLE|url|http://csv-to-sql.herokuapp.com/^0|0|0|A|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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

<a href="http://csv-to-sql.herokuapp.com" rel="nofollow">CSV-to-SQL</a>

blocks|key|308529|text|我生成的这个查询是为了将Excel文件数据插入到数据库中，它的id和price都是数字值和日期字段。这个查询总结了我需要的所有类型，它可能对您也很有用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|308530|="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|code-block|syntax|javascript|308531|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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 

<pre><code>="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
</code></pre>

blocks|key|91503|text|我必须经常编写SQL脚本，并将它们添加到源代码控制中，然后将它们发送给DBA。我使用这个ExcelIntoSQL应用程序从windows商店https://www.microsoft.com/store/apps/9NH0W51XXQRM它创建了完整的脚本与“创建表”和插入。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|91504|entityMap|0|LINK|mutability|MUTABLE|url|https://www.microsoft.com/store/apps/9NH0W51XXQRM^0|1Y|1D|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

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 <a href="https://www.microsoft.com/store/apps/9NH0W51XXQRM" rel="nofollow noreferrer">https://www.microsoft.com/store/apps/9NH0W51XXQRM</a>
It creates complete script with "CREATE TABLE" and INSERTS.

blocks|key|308660|text|您可以使用下面的C#方法来生成使用Excel+sheet的插入脚本，只需在执行该方法之前从NuGet包管理器导入OfficeOpenXml包。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|308661|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["%2B+tableName+%2B"]+(");
++++++++++++++++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%2B%2B)+//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();}|code-block|syntax|javascript|308662|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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();}
</code></pre>

blocks|key|308726|text|也许最快的方法是使用在线工具，比如这个convert+Excel+to+insert+SQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|308727|只需粘贴(从Microsoft+Excel、Google+Sheets、Mac+Numbers或网页中复制表格)或将Excel拖放到Data+Source的文本区，它就会立即执行转换的魔术。|308728|​|308729|📷|atomic|308730|308731|entityMap|0|LINK|mutability|MUTABLE|url|https://tableconvert.com/excel-to-sql|1|https://tableconvert.com/excel-to-sql#DataSource|2|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/c76a52b6777cb51e0776b96e0e22b6e0.png|imageAlt^0|J|R|0|0|1U|B|1|0|0|0|1|2|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@$A|12|B|13|1|14]]|C|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@$A|16|B|17|1|18]]|C|$]]|$1|F|3|G|5|6|7|19|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|1A|8|@]|9|@$A|1B|B|1C|1|1D]]|C|$]]|$1|K|3|G|5|6|7|1E|8|@]|9|@]|C|$]]|$1|L|3|-4|5|6|7|1F|8|@]|9|@]|C|$]]]|M|$N|$5|O|P|Q|C|$R|S]]|T|$5|O|P|Q|C|$R|U]]|V|$5|W|P|X|C|$Y|Z|10|-4]]]]

Perhaps the quickest way is to use online tools, Like this <a href="https://tableconvert.com/excel-to-sql" rel="nofollow noreferrer">convert Excel to insert SQL</a>.
Just paste (copy tables from Microsoft Excel, Google Sheets, Mac Numbers or a web page) or drag-and-drop your Excel into the textarea of <a href="https://tableconvert.com/excel-to-sql#DataSource" rel="nofollow noreferrer">Data Source</a>, and it will immediately perform the magic of the conversion.
<a href="https://i.stack.imgur.com/r84fx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/r84fx.png" alt="enter image description here" /></a>

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?

Generate sql insert script from excel worksheet

我有一个很大的excel工作表要添加到我的数据库中。是否可以从此excel工作表生成SQL插入脚本？

问从excel工作表生成sql插入脚本
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从excel工作表生成sql插入脚本EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从excel工作表生成sql插入脚本
EN