java读取csv文件内容（逗号分隔、回车换行文本）

来源：互联网发布：ipad屏幕录像软件编辑：程序博客网时间：2024/05/20 07:52

最近在做项目时需要每日更新大小额支持的银行列表信息，该文件从核心下载，并使用java项目解析放到数据库中，该文件是文本文件，一行为一条记录，记录中以逗号分隔字段，字段以双引号包裹，起初以这种规则自己写一个小程序也能用，于是写了几句代码，确实能运行解析出大部分内容，但有些特殊情况是没有解析出来的，例如：双引号包裹的的字段内容里面也有逗号，但这个逗号并不是字段之间的分隔符，而是字段内容的一部分。起初并不知道这其实是一种标准的csv格式，虽然曲折但是整好了记录下

package com.schedule;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.lang3.time.DateUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;

import com.ynet.ifp.eams.core.util.FtpUtils;
import com.ynet.ifp.eams.utils.file.UncompressFileGZIP;
import org.springframework.stereotype.Component;

/**
* 更新大小额联行号信息暂定每天晚上11点执行
*/
@Component("firstUpdatePxdzfInfoSchedule")
public class FirstUpdatePxdzfInfoScheduleImpl extends EamsScheduledService {
private static Logger log = Logger.getLogger(FirstUpdatePxdzfInfoScheduleImpl.class);

