How can i show backend table data in CSV format

[Srinivasa Maddi]

Hi
Need help in coding a swing app such that, once if user clicks a button the he should get the respective table data in CSV format which he can save into his local machine.

[Maneesh Godbole]

What have you got so far?

[Srinivasa Maddi]

I have a table in the database with batchId as the primary key. Now i have to design a screen with a textfield and a button. Once the user inputs the batcid in the textfield and if clicks the button then he should get the respective details in a CSV format

[Maneesh Godbole]

Originally posted by Srinivasa Maddi:
I have a table in the database with batchId as the primary key. Now i have to design a screen with a textfield and a button. Once the user inputs the batcid in the textfield and if clicks the button then he should get the respective details in a CSV format

Ok.
Where are you stuck at? Getting the data from the DB or exporting it as CSV?

[Srinivasa Maddi]

I can get it from DB but no idea how to export it to a CSV file

[Maneesh Godbole]

Originally posted by Srinivasa Maddi:
I can get it from DB but no idea how to export it to a CSV file

CSV is a relatively simple file format.
You can write "This,is,a,test" in any text editor, save the file as test.csv and open it with excel, it will show you 4 columns populated with the above words. As the name suggests, it is a "comma separated value" file format.

When you make your DB query, you are getting back the ResultSet.
You can take the following approach.
Create a StringBuilder.
Iterate through the result set.
Pick out all values you want to store on the file and append them to the StringBuilder.
You need to append commas after each value for a row.
After each row you need to append the '\n' or line.separator.
Once you are done with all your ResultSet, just write the builder's string value to the file of your choice.
Check out the FileWriter class. It should suit your purpose.

Best of luck.

[Srinivasa Maddi]

Thank you Manish. I will try to implement it

[Rob Spoor]

I suggest using a library such as opencsv. With this one you can write a String[] and the library will handle all the escaping et all.

// columns is the number of columns retrieved from the ResultSetMetaData
String[] values = new String[columns];
for (int i = 1; i <= columns; i++)
{
    values[i - 1] = resultSet.getString(i);
}
csvWriter.writeNext(values);

The CSVWriter class even has a method for printing the column names given a ResultSet object. And if you're really lazy, how about writeAll(ResultSet, boolean) which does all the work for you?

[Maneesh Godbole]

Thanks Rob!
I wasn't aware of "opencsv".
It seems more easier than what I was suggesting.

[Rob Spoor]

Well in the end it will do just that, but somebody else has done the coding

[Srinivasa Maddi]

Thank you all for your support.

Rob, i really liked the csvWriter but no idea how to use that in my code that i wrote as per Maneesh suggestion. See my code snippet below

    
String query = "select * from ont_batch_header where batch_id=444";
Connection con = getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
StringBuilder sb = new StringBuilder();
ResultSetMetaData rsmd = rs.getMetaData();
int cc = rsmd.getColumnCount();
rs.next();
for(int i=1; i<=cc; i++)
 {
 sb.append(rsmd.getColumnLabel(i)+" "+rs.getString(i)+","+"\n");
 }
String str1 = sb.toString();
BufferedWriter out = new BufferedWriter(new FileWriter("result.csv"));
out.write(str1);
out.close();  

Now my doubt is:
1. How to replace the above code to use csvWriter ?
2. What do i need to do if i want the above result in pdf format ?

In the above code i tried result.pdf instead of result.csv to get the output in pdf. Though its generating a pdf file but i cannot see its content. Popinng up a msg saying the file is either damager or acorbat doesnt support...(Sorry unable to attach the screenshot).



Thanks in advance
Maddi

[ November 06, 2008: Message edited by: Srinivasa Maddi ]
[ November 07, 2008: Message edited by: Srinivasa Maddi ]

[Maneesh Godbole]

Originally posted by Srinivasa Maddi:
2. What do i need to do if i want the above result in pdf format ?

You cannot just name the file with .pdf extension. This is akin to renaming any file to .pdf.

You need something like IText to generate pdf files through java.

[Rob Spoor]

    
String query = "select * from ont_batch_header where batch_id=444";
Connection con = getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(str);
 
// using , between values and " around values
CSVWriter writer = new CSVWriter(new FileWriter("result.csv"), ',', '\"');
writer.writeAll(rs, true);
writer.flush();
writer.close();

It's that easy.

About PDF, as Maneesh said it is a completely different format. In this case, you will have to do all the work yourself.
[ November 06, 2008: Message edited by: Rob Prime ]

[Srinivasa Maddi]

Rob, could you please tell me what libraries do i need to import for using CSVWriter (i mean is there any thrid party library that i need to download here)?
[ November 07, 2008: Message edited by: Srinivasa Maddi ]

[Ulf Dittmer]

For creating PDFs you need the iText library. It has extensive documentation online, including many examples.

[Rob Spoor]

And for CSVWriter you need opencsv. I've already posted a link to it earlier.