[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

need to export table to csv file using java code

Posted on 2011-05-01
11
Medium Priority
?
4,411 Views
Last Modified: 2012-06-21
I need to write java code that exports table data from Sybase 12, sybase 15 and oracle 11g to file
I have the code to connect to the db and just looking for the code that can be used to export the table toa csv file in the local machine.

something like
stmt.execute ("select * from tableabc to file "c:\export\abc.csv"")

0
Comment
Question by:PearlJamFanatic
11 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 35502671
something like that:
      try {
                Class.forName ("oracle.jdbc.driver.OracleDriver");
         
              conn =
                              DriverManager.getConnection ("jdbc:oracle:thin:@hostname.com:port_number:SID";,
                     "user", "password");

                     stmt =  conn.createStatement();


     ResultSet rs = stmt.executeQuery("select * from tableabcb");
     PrintStream psout = new PrintStream(new FileOuputStream("out.csv"));

   while(rs.next()){

     String s0 = rs.getString(1);
    Strung s1 = rsgetString(2);
    ...

    psout.println(s0 + "," + s1 + "," +...);

     }

conn.close();
  psout.close();
            }catch(Exception ex) {
...
}
     
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35502683
The above is the code I use for Oracle.
If you know the structure of the table, then this will work.
Actually with rs.getString(column_number)
most of the comlumn types  would be working
If columns are absiolutely not know then getMetaData(0 method could help
in the beginning

http://download.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSet.html

 
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35502693
some corrrections to the above snippet (not very significant):

     try {
                Class.forName ("oracle.jdbc.driver.OracleDriver");
         
          Connection    conn =
                              DriverManager.getConnection ("jdbc:oracle:thin:@hostname.com:port_number:SID";,
                     "user", "password");

                   Statement  stmt =  conn.createStatement();


     ResultSet rs = stmt.executeQuery("select * from tableabcb");
     PrintStream psout = new PrintStream(new FileOuputStream("out.csv"));

   while(rs.next()){

     String s0 = rs.getString(1);
    String s1 = rs.getString(2);
   //perhpas more columns 

    psout.println(s0 + "," + s1 + "," +...);

     }

conn.close();
  psout.close();
            }catch(Exception ex) {
System.out.println("Error " + ex.toString());
ex.printStackTrace();

}

Open in new window

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 40

Accepted Solution

by:
mrjoltcola earned 1200 total points
ID: 35502698
If you want to support the full CSV syntax (escape characters, quotes, multiline, etc.) then your best bet is to use one of the libs out there, like opencsv.

http://opencsv.sourceforge.net/
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 200 total points
ID: 35502699
I guess with the exception of the driver class name and connection string
the rest of the code should work for other DBs alsod, becuase it is JDBC iinterface
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35502707
That is true - if you have commas inside the fileds - they may cause prblems
then library will be handy - one simple way to deal
with such co,umns is to output double quotes around them
like this for column with value s1:
 psout.println(s0 + ",\"" + s1 + "\"," +...);
Of course you need to be sure that this column does not have double quotes inside

Otherwise, Excel will understand double quotes and will ignore commas
inside the doubl quotes


0
 
LVL 92

Assisted Solution

by:objects
objects earned 200 total points
ID: 35502742
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 35503030
Why don't you use Sybase utility bcp - it's the tool for data export/import.

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/10086;pt=8630/*
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 200 total points
ID: 35503344
0
 
LVL 2

Assisted Solution

by:drittenh
drittenh earned 200 total points
ID: 35514200
Sybase IQ can export data directly to csv file, and this will be faster than bcp.  
This will redirect the output of the select statement directly to the ascii file specified in the name1 parameter:  

set temporary option temp_extract_name1 = '/explicit/path/to/file.dat' ;
set temporary option temp_extract_column_delimiter = ',' ;
set temporary option temp_extract_row_delimiter = '\x0a' ;
set temporary option temp_extract_null_as_empty = 'ON' ;

select * from table_xyz ;  

HTH,

- David
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35514258
Bear in mind the possibility of your using a different rdbms (which jdbc is designed to make trivial) breaking any db-specific utilities
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month19 days, 3 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question