解析excel拼接SQL 小脚本

由于数据同步原因，我们经常会受到对方给的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;
        }
    }




}