   @Autowired
   private com.ynet.ifp.core.utils.TxUtils txUtils;
   @Autowired
   private DataSource dataSource;
   @Value("${hx.bank.host}")
   private String host;
   @Value("${hx.bank.port}")
   private int port;
   @Value("${hx.bank.path}")
   private String path;
   @Value("${hx.bank.name}")
   private String name;
   @Value("${hx.bank.pwd}")
   private String pwd;
   @Value("${hx.bank.fileName}")
   private String fileName;
   @Value("${hx.bank.down.path}")
   private String downPath;

public String getCnName() {
return "定时任务--更新大小额联行号信息";
}

@Override
protected void doService() throws Exception {
  Connection con= null;
  try{
  log.info("updatePxdzfInfoSchedule schedule is going to start.....");
  Date date = new Date();
  //凌晨一点取昨天3点左右放的文件
  Date tDay = DateUtils.addDays(date, -2);
   DateFormat df = new SimpleDateFormat("yyyyMMdd");
   String dateStr=df.format(tDay);

   BufferedReader reader = null;
   String path =downPath+"PXDZF.unl";
   String line= "";
   try {
    reader = new BufferedReader (new InputStreamReader(new FileInputStream(new File(path)),"GBK"));
   } catch (FileNotFoundException e) {
    e.printStackTrace();
   }
//   获取连接
     con= dataSource.getConnection();
           con.setAutoCommit(false);
           String deleteSql="delete from yc_pxdzf";
       PreparedStatement deleteStat= con.prepareStatement(deleteSql);
           log.info(deleteSql);
            deleteStat.execute();
            con.commit();
   String serialNumber = "yc_id_sequence.nextVal";
       String sql="insert into YC_PXDZF (YC_FQHHO2, YC_ZHUANT, YC_JIGULB, YC_HANBDM, YC_C2ZCHH, YC_BHSJCY, YC_C2RHDM, YC_FBHHO2, YC_QSHHO2, YC_JIEDDM,"+
                " YC_FKHMC1, YC_CXUMC1, YC_YLIUBZ, YC_SUSDDM, YC_DIZHI1, YC_YOUZBM, YC_TFDESC, YC_BYZDBE, YC_SXIORQ, YC_SHIXRQ, YC_BEIZXX, YC_BEIY40,"+
                " YC_BYBZ01, YC_BYBZ02, YC_WEIHRQ, YC_WEIHSJ, YC_WEIHGY, YC_ROWIDD, YC_SHJNCH, YC_JILUZT,YC_ID) values "+
                "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"+serialNumber+")";
         PreparedStatement stat= con.prepareStatement(sql);

         int t=0;
   while((line=reader.readLine())!=null){
    String regExp =getRegExp();
     String str = "";
     Pattern pattern = Pattern.compile(regExp);
                 Matcher matcher = pattern.matcher(line);
                 List listTemp = new ArrayList();
                 while(matcher.find()) {
                     str = matcher.group();
                     str = str.trim();
                     if (str.endsWith(",")){
                         str = str.substring(0, str.length()-1);
                         str = str.trim();
                     }
                     if (str.startsWith("\"") && str.endsWith("\"")) {
                         str = str.substring(1, str.length()-1);
                         if (isExisted("\"\"", str)) {
                             str = str.replaceAll("\"\"", "\"");
                         }
                     }
                     if (!"".equals(str)) {
                         listTemp.add(str);
                     }
                 }

    String[] pmsbankno = (String[]) listTemp.toArray(new String[listTemp.size()]);
    for(int i=0;i<pmsbankno.length;i++){
     String tempString=pmsbankno[i];
     tempString=tempString.replaceAll("\"", "").trim();
     pmsbankno[i]=tempString;

    }
     stat.setString(1,pmsbankno[0]);
            stat.setString(2,pmsbankno[1]);
            stat.setString(3, pmsbankno[2]);
            stat.setString(4,pmsbankno[3]);
            stat.setString(5,pmsbankno[4]);
            stat.setString(6,pmsbankno[5]);
            stat.setString(7,pmsbankno[6]);
            stat.setString(8, pmsbankno[7]);
            stat.setString(9,pmsbankno[8]);
            stat.setString(10,pmsbankno[9]);
            stat.setString(11,pmsbankno[10]);
            stat.setString(12, pmsbankno[11]);
            stat.setString(13, pmsbankno[12]);
            stat.setString(14, pmsbankno[13]);
            stat.setString(15, pmsbankno[14]);
            stat.setString(16,pmsbankno[15]);
            stat.setString(17, pmsbankno[16]);
            stat.setString(18,pmsbankno[17]);
            stat.setString(19,pmsbankno[18]);
            stat.setString(20, pmsbankno[19]);
            stat.setString(21,pmsbankno[20]);
            stat.setString(22, pmsbankno[21]);
            stat.setString(23,pmsbankno[22]);
            stat.setString(24, pmsbankno[23]);
            stat.setString(25,pmsbankno[24]);
            stat.setString(26, pmsbankno[25]);
            stat.setString(27, pmsbankno[26]);
            stat.setString(28, pmsbankno[27]);
            stat.setString(29, pmsbankno[28]);
            stat.setString(30, pmsbankno[29]);
            stat.addBatch();
             if(t%100==0){
             int[] resultInt=stat.executeBatch();
               con.commit();
             }

   }
    int[] resultInt=stat.executeBatch();
          String updateSql="update BATCH_JOB_EXECUTION e set e.end_time = sysdate where e.END_TIME is NULL and e.job_instance_id in (select i.job_instance_id from BATCH_JOB_INSTANCE i where i.job_name = 'updatePxdzfInfoSchedule')";
       PreparedStatement updatestat= con.prepareStatement(updateSql);
       updatestat.execute();
           con.commit();
           con.close();

  }catch(Exception e){
   e.printStackTrace();
   con.close();
  }
}
private String getRegExp() {
         String strRegExp = "";
         strRegExp =
             "\"(("+ SPECIAL_CHAR_A + "*[,\\n 　])*("+ SPECIAL_CHAR_A + "*\"{2})*)*"+ SPECIAL_CHAR_A + "*\"[ 　]*,[ 　]*"
             +"|"+ SPECIAL_CHAR_B + "*[ 　]*,[ 　]*"
             + "|\"(("+ SPECIAL_CHAR_A + "*[,\\n 　])*("+ SPECIAL_CHAR_A + "*\"{2})*)*"+ SPECIAL_CHAR_A + "*\"[ 　]*"
             + "|"+ SPECIAL_CHAR_B + "*[ 　]*";
         return strRegExp;
     }

     private static final String SPECIAL_CHAR_A = "[^\",\\n 　]";
     private static final String SPECIAL_CHAR_B = "[^\",\\n]";
     private boolean isExisted(String argChar, String argStr) {
         boolean blnReturnValue = false;
         if ((argStr.indexOf(argChar) >= 0)
                 && (argStr.indexOf(argChar) <= argStr.length())) {
             blnReturnValue = true;
         }
         return blnReturnValue;
     }

}

阅读全文

0 0