 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / March 2010Export data to CSV in SQL Server
Hi, I am trying to write a Java JDBC program exporting data from the database into CSV file using SQL Server 2005 DB. From that query: String="SELECT * into OUTFILE '"+filename+'" FIELDS TERMINATED BY ',' FROM table1"; getting: "SQLException: Incorrect syntax near 'myfilename.csv' Yes, I put filename in single quotes. Same problem without them, but it strips out csv file extension. Using JTDS driver 1.2.4 on WinXP. Will later need to insert that into a batch process. What is a reason for that error? - I saw many many posts on the net, but no good explanations so far. So how do I fix that ? Is there a better way of doing it in SQL server ? I am a Java developer, not a DBA or Unix Sys Admin ;-) Please help ! TIA, Oleg. > Hi, > [quoted text clipped - 8 lines] > Yes, I put filename in single quotes. Same problem without them, but it > strips out csv file extension. Obvious: your single and double quotes are mismatched. Use '"+filename+"' instead of '"+filename+'"  Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | >> I am trying to write a Java JDBC program exporting data from the >> database into CSV file using SQL Server 2005 DB. [quoted text clipped - 10 lines] > > Use '"+filename+"' instead of '"+filename+'" I don't think he would get an SQLException if his code really had that problem. Arne >>> I am trying to write a Java JDBC program exporting data from the >>> database into CSV file using SQL Server 2005 DB. [quoted text clipped - 13 lines] > I don't think he would get an SQLException if his code really had that > problem. I'm surprised the OP's code even compiled unless '"+filename+'" was a typo. My rearrangement would at least compile correctly. I don't know SQL Server well enough to know whether it would like the 'into outfile' syntax. Postgres doesn't. Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | > I'm surprised the OP's code even compiled unless '"+filename+'" was a > typo. My rearrangement would at least compile correctly. Perhaps the OP could respond and let us know. At the very least they should know that copied-and-pasted is the way to go, rather than paraphrased with specious errors added. > I don't know SQL Server well enough to know whether it would like > the 'into outfile' syntax. Postgres doesn't. Perhaps the OP could read the docs (!) and let us know if that's the answer. SignatureLew >>>> I am trying to write a Java JDBC program exporting data from the >>>> database into CSV file using SQL Server 2005 DB. [quoted text clipped - 16 lines] > I'm surprised the OP's code even compiled unless '"+filename+'" was a > typo. That was my point. Arne > I am trying to write a Java JDBC program exporting data > from the database into CSV file using SQL Server 2005 DB. [quoted text clipped - 13 lines] > but no good explanations so far. > So how do I fix that ? I believe that SELECT INTO OUTFILE is MySQL not SQLServer ! > Is there a better way of doing it in SQL server ? Why not just have the Java code write the CSV file - it is relative simple. Arne Arne, You are right, snippet I found and try to use is using MySQL, is it supposed to fail with SQL Server ? > Why not just have the Java code write the CSV file - it is > relative simple. I am a Web developer, not a backend guy and have never done that. Any code sample ? Do you mean processing resultset and writing to a file manually, not from SQL ? Any T-SQL features to help with CSV? Martin, with quotes: it was a typo which is NOT in my code. TIA, Oleg. > You are right, snippet I found and try to use is using MySQL, > is it supposed to fail with SQL Server ? Yes. SQLServer does not support that syntax. >> Why not just have the Java code write the CSV file - it is >> relative simple. > I am a Web developer, not a backend guy and have never done that. > Any code sample ? > Do you mean processing resultset and writing to a file manually, not > from SQL ? Yes. It is trivial to do a SELECT and for all rows write out all columns separated by comma or whatever. > Any T-SQL features to help with CSV? DTS/SSIS can do it. But I am not aware of any way to use that from Java. Arne >From that query: >String="SELECT * into OUTFILE '"+filename+'" FIELDS TERMINATED BY ',' >FROM table1"; >getting: "SQLException: Incorrect syntax near 'myfilename.csv' That won't even compile. What did you REALLY write? Try experimenting with the ad hoc query tools that let you key queries. Once you have the SQL correct, concentrate on the Java equivalent. SignatureRoedy Green Canadian Mind Products http://mindprod.com The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time. ~ Tom Cargill Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.