java将空格与制表符混合的txt数据文件导入MySQL

空格与制表符混合的txt文件直接使用load函数无法指定间隔符，可以利用字节流读取文件数据内容，将空格、制表符替换为逗号，转换为csv再执行load。话不多说，上代码>>>
txt转csv工具类

import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.text.csv.CsvWriter;
import cn.hutool.core.util.CharsetUtil;

import java.io.*;
import java.util.HashMap;
import java.util.Map;

public class TxtToCsv {

    public static Map<String, Integer> dataTransfer(String filePath) {
        String readFile = filePath + ".txt";
        String writeFile = filePath + ".csv";
        File file = new File(readFile);
        CsvWriter csvWriter = null;
        Map<String, Integer> map = new HashMap<>();
        try {
            csvWriter = CsvUtil.getWriter(writeFile, CharsetUtil.CHARSET_GBK);
            FileInputStream fis = new FileInputStream(file);
            //将字节流转化为字符流，编码指定为文件保存的编码
            InputStreamReader isr = new InputStreamReader(fis, "GBK");
            BufferedReader bufferedReader = new BufferedReader(isr);
            String string = "";
            Integer size = 0;
            while ((string = bufferedReader.readLine()) != null) {
                size++;
                String[] s = string.split("\\s+");
                csvWriter.write(s);
            }
            map.put(writeFile, size);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            csvWriter.close();
        }
        return map;
    }
}

上传文件代码（前端bootstrap模板）

<div>
	<button class="btn btn-w-m btn-success" data-toggle="modal" data-target="#txtImportModal">
                    导入txt台账
                </button>
</div>
<div class="modal fade" id="txtImportModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">
                    &times;
                </button>
                <h4 class="modal-title" id="myModalLabel">
                    上传txt数据台账
                </h4>
                <font color="red" class="pull-left mt10">
                    提示：仅允许导入txt文件!
                </font>
            </div>
            <div class="modal-body">
                <input type="file" id="upfile" name="upfile"/>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">关闭
                </button>
                <button type="button" class="btn btn-primary" onclick="submitHandler()">
                    提交
                </button>
            </div>
        </div><!-- /.modal-content -->
    </div><!-- /.modal -->
</div>
<script>
var prefix = "loan/sxCustInfo";

    function submitHandler() {
        if ($.validate.form()) {
            var formData = new FormData();
            formData.append('file', $('#upfile')[0].files[0]);
            formData.append('fileName', $('#upfile')[0].files[0].name);
            $.ajax({
                url: prefix + "/importData_xsbk",
                type: 'post',
                cache: false,
                data: formData,
                processData: false,
                contentType: false,
                dataType: "json",
                beforeSend: function () {
                    $.modal.loading("数据存储中,请稍后...");
                    $('#txtImportModal').modal('hide');
                },
                success: function (result) {
                    $.modal.closeLoading();
                    $.operate.successCallback(result);
                }
            });
        }
    }
</script>

上传文件代码（后台）

service

public String importTxtData(MultipartFile file, String fileName) {
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        Integer zongCount = 0;
        Integer successCount = 0;
        if (file != null) {
            // 上传文件路径
            String filePath = jsczConfig.getUploadPath();//路径
            try {
                fileName = FileUploadUtils.upload(filePath, file);//上传文件
                if ((fileName != null) && (fileName.length() > 0)) {
                    //文件名去后缀
                    if ((fileName != null) && (fileName.length() > 0)) {
                        int dot = fileName.lastIndexOf('.');
                        if ((dot > -1) && (dot < (fileName.length()))) {
                            fileName = fileName.substring(0, dot);
                        }
                    }
                    //拼接上传后文件全路径
                    filePath = filePath + fileName;
                }
                //txt转csv
                Map<String, Integer> map = TxtToCsv.dataTransfer(filePath);
                String absolutePath = null;
                for (String key : map.keySet()) {
                    absolutePath = key;
                    zongCount = map.get(key);
                }
                if (absolutePath != null) {
                    try {
                        sxCustInfoMapper.insertXsbkData(absolutePath);
                        successCount = sxCustInfoMapper.selectXsbkDataCount();
                        importDataLog.setZongCount(zongCount);
                        importDataLog.setSuccessCount(successCount);
                        importDataLog.setFailCount(zongCount - successCount);
                    } catch (Exception e) {
                        e.printStackTrace();
                        failureMsg.insert(0, "很抱歉，导入失败!");
                        return failureMsg.toString();
                    }
                    importDataLog.setImportEndDate(DateUtils.dateTimeNow(DateUtils.YYYY_MM_DD_HH_MM_SS));
                    importDataLogMapper.insertImportDataLog(importDataLog);
                    successMsg.insert(0, "恭喜您，数据已全部导入成功！");
                } else {
                    failureMsg.insert(0, "很抱歉，导入失败!");
                    return failureMsg.toString();
                }
            } catch (IOException e) {
                e.printStackTrace();
                String msg = new Date() + "";
                log.error(msg, e);
            }
        }
        return successMsg.toString();
    }

mapper

void insertXsbkData(@Param("fileName") String fileName);

xml

<select id="insertXsbkData" parameterType="string">
        LOAD DATA LOCAL INFILE #{fileName} INTO TABLE xsbk_info CHARACTER
        SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
        ( bank_code, user_name, zc_date, id_card, account, phone );
    </select>