使用Java将Resultset转换为CSV文件

Hi I am trying to convert oracle jdbc resultset to csv file. Below is the code used. Issue occures when there is value like below in the field. It deforms the output csv and all this come in separate line rather than in one field.

您好我正在尝试将oracle jdbc结果集转换为csv文件。以下是使用的代码。如果字段中存在如下值，则会出现问题。它会使输出csv变形，所有这些都在单独的行而不是在一个字段中。

Value in Field comes in csv as

Field中的值以csv为单位

[<333message:Runtime error in script' ProcessItem: ' Type: 'ITEM'" 1:0).Internal Script error: java.lang.NullPointerException
Script (line 1):
setHours = 0 ;
if(ts.instanceId == null)
" 3 : ts.instanceId = 0 ;"
Step >]

[<333message：脚本中的运行时错误'ProcessItem：'Type：'ITEM'“1：0）。内部脚本错误：java.lang.NullPointerException脚本（第1行）：setHours = 0; if（ts.instanceId == null））“3：ts.instanceId = 0;”步骤>]

int ncols = result.getMetaData().getColumnCount();  

            System.out.println("ColumnCout"+ncols);  
            FileOutputStream fos=new FileOutputStream(new File("C:\\test.csv"),false);  
            Writer out = new OutputStreamWriter(new BufferedOutputStream(fos),"UTF_8");      

            for (int j=1; j<(ncols+1); j++) {     
            out.append(result.getMetaData().getColumnName (j));       
            if (j<ncols) out.append(","); else out.append("\r\n");      
            }   
            int m =1;    

            while (result.next()) {   

            for (int k=1; k<(ncols+1); k++) {   

            out.append(result.getString(k));    

            if (k<ncols) out.append(","); else out.append("\r\n");    
            }   
            //System.out.println("No of rows"+m);   
            m++;   
            }

2 个解决方案

#1

Get the value for the column that could have new lines as

获取可能有新行的列的值

String multiLine = null;
if (k == <col_index> && (mutiLine = rs.getString(k)) != null)
    out.append(multiLine.replaceAll("\\n", ""));
else
    out.append(result.getString(k));

You could filter all the columns as well but then would incur some performance hit.

您也可以过滤所有列，但这会产生一些性能损失。

#2

Are you using "java.sql.ResultSet" class? If yes, see the library in this link http://opencsv.sourceforge.net/

你在使用“java.sql.ResultSet”类吗？如果是，请参阅此链接中的库http://opencsv.sourceforge.net/

See an example:

看一个例子：

CSVWriter csvWriter = new CSVWriter(new FileWriter("yourfile.csv"), '\t');
java.sql.ResultSet myResultSet = .... ;
csvWriter.writeAll(myResultSet, includeHeaders);

#1