JS 实现CSV文件转换SQL文件小工具

fengyehongWorld

已于 2023-07-08 10:19:46 修改

阅读量615

点赞数

分类专栏： # JS 文章标签： javascript sql

于 2023-07-08 10:16:55 首次发布

本文链接：https://blog.csdn.net/feyehong/article/details/131608425

版权

JS 专栏收录该内容

52 篇文章 1 订阅

订阅专栏

一. 需求

最近在项目中遇到一个问题，客户提供的数据是CSV格式的，
需要将CSV文件中的数据转换为SQL语句文件。

😅由于本人不会Excel的vba编程，因此决定使用JS来实现。

二. 实现思路

提供一个文件上传框，支持多文件上传，且只能上传csv文件
使用new FileReader()来读取上传的csv文件
每个csv文件都包含标题栏，因此需要从第二行开始读取数据
将读取到的数据构造成insert的sql语句然后存放到一个list中
创建Blob对象，将包含sql的list放入该对象中构建sql文本对象
通过URL.createObjectURL()获取Blob对象在内存中的地址
创建a标签，配合内存中的地址实现sql文件下载

三. CSV文件例子

"ＣＱ企画番号","共同購入商品コード","ＣＱ商品コード","ＪＡＮコード","定催区分","部門コード","大分類","中分類","小分類","集品区分","商品名漢字","規格名漢字","入数","納品時容器区分","シール貼付有無区分","資材名","税抜組合員単価","税込組合員単価","企画年月回","配達年月回","企画単協　エフ","企画単協　さが","企画単協　ララ","企画単協　おおいた","企画単協　水光社","企画単協　みやざき","企画単協　かごしま","企画単協　おきなわ","税コード","消費税率","単価計算区分","税抜計算区分","税込計算区分","禁則チェック区分","エラーコード","削除フラグ","作成日","作成時刻","更新日","更新時刻"
"301","1005405","100317290","2000000432908","1","02","01","02","09","02","フレンドリーバナナ（フィリピン産）","７５０ｇ",1,"2","0",,288,311,"2023041","       ","0","0","0","0","0","0","0","1","080",0.08,"2","2","1","1","  ","0","20230117","100742","20230309","102624"
"301","1039130","100499798","2000000648941","1","02","01","02","09","02","フレンドリーバナナ（フィリピン産）","５００ｇ（３?５本）",1,"2","0",,278,300,"2023041","       ","0","0","0","0","0","0","0","1","080",0.08,"2","2","1","1","  ","0","20230117","100742","20230309","102624"
"301","1355180","800022912","2008000229122","1","01","01","01","02","02","貝割大根（大分県産）","１パック",20,"2","0",,36,38,"2023041","       ","0","0","0","1","0","0","0","0","080",0.08,"2","2","1","1","  ","0","20230117","100742","20230309","102624"
"301","1395165","800055699","2000000678788","1","01","01","00","05","02","たまねぎ（北海道産）","３００ｇ（大小混）",1,,"0",,138,149,"2023041","       ","0","0","0","0","0","0","0","1","080",0.08,"2","2","1","1","  ","0","20230117","100742","20230309","102624"
"301","1409182","100433518","2000000601557","2","01","01","00","05","00","よくねたいも〈北あかり〉（北海道産）","５００ｇ（大小混）",1,"1","0",,258,278,"2023041","       ","0","0","0","0","0","0","0","1","080",0.08,"2","2","1","1","  ","0","20230302","130441","20230309","102458"
"301","1417550","800072337","2008000723378","1","01","01","00","01","05","ほうれん草（長崎県産）","１５０ｇ",1,"2","0",,138,149,"2023041","       ","0","0","1","0","0","0","0","0","080",0.08,"2","2","1","1","  ","0","20230117","100742","20230309","102624"

四. 转换工具

复制代码创建html文件，拿来就用。😋不用配置环境

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CSV转换SQL工具</title>
</head>
<body>
    <div style="display: flex;">
        <label for="schema">指定schema</label>
        <select id="schema">
            <option value=""></option>
            <option value="nosan">nosan 农业</option>
            <option value="sozai">sozai 蔬菜</option>
            <option value="shokan">shokan 商管</option>
        </select>
    </div>
    <hr>

    <label for="removeBlank">是否去除字符串中的空白</label>
    <input id="removeBlank" type="checkbox" />
    <hr>

    <label for="csv">请上传csv文件</label>
    <input id="csv" type="file" accept=".csv" multiple />
    <hr>

    <button id="transform">csv转sql开始</button>
    <hr>

    <!-- 显示错误消息的区域 -->
    <div id="error" style="color: red;">
        
    </div>
