• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Change the default date format while exporting the resultset data in CSV using openCSV

 
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I am trying to export the data into CSV using openCSv framework.Database is Universe Db.CSV generates fine with "$" as separator.But when we tried to load the same csv data into IBM DB2 database it is failing because of incompatible date format(i.e DD-MMM-YYYY).While IBM DB2 doesnt support the mentioned date format.

Code to generate the CSV:
private CSVWriter writer;
writer = new CSVWriter(new OutputStreamWriter(new FileOutputStream(savePath + "\\" + tableName+ ".csv"),"UTF-8"), '$',CSVWriter.NO_QUOTE_CHARACTER);
writer.writeAll(rs, false); //rs is the resultset returned for the select query
writer.flush();

We are loading the csv using DB2 load & imp command.

Kindly help me in changing the default date format while generating the csv or any other option to do the same .
Also,let me know if there is any other framework that can be used to load the csv data.

Puprose of the activity :
To load the data from Universe db to IBM DB2 database.

Note : Universe DB doesnt support updating the resultset hence that option was not taken into consideration
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Format the date in the SQL query, so it returns in the format you require.
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My sql query is ,
rs = select * from table_name;
here i am directly sending the rs to writeAll method of opencsv
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, so does Universe's version of SQL have a way of formatting the output of a column?

In Oracle you would do:
SELECT TO_CHAR(some_date_column, 'DD-MON-YYYY')
FROM some_table;

for example.
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,


i tried the same option SELECT to_char(column_name, 'MM/DD/YYYY') FROM table_name;

I am getting below error msg.
java.sql.SQLException: UniVerse/SQL: syntax error. Unexpected symbol. Token was "to_char". Scanned command was FROM v10.vakuutus SELECT to_char
at com.ibm.u2.jdbc.UniJDBCMsgFactory.createException(UniJDBCMsgFactory.java:101)
at com.ibm.u2.jdbc.UniJDBCExceptionSupport.addException(UniJDBCExceptionSupport.java:87)
at com.ibm.u2.jdbc.UniJDBCProtocolU2Impl.addServerError(UniJDBCProtocolU2Impl.java:2830)

It seems Universe Database doesnt support to_char function.I could not find any function in Universe DB which can help in solving my problem.
can you please suggest something on this front.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I didn't say it did support that function, which is Oracle specific.
I suggested looking into whether Universe has something similar.
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
true..
but i found similar option when i googled it...but nothing is working more over i have only one option to access the universe db i.e. from java ..
it will be very helpful i get some functions to convert the date format in universe database.
 
Bartender
Posts: 10780
71
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Nilesh Wattamwar wrote:I am trying to export the data into CSV using openCSv framework.Database is Universe Db.CSV generates fine with "$" as separator.But when we tried to load the same csv data into IBM DB2 database it is failing because of incompatible date format(i.e DD-MMM-YYYY).While IBM DB2 doesnt support the mentioned date format.


Good old SQL.

TBH, if you're required to use openCSV, I'd do this in four stages:
1. Write out the CSV file as you're doing.
2. Read it back into a List <String[]> using a CSVReader with the same settings as (1). (have a look at the readAll() method)
3. Convert the date column yourself.
4. Write lines back out with CSVWriter.writeAll().

It's a bit of a kludge, but very straightforward.

I suspect you could also do the same thing without the need for an interim file by connecting the output and input streams for your csv objects with a Pipe, and doing conversions a line at a time. The logic would be slightliy trickier, but then you wouldn't be constrained by memory size.

I should add that I'm no csv expert; but that's how I'd do it - at least until I had more expertise.

Winston
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Winston ,

TBH, if you're required to use openCSV, I'd do this in four stages:
1. Write out the CSV file as you're doing.
2. Read it back into a List <String[]> using a CSVReader with the same settings as (1). (have a look at the readAll() method)
3. Convert the date column yourself.
4. Write lines back out with CSVWriter.writeAll().

As you mentioned above,

first two steps are fine.for 3rd step how can convert the date on my own? can you please explain,it will be better if provided the code for the same.
 
Winston Gutkowski
Bartender
Posts: 10780
71
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Nilesh Wattamwar wrote:first two steps are fine.for 3rd step how can convert the date on my own? can you please explain,it will be better if provided the code for the same.


I'm afraid that's not how we do things here.

You know that you have a date in your output/input, and you presumably know which column it's in. You also know what format it's in, so it's simply a case of writing a method, and using it to replace the data in that column.

