Select the columns you want to generate, format the tables, select the options that contain the headings, and then, in the second row of the rightmost column, click Function, select Concatenate, and enter the desired structure in the text.
| The code is as follows |
Copy Code |
| , =concatenate ("(", [@id], "', '", [@name], "'),") |
The following generated statements can be written as
| The code is as follows |
Copy Code |
| Insert table1 (id,name) VALUES (' 1 ', "Test 1"), (' 2 ', "Test 2") ... |
If you will C # we can use OLE DB to read Excel and generate SQL statements
| The code is as follows |
Copy Code |
| Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.IO;
Using System.Reflection;
Using Excel = Microsoft.Office.Interop.Excel;
Using System.Data;
Using System.Data.OleDb;
Using System.Windows.Forms;
Namespace Readxlsxdata
{
Static Class Parsexlsx
{
public static readonly int comment_index=4; Field Description Row subscript
public static readonly int key_index = 5; Primary Key line Subscript
public static readonly int type_index = 6; Field type row subscript
public static readonly int sqlname_index = 7; database field name line subscript
public static readonly int value_index = 8; Value Line subscript
public static StringBuilder Objectdata = new StringBuilder ();
public static DataTable Todataset (String filePath)
{
String connstr = "";
String fileType = System.IO.Path.GetExtension (FilePath);
if (string. IsNullOrEmpty (fileType)) return null;
if (FileType = ". xls")
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0" + "Data source=" + FilePath + ";" + "; Extended properties= "Excel 8.0; Hdr=no;imex=1 "";
Else
ConnStr = "provider=microsoft.ace.oledb.12.0" + "Data source=" + FilePath + ";" + "; Extended properties= "Excel 12.0; Hdr=no;imex=1 "";
String sql_f = "SELECT * from [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable datatable = new DataTable ();
Try
{
Initialize the connection and open the Www.111cn.net
conn = new OleDbConnection (CONNSTR);
Conn. Open ();
da = new OleDbDataAdapter ();
Da. SelectCommand = new OleDbCommand (String.Format (Sql_f, "sheet1$"), conn);
Da. Fill (dataTable);
}
catch (Exception ex)
{ }
Finally
{//close connection
IF (Conn. state = = ConnectionState.Open)
{
Conn. Close ();
Da. Dispose ();
Conn. Dispose ();
}
}
Conn. Close ();
Da. Dispose ();
Conn. Dispose ();
return dataTable;
}
public static string Readexcelfile (String namef, String sqlfile, String sqlcomment)
{
Objectdata.clear ();
DataTable dt = Todataset (NAMEF);
string temp, KEY,TEMP1,TEMP2;
List<int> index = new list<int> ();
To create a table header
Objectdata.append ("DROP TABLE IF EXISTS '" + sqlfile + "'; n");
Objectdata.append ("CREATE TABLE" + Sqlfile + "' (n");
int columnsize = dt. Columns.count;
int rowsize = dt. Rows.Count;
DataColumn DC;
DataRow Dr;
temp = string. Empty;
Key = String. Empty;
Temp1 = string. Empty;
Temp2 = string. Empty;
DataRow DR5 = dt. Rows[comment_index],dr9=dt. Rows[sqlname_index],dr8=dt. Rows[type_index];
for (int i = 1; i < columnsize; i++)
{
DC = dt. Columns[i];
Temp2 = DR5[DC]. ToString ();
Temp1 = DR9[DC]. ToString ();
if (Temp2 = = string. Empty)//Empty column judgment
Break
else if (Temp1. ToString ()!= string. EMPTY)//Database fields
{
Index. ADD (i);
temp = DR8[DC]. ToString ();
if (temp. Contains ("Vachar"))
Objectdata.append ("t '" + Temp1 + "'" + temp + "not NULL DEFAULT ' COMMENT '" + Temp2 + "', n");
Else
Objectdata.append ("t '" + Temp1 + "'" + temp + "not NULL DEFAULT ' 0 ' COMMENT '" + Temp2 + "', n");
temp = dt. ROWS[KEY_INDEX][DC]. ToString ();
if (temp!= null && temp. Contains ("key"))
{
Key + + "" + Temp1 + "'"; } }
}
if (key!=string. Empty)
Objectdata.append ("Tprimary key (" + key + ") n");
Objectdata.append (") Engine=innodb DEFAULT Charset=utf8 comment= '" + sqlcomment + "'; n");
for (int i = Value_index i < rowsize; i++)/Read data record
{
Objectdata.append ("INSERT into" "+ Sqlfile +" ' VALUES (');
Dr = dt. Rows[i];
int length = index. Count;
for (int j = 0; J < length; J + +)
{
Objectdata.append (Dr[index[j]] + "', '");
}
Objectdata.remove (objectdata.length-3, 2);
Objectdata.append ("); n");
}
return objectdata.tostring ();
}
} |
}
The final export results are as follows
