Convert Resultset to CSV file using Java

Question

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.

Value in Field comes in csv as

[<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 >]

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++;   
            }

Jacobi · Accepted Answer · 2013-08-08 14:41:03Z

23

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

See an example:

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

answered Aug 8, 2013 at 14:41

Jacobi

1,52415 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Devesh Over a year ago

Yes I am using java.swl.Resultset. Due to some constraint opencsv is not allowed to use.

Ravi K Thapliyal · Accepted Answer · 2013-08-08 15:17:08Z

3

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.

answered Aug 8, 2013 at 15:17

Ravi K Thapliyal

51.9k9 gold badges80 silver badges90 bronze badges

4 Comments

Devesh Over a year ago

Though it took care of new line but it did not complete. While writing 1439 rows it quit in 3 rd col.

Devesh Over a year ago

last 4 lines of csv 92471 147183 Msg for 10641508 Processed Determine Next Activity From Host Msg null 2/20/2013 13:17 92472 147184 Msg for 10641505 Processed Determine Next Activity From Host Msg null 2/20/2013 13:17 92468 147185 Msg for 10641504 Processed Determine Next Activity From Host Msg null 2/20/2013 13:17 92470 147181 Msg for 10641506 Processed Determine Next Activit

Ravi K Thapliyal Over a year ago

I'm unable to understand due to not being familiar with your data. Could you compare this with your previous CSV and point out the issue? Feel free to update your question with the info.

Devesh Over a year ago

Yes I compared and the previous also didnt complete. I am still trying to understand why it didnt complete. Thank you Ravi

Collectives™ on Stack Overflow

Convert Resultset to CSV file using Java

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related