• Post Reply Bookmark Topic Watch Topic
  • New Topic

Making a CSV file from data obtained from a resultset

 
Monty Guppy
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to do something similar to the example below:
I obtain the LastName, FirstName, MiddleInitial of all the people from my customer table (Oracle).
From the resultset, I create a string which looks like (comma-delimited, with new line for each name):
Austin, Jane, K.
Smith, John, B.
Carol, Lewis, L.
I want to do 2 operations:
1. To write to a text file where the contents of the file are exactly as seen above (with each resultset in a new line). Currently, I am able to create the file, but the data looks like this:
Austin, Jane, K.[]Smith, John, B.[]Carol, Lewis, L.
where: [] is a square-like character which otherwise indicates end of line.
2. I also want to upload the data as bytearray to my SQL-Server database, so that when (using a servlet) my user wants to download the data, all the records appear in separate lines as comma-delimited values which my user can choose to convert to an excel spreadsheet.
Any help on this would be gratefully appreciated. Thank You.
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the Oracle data type for the middle initial? And what does the code look like which you're using to extract the middle initial from the ResultSet and print it?
 
Monty Guppy
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jim,
The above is just a sample data. The real data would be a report with potentially string, datetime and numeric data types. This report alos would potentially contain several thousands of records.
Currently, I have a stored procedure which returns all the data in a cursor. The java code is something like this:
CallableStatement cStmt=conn.prepareCall("{call PRC_SEL_TEST3(?,?)}" );
cStmt.registerOutParameter(2, OracleTypes.CURSOR);
cStmt.execute();
ResultSet rset = (ResultSet)cStmt.getObject(2);
while (rset.next()){
for (int j=0; j<iNumberOfCols; j++){
testVal = rset.getString (j+1);
if (j < (iNumberOfCols-1)) testVal=testVal+",";
else testVal=testVal+"\n";
sb.append(testVal);
}
}
System.out.println("sb = " + sb.toString());
Thanks
Prateek
 
Monty Guppy
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anyone? Please help if you have an idea re: this.
You will be of great help even if you could tell me how to store records (fields being comma-delimited and each record on separate line) in a database, such that the data, when retrieved for online viewing/ downloading, would continue to have the comma-delimited records in separate line.
Thanks
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The code here looks fine to me. Perhaps the data for middle initial isn't being stored correctly in the DB. What data type is used in the database for the middle initial? Does the data look normal if you view it with other tools (like sqlplus)? You may have to do some custom handling for this particular column to remove the final (bogus) char, e.g.
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think it's simpler than this. How are you writing your end-of-line characters? And how are you viewing the output?
It's important to remember that you need to generate the correct end-of-line sequence for the platform you are storing the file on. On Unix, for example that will just be "\n", on Windows it needs to be "\r\n". To find the correct end-of-line sequence for the platform running your JVM, you can use System.getProperty("line.separator");
Or have I missed the point ?
 
Monty Guppy
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much Frank. Your solution worked for me.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!