SQL 将excel工作表转换为sql脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13067370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
convert excel worksheet to sql script
提问by user1402867
I have a excel worksheet (.xls). I need to convert it into sql script. The single excel worksheet consists of multiple tables. So the resultant script should have multiple create table and insert statements. I tried various tools such as http://www.sqlconverter.com/but I am unable to get a proper solution. Any other way I can do it?
我有一个 Excel 工作表 (.xls)。我需要将其转换为 sql 脚本。单个 Excel 工作表由多个表组成。所以生成的脚本应该有多个 create table 和 insert 语句。我尝试了各种工具，例如http://www.sqlconverter.com/，但我无法获得正确的解决方案。我还有什么办法可以做到吗？
回答by Vinnie
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:
你可以试试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.
首先，在每张纸上做一个另存为并将它们转换为 CSV 文件。每个要导入的工作表都应该有一个 CSV 文件。
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).
接下来，使用您将引入的类似数据类型和长度制作一个表格。典型的 Excel 单元格是 VARCHAR(255)，（如果您想具体一点，可能更像 NVARCHAR(255)，但我们会避免使用 unicode 用于此解决方案）。
So, if your excel sheet had 5 columns:
因此，如果您的 Excel 表有 5 列：
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
然后，您可以向表中写入一个简单的批量插入，前提是您拥有网络共享上的文件或 SQL 实例所在的本地服务器/机器。例如，如果您的机器上有该文件，并想尝试将其推送到网络上的服务器，SQL 会认为C:\下面脚本中的文件位于服务器上，而不是您的机器上。您必须共享一个文件夹并通过网络访问它：\\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 INSERTis a tried and true method of basic and quick loading.
它不漂亮，但很简单。这BULK INSERT是一种久经考验的基本和快速加载方法。
回答by Yasskier
There is also a simple method to group insert from Excell: simply if your data is in columns B,C and D in sepearate column create formula: ="insert into values('" &B1 &"','" & C1 & "','"&D1&"')
还有一个简单的方法可以从 Excell 中对插入进行分组：如果您的数据在单独的列中的 B、C 和 D 列中，则创建公式：="插入值('" &B1 &"','" & C1 & "' ,'"&D1&"')
回答by vamsi krishnan mysore
this is created by vamsi krishna mysore 1.apache apoi should be used it should be added to the files and system apache apoi i sused in this project
这是由 vamsi krishna mysore 创建的 1.apache apoi 应该使用它应该添加到我在这个项目中使用的文件和系统 apache apoi
package excelread;
import java.io.File;
import java.io.FileOutputStream;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.UnderlinePatterns;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
public class ExcelRead
{
public static void main(String[] args) throws Exception
{
try
{
FileOutputStream output=new FileOutputStream("result.docx");
FileOutputStream output=new FileOutputStream("result.sql");//sql script in the script
FileOutputStream output=new FileOutputStream("result.xlxs");
FileOutputStream output=new FileOutputStream("result.csv");
XWPFDocument doc=new XWPFDocument();
XWPFParagraph para=doc.createParagraph();
para.setAlignment(ParagraphAlignment.CENTER);
XWPFRun pararun=para.createRun();
pararun.setBold(true);
pararun.setFontSize(20);
pararun.setText("Database Tables\n\n");
File f= new File("C:\Users\admin\Desktop\BUILDING_TB.xls");//file location where it is stored in the system
Workbook wb= Workbook.getWorkbook(f);
int sheets=wb.getNumberOfSheets();
for(int s1=0;s1<sheets;s1++)
{
System.out.println("for sheet"+s1);
Sheet s= wb.getSheet(s1);
String tbname=s.getName();
XWPFParagraph para1=doc.createParagraph();
para1.setAlignment(ParagraphAlignment.LEFT);
pararun=para1.createRun();
pararun.setText(tbname);
pararun.setFontSize(16);
pararun.setUnderline(UnderlinePatterns.WORDS);
int rows=s.getRows();
int cols=s.getColumns();
int indexrows=0;
int cols1=0;
int indexcols=0;
int pk=1000,dt=1000,cn=1000,ci=1000,dd=1000,n=1000,com=1000;
int ava=0;
List <String> comments= new LinkedList <String>();
List <String> sequence= new LinkedList <String>();
List <String> cid= new LinkedList <String>();
String createQuery="create table " +tbname+"(";
System.out.println(rows+" "+cols);
for(int j=0;j<rows;j++) //TO AVOID EMPTY ROW AND COLUMNS
{
sequence.clear();
for(int i=0;i<cols;i++) //TO GET ONE ROW DETAILS
{
indexcols=0;
cols1=0;
Cell c=s.getCell(i,j);
sequence.add(c.getContents());
}
for(int i=0;i<cols;i++)
{
if(sequence.get(i)=="")
{
cols1= ++indexcols;
}
else
{
ava=1;
indexrows=j;
break;
}
}
if(ava==1)
break;
}
for(;indexcols<cols;indexcols++) //TO ARRANG DATA IN REQUIRED ORDER
{
if(sequence.get(indexcols).toLowerCase().contains("PK".toLowerCase()))
{
pk=indexcols;
}
else if(sequence.get(indexcols).toLowerCase().contains("Column_id".toLowerCase()))
{
ci=indexcols;
}
else if(sequence.get(indexcols).toLowerCase().contains("Column_Name".toLowerCase()))
{
cn=indexcols;
}
else if(sequence.get(indexcols).toLowerCase().contains("nullable".toLowerCase()))
{
n=indexcols;
}
else if(sequence.get(indexcols).toLowerCase().contains("Data_TYpe".toLowerCase()))
{
dt=indexcols;
}
else if(sequence.get(indexcols).toLowerCase().contains("Default".toLowerCase()))
{
dd=indexcols;
}
else if(sequence.get(indexcols).toLowerCase().contains("comments".toLowerCase()))
{
com=indexcols;
}
}
indexrows++;
int rows1=indexrows;
for(;indexrows<rows;indexrows++) //PREPARING QUERY(For excel rows which contain data)
{
indexcols=cols1;
for(;indexcols<cols;indexcols++) //for all columns
{
Cell c=s.getCell(indexcols, indexrows);
String item=c.getContents();
//adding Column name to query
if(indexcols==cn)
{ if(!(item.equals("")) && indexrows!=rows1)
createQuery =createQuery+" ,"+item;
else if(item.equals(""))
break;
else
createQuery =createQuery+" "+item;
}
//adding data type to query
if(indexcols==dt)
{
createQuery =createQuery+" "+item;
}
//adding data default to query
else if(indexcols==dd)
{
if(item=="")
continue;
else
createQuery =createQuery+" "+"default "+item;
}
//addig primary key constaint to query
else if(indexcols==pk)
{
if(item.equalsIgnoreCase("true"))
createQuery =createQuery+" "+"primary key";
else
createQuery =createQuery+" "+"";
}
//adding not null constraint to query
else if(indexcols==n)
{
if(item.equalsIgnoreCase("no"))
createQuery =createQuery+" "+"not null";
else
createQuery =createQuery+" "+"";
}
//adding comments
else if(indexcols==com)
{
if(item!="")
{
comments.add(item);
}
else
{
comments.add("comments empty");
}
}
else if(indexcols==ci)
{
if(item!=null)
{
cid.add(item);
}
}
}//column loop close
}//row looop close
createQuery=createQuery+")";
System.out.println(createQuery);
XWPFParagraph para2=doc.createParagraph();
para2.setAlignment(ParagraphAlignment.LEFT);
pararun=para2.createRun();
pararun.setFontSize(14);
pararun.setText(createQuery+";");
System.out.println("table created successfully");
}//sheets loop closse
doc.write(output); //writing data into ouptu file
output.close();
}//try block close
catch(Exception e)
{
System.out.println(e.toString());
}
}//main close
}//class close
回答by ngudbhav
I created this utility to ease these kind of tasks.
我创建了这个实用程序来简化这些任务。
https://github.com/ngudbhav/TriCo-electron-app/releases/latest
https://github.com/ngudbhav/TriCo-electron-app/releases/latest
The software is equipped with features such as safe-mode, auto-id and write-to-file mode.
该软件配备了安全模式、自动识别和写入文件模式等功能。