Try writing the method yourself, and come back if you run into any problems.

Winston
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Nilesh Wattamwar wrote:true..
but i found similar option when i googled it...but nothing is working more over i have only one option to access the universe db i.e. from java ..
it will be very helpful i get some functions to convert the date format in universe database.



Surely there's some documentation on the available functions for this database?
Obviously there's no guarantee there is a way of formatting the output (for example I don't think Derby has such a thing).

If there isn't then I would go for Winston's answer.
But my preference will always be to get the data out of the DB in the format you want. It's a good habit since it tends to be quicker, often by quite some margin, than crunching the data row by row (or slow by slow).
However, as I say, if there's no such way of doing it in the query then slow-by-slow it is...
 
Winston Gutkowski
Bartender
Posts: 10780
71
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:But my preference will always be to get the data out of the DB in the format you want. It's a good habit since it tends to be quicker, often by quite some margin, than crunching the data row by row (or slow by slow)...


@Nilesh: I totally agree with Dave. Just remember that in such a case your ResultSet would then have a String in the "Date" column.

Mine is definitely a "CSV for Dummies" solution.

Winston
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am totally agree with Dave since the table data volume is high i need to find the most efficient way to extract the same.Slow by slow is definitely a good approach but in this case it is not possible.
However,I still didnt find anything at Universe DB front to change the date format in select query itself like "select to_date(to_char(column_name,'DD-MM-YYYY'),'DD.MM.YYYY')) from table name" or any other option except exporting data into csv( like into txt or xml etc) which will be more efficient to extract large volume data?
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do you have a link to the Universe DB website or docs?
I did a Google, but I couldn't guarantee I was looking at the correct thing.

ETA: If the database has an export tool then it is possible that would be better, however (and it's quite a big "however") most of these export tools are intended to produce output for importing into the same db, so I suspect you'd be in the same position regarding your date issue.

Just one more thing, in case someone comes upon this with a non-export/reporting issue, the "format in the db" really does only apply to this sort of report/export thing. I wouldn't apply that to, for example, data pulled from the db for display via a webapp. In those cases display format is likely to change by circumstance (eg internationalisation, or just the whim of some marketing bod) and formatting when you display it is far preferable.
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As of now , I am fetching the data from universe db and writing it to csv using OpenCSV with delimiter as "$".while writing the data to csv , date is coming in 'DD-MMM-YYYY' format(not sure if it is actual database date or java recognised date format).
once csv is written,I am loading this data into db2 using load\imp utility . Unfortunately, db2 doesnt recognise 'DD-MMM-YYYY' format so that utility also not working . thats why need to convert it to 'DD-MM-YYYY' or 'YYYY-DD-MM' format.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, let's look at this from the other end.
What does your IMPORT command for DB2 look like?

Many importers will allow you to define formats...and a quick Google gives this for DB2:
http://www.dbforums.com/showthread.php?1658384-Importing-CSV-file-and-Date-amp-Timestamp-fields

That should give you enough info to look it up in the DB2 docs (always go to the docs, and don't rely on some random post on a forum).
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have tried imp and load both the options.

#import
db2 import from v10.vakuutus.csv of coldel$ messages vakuttus_load.log insert into lis.vakuutus

#load
db2 load from v10.vakuutus.csv of del modified by nochardel coldel$ dateformat=\"YYYY-MM-DD \" insert into lis.vakuutus > load_dollar_out.txt
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That's back-to-front.
You're supposed to give it the format that exists in the CSV, not the format for DB2.
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hii Dave,Winston,

Finally my issue got solved

Actual culprit was OpenCSV ..
when i do select * from table_name ..date format coming as YYYY-MM-DD . I was sending that resultset directly to CSVWriter.writeAll() method.When it writing to csv it was changing the date format to DD-MMM-YYYY which was incompatible with DB2.
So I changed the approach and was directly writing each resultset row to BufferedWriter without using opencsv API.
Once csv got generated used below load command to load the csv to db.


db2 load from v10.vakuutus.csv of del modified by nochardel coldel$ dateformat=\"YYYY-MM-DD\" insert into lis.vakuutus > load_dollar_out.txt

And it worked!!!

many thanks for your timely support..
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There's no point using the dateformat if the format in the CSV is now YYYY-MM-DD as, according to earlier in the thread, that's already the format expected by DB2.
 
Nilesh Wattamwar
Greenhorn
Posts: 11
IBM DB2 Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes... no need to give dateformat explicitly..

thanks
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic