2021-11-05

mysql数据库有一字段里的数据含有 "和，在生成csv文件时，会被分割导致错位
当字符含有逗号时，将字符用双引号引起来，那么字符里的逗号将不会被解析；
含有双引号时，可替换为两个双引号。

java编写

	int ftpPort = 21;
	int capacity = 16;
    String remote = fileName;
	byte[] bytes = this.produceCSVData(dataList, capacity).getBytes("UTF-8");
   FTPClient ftpClient = this.getFtpClient(ftpHost, ftpPort, ftpUserName, ftpPassword);
    this.setParameter(ftpClient, ftpPath);
    OutputStream out = ftpClient.storeFileStream(remote);
    if (out == null) {
        throw new RuntimeException("");
    }
    out.write(bytes);out.close();

    if (ftpClient.isConnected()) {
        ftpClient.disconnect();
    }




    private String produceCSVData(List> dataList, int capacity) {
    if (dataList == null || dataList.size() <= 0) {
        throw new RuntimeException("需要转换的数据为空！");
    }

    // 文件数据
    StringBuilder builder = new StringBuilder(capacity);
    builder.append("a,b,c,d,e,f,g);
 // 拼接上换行符
    builder.append("rn");
    
    for (Map map : dataList) {
    	 // 拼接文件数据
        for (Map.Entry entry : map.entrySet()) {
            if (entry.getValue() != null) {
            	String handleStr=(String) entry.getValue();
            	String str=(String) entry.getValue();
            	//先判断字符里是否含有逗号
            	if(str.contains(",")){
            	    //如果还有双引号，先将双引号转义，避免两边加了双引号后转义错误              
            	    if(str.contains(""")){
            	        handleStr=str.replace(""", """");
            	    }
            	    //将逗号转义  
            	    handleStr="""+handleStr+""";  
            	}
            	builder.append(handleStr);
            }
        }
        //拼接上换行符
        builder.append("rn");
	}
    return builder.toString();

}

sql编写

select
concat(a,’,’,b,’,’,c,’,’,d,’,’,e,’,’,
case when fis not null then ‘"’||f||’"’ else fend,’,’,g) as col
from tableName