parsing timestamp from mysql to csv using java

Question

i have a mysql db, and i have one table which has many columns including a timestamp one. i want to read the table and write it to a csv file. then i will read from the csv file the data and insert it in another db and table. all these are done through java. I can read the table date and the timestamp from the first db, but the timestap is not written correctly into the csv file.. only the last few digits are written. for example if i have a timestamp 2012-11-01 02:18:00.0 only the 18:00.0 will be written to the csv. All the other stuff work fine. For example if i hard write a timestamp i can write it to the second db. there;s the code for reading mysql and writing to csv :

                    con = DriverManager.getConnection("jdbc:mysql://localhost/campdb", "...", "...");

                    stmt = (Statement) con.createStatement();
                    fw = new FileWriter(filename);
                    PrintWriter a = new PrintWriter(fw);
                    ResultSet res = stmt.executeQuery("SELECT * FROM CAMPREQ;");
                    while (res.next()) 
                    {
                        String ID = res.getString(1);
                        java.sql.Date date = res.getDate(2);
                        //java.util.Date jdate = new java.util.Date(date.getTime());
                        String company = res.getString(3);
                        String time = res.getString(4);
                        String origin = res.getString(5);
                        String dest = res.getString(6);
                        String timestamp = res.getString(7);
                        System.out.println(timestamp);
                        a.print(ID);
                        a.print(',');
                        a.print(date);
                        a.print(',');
                        a.print(company);
                        a.print(',');
                        a.print(time);
                        a.print(',');
                        a.print(origin);
                        a.print(',');
                        a.print(dest);
                        a.print(',');
                        a.print(timestamp);
                        a.print('\n');
                        a.flush();

                    }

The System.out command prints the timestamp as it should be written, however it is not written correctly in the csv :( thank you

"i have a mysql db,.." Do you have a shift key? That should be something like "I have a MySQL DB,..". I would advise further, but I cannot endure 'mumbling'. — Andrew Thompson
– Andrew Thompson, Commented Nov 1, 2012 at 3:08

bmukorera · Accepted Answer · 2012-11-01 14:23:51Z

1

try using the code below, it works for most databases and properly takes all datatypes correctly with correct formatting. This should solve your problem not just for mysql but for any other database

 public static StringBuffer resultSetToStringCsv(ResultSet resultSet) throws Exception{
StringBuffer stringwriter = new StringBuffer();

ResultSet rs = resultSet;
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

//Write column names
for(int i=1;i<=numberOfColumns;i++){
 stringwriter.append(rsmd.getColumnName(i));
 stringwriter.append(',');
}
stringwriter.append('\n');
//Write rows
while(rs.next()){
 for(int i=1;i<=numberOfColumns;i++){
  try{
   stringwriter.append(""+rs.getObject(i));
  stringwriter.append(',');
  }catch (Exception e) {
   stringwriter.append("null");
 stringwriter.append(',');
}
}    
 stringwriter.append('\n');
}

You can check http://bmukorera.blogspot.com/2012/11/resultset-query-to-string-generating.html for full code

answered Nov 1, 2012 at 14:23

bmukorera

1161 silver badge8 bronze badges

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

1 Comment

Stelios Over a year ago

Sorry, my mistake, your solution works great, the timestamp is correctly writen to the csv :) ty

jfmatt · Accepted Answer · 2012-11-01 03:15:26Z

1

Don't do things in Java that MySQL can do by itself.

con = DriverManager.getConnection("jdbc:mysql://localhost/campdb", "...", "...");
stmt = (Statement) con.createStatement();

String q = 
  "SELECT * INTO OUTFILE '" + filename + "'"
  + " FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"
  + " LINES TERMINATED BY '\n'"
  + " FROM " + tablename;

stmt.execute(q);

answered Nov 1, 2012 at 3:15

jfmatt

9265 silver badges14 bronze badges

1 Comment

Stelios Over a year ago

1) I want to do this process multiple times. so if the file will be created once, then I can restart the same process, because the file should be created during the SELECT statement. 2)The data is written 100% correct in the .csv file, including the timestamp, but when I try to read the .csv and write the data to another database an error msg appears "mysqldatatruncation: data truncation: incorrect date value" . The error msg is about the date column and not the timestamp one.. ty anyway

Collectives™ on Stack Overflow

parsing timestamp from mysql to csv using java

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related