CSV文件转换SQL文件

有段时间没用csdn了，今天随手写个csv文件转sql文件的方法，以便以后有机会用到。

其实csv转sql有多种方式，比如直接利用mysql客户端工具去转、通过代码方法转等

这里先记录下用java代码方式去转换下，转换方法如下：

package com.swhysc.ubsp.service.business.service.impl.paperless;
import lombok.SneakyThrows;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
/**
 * @ClassName CsvToSqlConverter
 * @Description csv转sql
 * @Author xqy
 * @Date 2023/8/21 14:39
 * @Version 1.0
 */
public class CsvToSqlConverter {

    public static void main(String[] args) {
        String csvFilePath = "C:\\Users\\MI\\Desktop\\密码重置\\20230816-layout_lht1.csv"; // 输入CSV文件路径
        String sqlFilePath = "C:\\Users\\MI\\Desktop\\密码重置\\20230816-layout_lht1.sql"; // 输出SQL文件路径

        List<String> sqlStatements = convertCsvToSql(csvFilePath);

        if (writeSqlFile(sqlFilePath, sqlStatements)) {
            System.out.println("SQL file generated successfully!");
        } else {
            System.out.println("Failed to generate SQL file.");
        }
    }

    @SneakyThrows
    public static List<String> convertCsvToSql(String csvFilePath) {
        List<String> sqlStatements = new ArrayList<>();

        //BufferedReader reader = new BufferedReader(new FileReader(csvFilePath));
        InputStreamReader isr = new InputStreamReader(new FileInputStream(csvFilePath), "GB2312");
        try (BufferedReader reader=new BufferedReader(isr)) {
            String line;
            while ((line = reader.readLine()) != null) {
                String[] values = line.split(",");
                String tableName = "ubsp_layout.business_process"; // 设置要插入的目标表名

                StringBuilder sqlStatement = new StringBuilder();
                sqlStatement.append("INSERT INTO ").append(tableName).append(" VALUES (");

                for (String value : values) {
                    value = value.replace("\"","");
                    sqlStatement.append("'").append(value).append("',");
                }

                sqlStatement.deleteCharAt(sqlStatement.length() - 1); // 删除最后一个逗号
                sqlStatement.append(");");

                sqlStatements.add(sqlStatement.toString());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return sqlStatements;
    }

    @SneakyThrows
    public static boolean writeSqlFile(String sqlFilePath, List<String> sqlStatements) {
        //FileWriter writer = new FileWriter(sqlFilePath);;
        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(sqlFilePath), "UTF-8"))) {
            for (String statement : sqlStatements) {
                writer.write(statement);
                writer.write("\n");
            }

            return true;
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
    }
}