Convert excel table to sql script

Question

I have an excel table (.xls). I need to convert it to sql script. A single excel worksheet consists of several tables. Thus, the resulting script should have several create table and insert statements. I tried various tools like http://www.sqlconverter.com/ , but I can not find the right solution. How can i do this?

Answer 1

I noticed your comment that using the import wizard was a more complicated solution than you wanted, so you should try to load the data.

You can try BULK INSERT :

First create SAVE AS on each sheet and convert them to CSV files. You must have one CSV file for each sheet that you want to import.

Then create a table with similar data types and lengths that you will enter. A typical Excel cell is VARCHAR (255) (probably more like NVARCHAR (255) if you want to be specific, but we will avoid using unicode for this solution).

So if your excel sheet had 5 columns:

 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 into a table. PROVIDES that you have a file on a network share or a local server / machine where the SQL instance is. For example, if you have a file on your computer and you want to try to click on a server on the network, SQL will think that C:\ in the script below was on the server and not on your computer. You will need to open the folder and access it over the network: \\MyMachineName\SharedFolder\Sheet1.csv

 BULK INSERT dbo.Sheet1 FROM 'C:\LocalFolder\WhereTheFileIs\Sheet1.csv' WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' ) 

This should get the data in this table, provided that the file and the table have the same number of columns.

It is not very, but it is simple. BULK INSERT is a proven and reliable method for basic and fast loading.

Answer 2

The sqlizer.io online tool does this, including converting tables from XLSX, CSV, or JSON to SQL insert scripts complete with table definitions.

Answer 3

There is also a simple way to group insertions from Excell: simply, if your data is in columns B, C and D in the sepearate column, create a formula: = "insert into values ​​('' & B1 &" ',' 'and C1 and' ' , '' & D1 & "')

Answer 4

this is created by wammie krsna masore 1.apache apoi should be used, it should be added to the apache apoi files and system that I suspected of this project

  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 

Answer 5

I created this utility to facilitate such tasks.

https://github.com/ngudbhav/TriCo-electron-app/releases/latest

The software is equipped with features such as safe mode, automatic identifier, and file write mode.