</body>
<script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
<script>
    // 是否去除字符串空白flag
    let removeBlank = false;

    $(function() {
        transformEvent();
    });

    function transformEvent() {

        $("#transform").click(function() {

            removeBlank = $("#removeBlank").prop("checked");

            const schema = $("#schema").val();
            if(!schema) {
                alert("请指定schema!");
                return;
            }

            // 清空所有的内容
            $("#error").empty();

            // 获取上传的所有csv文件
            const csvFileList = $("#csv").get(0).files;
            for(csvfile of csvFileList) {

                const sqlList = [];

                /*
                    将上传的文件名称当做表名
                    注意:
                        下面这行代码不能放到reader的load回调里面,
                        否则生成的csv的文件名会相同
                */
                const tableName = csvfile.name.split(".")[0];
                
                // 创建csv文件读取对象
                const reader  = new FileReader();

                // 指定要读取的CSV的编码
                reader.readAsText(csvfile, "Shift_JIS");
                reader.addEventListener('load', ({target: {result: csvText}}) => {

                    // 获取出标题栏之外的csv内容数据
                    const hanleCSVData = csvText.slice(csvText.indexOf('\n') + 1).split('\n');

                    // 如果上传的csv文件只有标题没有数据
                    if(!hanleCSVData || hanleCSVData.length === 1 && !hanleCSVData[0].trim()) {
                        const newHtml = $("#error").html() + "<br />" + tableName + ".csv文件没有内容!";
                        $("#error").html(newHtml);
                        return;
                    }

                    for (const csvRowData of hanleCSVData) {

                        if (!csvRowData) {
                            continue;
                        }

                        // 构造插入SQL
                        let insertSql = SqlUtils.createBaseSql(schema, tableName);
                        
                        // 创建要插入sql的值
                        const sqlValueStr = SqlUtils.addCsvRowToSqlItem(csvRowData);
                        
                        // 补足自定义项目后,构建
                        const insertSqlStr = SqlUtils.customeSqlHandle(insertSql + sqlValueStr);

                        sqlList.push(insertSqlStr);
                    }

                    // 下载转换之后的sql文件
                    fileDownload(sqlList, `${tableName}.sql`);
                });
            }
        });
    };

    class SqlUtils {

        // 获取当前时间 yyyy/MM/dd HH:mm:ss
        static nowDate = new Date().toLocaleString();

        // 是否去除字符串中的空白
        static trimStrFlg = true;

        // 创建基本的插入SQL语句
        static createBaseSql(schema, tableName) {
            // 因为csv项目和表项目完全一致,因此可以省略项目
            return `INSERT INTO [${schema}].${tableName} VALUES (`;
        }

        static addCsvRowToSqlItem(csvRowData) {

            let sqlItem = "";
            // 获取CSV所有的列
            const csvItemList = csvRowData.split(",");
            const newcsvItemList = [];

            // 对csv项目进行特殊处理
            for (const csvItem of csvItemList) {
                if(!!csvItem) {
                    /*
                        如果需要去除空格,并且是csv中的字符串项目的话
                    */
                    if (removeBlank && csvItem.includes("\"")) {
                        // "测试     "   ===> '测试'
                        newcsvItemList.push("\'" + csvItem.replaceAll('"', '').trim() + "\'");
                    } else {
                        // 插入的是数字
                        newcsvItemList.push(csvItem);
                    }

                } else {
                    // 如果csv项目为空项目则插入NULL
                    newcsvItemList.push('NULL');
                }
            }

            // 将list转换为以 , 分隔的字符串,并把 " 替换为 ' ,因为sql插入时只能用 '
            return newcsvItemList.join(", ").replaceAll('\"', '\'') + ", ";
        }

        static customeSqlHandle(insertSql) {
            // 插入时间
            const SKSI_NCHJ = `'${SqlUtils.nowDate}'`;
            // 更新时间
            const KUSHN_NCHJ = `'${SqlUtils.nowDate}'`;

            // 添加换行符
            return `${insertSql}${SKSI_NCHJ}, ${KUSHN_NCHJ});\r\n`;
        }
    }

    function fileDownload(data, fileName) {

        // 创建文件下载的url
        const insertSqlStrBlob = new Blob(data, {type: "application/sql"});
        const src = URL.createObjectURL(insertSqlStrBlob);

        // 创建a标签
        const aElement = document.createElement('a');
        aElement.download = fileName;
        aElement.style.display = 'none';
        aElement.href = src;

        // 将a标签添加到页面上手动触发点击事件,从而触发文件下载
        document.body.appendChild(aElement);
        aElement.click();
        document.body.removeChild(aElement);

        URL.revokeObjectURL(src);
    }
</script>
</html>