由于数据同步原因,我们经常会受到对方给的excel文件去同步字段到数据库
这种情况遇到过多次,如果数据量小还好,手动拼一下Sql,如果对方给的同步数据过大,那么我采用了一个小脚本处理这件事
其实非常简单,读取同步excel,同时根据字段转存为sql文件,涉及到文件边读边写,然后处理拼接sql的操作
下面是 csv文件转sql语句
package com.group.wordskey.file;
import org.apache.commons.lang3.StringUtils;
import java.awt.*;
import java.io.*;
/**
* Description:
*
*
* @date 2020/8/24 13:37
*/
public class FileReadSql{
public static void main(String[] args) throws IOException{
String path = "C:\\Users\\10015467\\Desktop\\poi\\sit01\\sit01.csv";
String outPath = "C:\\Users\\10015467\\Desktop\\poi\\sit01\\200827b-aaa-DDL.sql";
readFile(path,outPath);
}
public static StringBuilder readFile(String path,String outPath) throws IOException{
File file = new File(path);
File outFile = new File(outPath);
StringBuilder result = new StringBuilder();
OutputStream outputStream = null;
BufferedReader br = null;
try{
outputStream = new FileOutputStream(outFile);
br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-8"));
int index = 0;
String s = null;
while((s = br.readLine())!=null){
if(index != 0 ){
System.out.println(s);
}
index++;
if(index == 1){
continue;
}
String[] split = s.split(",");
int len = split.length - 1;
//字段1
String sku = formatStr(split[0]);
//字段2
String startDate = formatStr(split[1]);
//字段3
String deliveryTimeType = formatStr(split[2]);
//字段4
Integer clothesCount = Integer.valueOf(split[3]);
//字段5
Integer beltState = Integer.valueOf(split[4]);
//字段6
String seriesBrandName = "\'\'";
if(len >= 5){
seriesBrandName = formatStr(split[5]);
if(StringUtils.isBlank(seriesBrandName)){
seriesBrandName = "\'\'";
}
}
//字段7
String consumablesTypeName = "\'\'";
if(len >= 6){
consumablesTypeName = formatStr(split[6]);
if(StringUtils.isBlank(consumablesTypeName)){
consumablesTypeName = "\'\'";
}
}
//字段8 ,因为这里是一个逗号分隔的字符串,所以放在最后解析,重新拼接
//重新拼接成一个逗号分隔的字符串
String colors = "\'\'";
if(len >= 7){
//提取颜色字段
if(len == 7){
colors = formatStr(split[7]);
}else {
StringBuffer sb = new StringBuffer();
for(int i = 7;i < split.length;i++){
if(i == len){
sb.append(split[i]);
}else {
sb.append(split[i]).append(",");
}
}
colors = sb.toString();
}
if(StringUtils.isBlank(colors)){
colors = "\'\'";
}
}
String sql = "";
sql = "UPDATE basic_sku_extend set start_date = %S,delivery_time_type = %S,clothes_count=%S,belt_state=%S," +
"colors=%S,series_brand_name=%S,consumables_type_name=%S WHERE sku = %S;";
String format = String.format(sql,startDate,deliveryTimeType,clothesCount,beltState,colors,seriesBrandName,consumablesTypeName,sku);
//写入文件
format = format + System.lineSeparator();
outputStream.write(format.getBytes("UTF-8"));
result.append(format + System.lineSeparator());
}
}catch(Exception e){
e.printStackTrace();
}finally{
br.close();
outputStream.close();
}
return result;
}
public static String formatStr(String str){
String result = "\'\'";
if(str == null || result.equals(str)){
return result;
}else {
str = "\'" + str + "\'";
return str;
}
}
}