Forum:

Java in General

CSV Format not Working

Ranch Hand

Posts: 74

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

The below program work flawlessly in a recently completed program. However, when I did a new program, using the same CSV code, it doesn't output the database fields to a csv file. I placed a System print just below the data string declaration and do get a partial of the first record --but not the complete record. Not sure what I'm doing different.

In my previous program, I used the comma deliminator by stating the CSVFormat to "CSVFormat format = CSVFormat.RFC4180.withRecordSeparator(",")". In this new program, the "withRecordSeparator" Netbeans deprecates it. Not sure how to resolve this since the format parameter has changed. I use Zulu OpenJDK 17 within Netbeans 17 in a Windows 10 OS.

public void SaveCSVFile() throws IOException{
        JFileChooser fc = new JFileChooser();
        fc.setFileFilter(new FileNameExtensionFilter("ADD  .csv  TO FILE END", "csv"));
        //File f = new File(new File("main.csv").getCanonicalPath());
        fc.setDialogTitle("SAVE OPTION");
      int returnValue = fc.showSaveDialog(null); 
        if (returnValue == JFileChooser.APPROVE_OPTION) {
          //File selectedFile = fc.getSelectedFile();
       SaveFile = fc.getSelectedFile().toString();
       saveCsv();
        }    // If canceled in the JFileChooser then do this: 
  }

private void saveCsv()throws IOException {
      Connection conn = null;
        Statement stmt;
        ResultSet rst;
        
    sqlheader = "PDE, DATETIME, PROPERTY, CONTACTNAME, PHONE, EMAIL, "
            + "PROBLEM,TROUBLESHOOT, RESOLUTION, MISCNOTES, COMPANY, CATEGORY, TASKCODE ";
    
        String FILE_HEADER = sqlheader;
        String sqlLOAD = "SELECT * FROM TASKLOG";
                   
        
         // change to the filename slection in JFileChooser
   
                   try {   
                   
             conn = DriverManager.getConnection("jdbc:derby:ticketLogDB");
            stmt = conn6.createStatement();
            rst = stmt.executeQuery(sqlLOAD);
            FileWriter fw = new FileWriter(SaveFile);
           fw.append(FILE_HEADER);
           //Add a new line separator after the header
          //  BufferedWriter writer = Files.newBufferedWriter(SaveFile));
            CSVPrinter csvPrinter = new CSVPrinter(fw,format);

fw.append(NEW_LINE_SEPARATOR);

while (rst.next()){
                         ResultSetMetaData rsmd = rst.getMetaData();
                          int columnCount = rsmd.getColumnCount();
                                           
                    
                     for(int i=1; i <=columnCount; i++)
                     {
                        if(rst.getObject(i)!=null)
                         {
                             String data= rst.getObject(i).toString();
                             //csvPrinter.printRecord(data);
                             
                         }
                         else
                         {
                             String data= "null";
                              csvPrinter.printRecord(data);
                              
                             
                         }
                     } 
                  //new line entered after each row
                             fw.append(System.getProperty("line.separator"));
                 }        
                     fw.flush();
                      fw.close();
                      csvPrinter.close();
                } catch (SQLException e) {
                } catch (IOException ex) {
          Logger.getLogger(PhoneLogMain.class.getName()).log(Level.SEVERE, null, ex);
      }

};

Carey Brown

Saloon Keeper

Posts: 10796

I like...

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

What do you get?
On line 53 you commented out the print and I don't see the system print you mentioned.

JavaRanch-FAQ HowToAskQuestionsOnJavaRanch UseCodeTags DontWriteLongLines ItDoesntWorkIsUseLess FormatCode JavaIndenter SSCCE API-17 JLS JavaLanguageSpecification MainIsAPain KeyboardUtility

Carey Brown

Saloon Keeper

Posts: 10796

I like...

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Use the auto-formatting capability of Netbeans. Example:

public void SaveCSVFile() throws IOException
	{
		JFileChooser fc = new JFileChooser();
		fc.setFileFilter( new FileNameExtensionFilter( "ADD  .csv  TO FILE END",
				"csv" ) );
		// File f = new File(new File("main.csv").getCanonicalPath());
		fc.setDialogTitle( "SAVE OPTION" );
		int returnValue = fc.showSaveDialog( null );
		if( returnValue == JFileChooser.APPROVE_OPTION )
		{
			// File selectedFile = fc.getSelectedFile();
			SaveFile = fc.getSelectedFile().toString();
			saveCsv();
		}    // If canceled in the JFileChooser then do this:
	}

private void saveCsv() throws IOException
	{
		Connection conn = null;
		Statement stmt;
		ResultSet rst;

sqlheader = "PDE, DATETIME, PROPERTY, CONTACTNAME, PHONE, EMAIL, "
				+ "PROBLEM,TROUBLESHOOT, RESOLUTION, MISCNOTES, COMPANY, CATEGORY, TASKCODE ";

String FILE_HEADER = sqlheader;
		String sqlLOAD = "SELECT * FROM TASKLOG";

// change to the filename slection in JFileChooser

try
		{
			conn = DriverManager.getConnection( "jdbc:derby:ticketLogDB" );
			stmt = conn6.createStatement();
			rst = stmt.executeQuery( sqlLOAD );
			FileWriter fw = new FileWriter( SaveFile );
			fw.append( FILE_HEADER );
			// Add a new line separator after the header
			// BufferedWriter writer = Files.newBufferedWriter(SaveFile));
			CSVPrinter csvPrinter = new CSVPrinter( fw, format );

fw.append( NEW_LINE_SEPARATOR );

while( rst.next() )
			{
				ResultSetMetaData rsmd = rst.getMetaData();
				int columnCount = rsmd.getColumnCount();

for( int i = 1 ; i <= columnCount ; i++ )
				{
					if( rst.getObject( i ) != null )
					{
						String data = rst.getObject( i ).toString();
						// csvPrinter.printRecord(data);
					}
					else
					{
						String data = "null";
						csvPrinter.printRecord( data );
					}
				}
				// new line entered after each row
				fw.append( System.getProperty( "line.separator" ) );
			}
			fw.flush();
			fw.close();
			csvPrinter.close();
		}
		catch( SQLException e )
		{
		}
		catch( IOException ex )
		{
			Logger.getLogger( PhoneLogMain.class.getName() ).log( Level.SEVERE,
					null, ex );
		}
	}

JavaRanch-FAQ HowToAskQuestionsOnJavaRanch UseCodeTags DontWriteLongLines ItDoesntWorkIsUseLess FormatCode JavaIndenter SSCCE API-17 JLS JavaLanguageSpecification MainIsAPain KeyboardUtility

Carey Brown

Saloon Keeper

Posts: 10796

I like...

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

You open "conn" but use "conn6".

JavaRanch-FAQ HowToAskQuestionsOnJavaRanch UseCodeTags DontWriteLongLines ItDoesntWorkIsUseLess FormatCode JavaIndenter SSCCE API-17 JLS JavaLanguageSpecification MainIsAPain KeyboardUtility

Ron McLeod

Marshal

Posts: 4533

572

I like...

posted 8 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

You should either handle the database exceptions or let them bubble up. Hiding them is going to bring you grief, in both development and production.

Ron McLeod

Marshal

Posts: 4533

572

I like...

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Bill Melendez wrote:I used the comma deliminator by stating the CSVFormat to "CSVFormat format = CSVFormat.RFC4180.withRecordSeparator(",")". In this new program, the "withRecordSeparator" Netbeans deprecates it.

Assuming that you are using Apache Commons CSV, the current documentation indicates that you should be using a CSVFormat.Builder and setting the delimiter with the setRecordSeparator method.

Grzegorz Wilanowski

Ranch Hand

Posts: 32

posted 8 months ago

1
Number of slices to send:

Optional 'thank-you' note:

Send

This works for me

import java.sql.SQLException;
import java.io.IOException;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.CSVFormat;
public class MyCSV{
    public static void main(String[] args) throws IOException{
        (new MyCSV()).saveCsv();
    }
    public void saveCsv()throws IOException {
        Connection conn = null;
        Statement stmt;
        ResultSet rst;
        String sqlheader = "c1,c2,c3 ";
        String FILE_HEADER = sqlheader;
        String sqlLOAD = "SELECT * FROM mytable";
        // change to the filename slection in JFileChooser
        try {
            conn = DriverManager.getConnection("jdbc:sqlite:mydb.db");
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sqlLOAD);
            FileWriter fw = new FileWriter("mycsv.csv");
            fw.append(FILE_HEADER);
            CSVFormat format =  CSVFormat.Builder.create().setRecordSeparator(",").build() ;
            CSVPrinter csvPrinter = new CSVPrinter(fw, format  );
            fw.append(System.getProperty("line.separator"));
            while (rst.next()){
                ResultSetMetaData rsmd = rst.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for(int i=1; i <=columnCount; i++)
                {
                    if(rst.getObject(i)!=null)
                    {
                        String data= rst.getObject(i).toString();
                        csvPrinter.printRecord(data);
                    }
                    else
                    {
                        String data= "null";
                        csvPrinter.printRecord(data);
                    }
                }
                //new line entered after each row
                fw.append(System.getProperty("line.separator"));
            }
            fw.flush();
            fw.close();
            csvPrinter.close();
        } catch (SQLException e) {  System.out.println(e);
        } catch (IOException ex) {  System.out.println(ex);
        }
    }

I used here sqlite mydb.db with mytable:

sqlite> .schema mytable
CREATE TABLE mytable(c1 TEXT,c2 TEXT,c3 REAL);
sqlite> select * from mytable;
aaa|my text|1.2
bbb|my text 2|0.99
ccc|my text 3|1.33

And the output mycsv.csv file is

c1,c2,c3
aaa,my text,1.2,
bbb,my text 2,0.99,
ccc,my text 3,1.33,

java 17

Bill Melendez

Ranch Hand

Posts: 74

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Grzegorz: Thanks. I redid the whole thing to the below code which works great for me. I do appreciate your post though. The key is to move away from having a "main" that activates the CSV export. Instead, I prefer to use it as a "method" that can work when called by a jbutton within a main program.

public void SaveCSVFile() throws IOException, SQLException{
       JFileChooser fc = new JFileChooser();
        fc.setFileFilter(new FileNameExtensionFilter("CSV Files (*.csv)", "csv"));

fc.setDialogTitle("SAVE OPTION");
      int returnValue = fc.showSaveDialog(null); 
        if (returnValue == JFileChooser.APPROVE_OPTION) {
          File selectedFile = fc.getSelectedFile();
       SaveFile = fc.getSelectedFile().toString();
       saveCsv();
        }    // If canceled in the JFileChooser then do this: 
  }

private void saveCsv() throws IOException, SQLException {
    String FILE_HEADER = sqlheader;
     String sqlheader = "PDE,DATE,PROPERTY,CONTACTNAME,PHONE,EMAIL,PROBLEM,TROUBLESHOOT,RESOLUTION,MISNOTES,COMPANY,CATEGORY,TASKCODE";  
   try{
     String FILW_HEADER = sqlheader;
     String sqlLOAD = "SELECT * FROM TASKLOG";
List<String> rowData = null;
    try (Connection conn = DriverManager.getConnection("jdbc:derby:ticketLogDB");
         Statement stmt = conn.createStatement();
         ResultSet rst = stmt.executeQuery(sqlLOAD);
         FileWriter fw = new FileWriter(SaveFile);
         
            
         CSVPrinter csvPrinter = new CSVPrinter(fw, CSVFormat.DEFAULT);
    ) {
        ResultSetMetaData metaData = rst.getMetaData();
        int columnCount = metaData.getColumnCount();
         csvPrinter.printHeaders(rst);
        while (rst.next()) {
           rowData = new ArrayList<>();

for (int i = 1; i <= columnCount; i++) {
                String data = rst.getString(i);
                if (data != null) {
                    rowData.add(data);
                } else {
                    rowData.add("null");
                }
            }
                 
                
            csvPrinter.printRecord(rowData);
        }
    } catch (SQLException e) {
        // Handle SQLException
    } catch (IOException ex) {
        Logger.getLogger(PhoneLog.class.getName()).log(Level.SEVERE, null, ex);
    }
   }catch(Exception e){}
}

Bill Melendez

Ranch Hand

Posts: 74

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Grzegorz: here's the main screen that applies the CSV Save output. It is a Phone Logging for customer support calls and actions taken. I cleared the entry form since the information is not needed for displaying this screen to this post. Otherwise, the right side is a JTABLE with the database records displayed. Clicking on a row will populate the entry form.

PhoneLog-Main-Screen.png

Carey Brown

Saloon Keeper

Posts: 10796

I like...

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Indent cleanup.
Remove outer try, unnecessary.
Declare rowData on line 29.
Comment out unused HEADER String.
Marked probable problem on line 27.
Removed throws because you don't.
Catching all non-IOExceptions is a bad idea. at least log them.

public void SaveCSVFile() {
		JFileChooser fc = new JFileChooser();
		fc.setFileFilter(
				new FileNameExtensionFilter( "CSV Files (*.csv)", "csv" ) );

fc.setDialogTitle( "SAVE OPTION" );
		int returnValue = fc.showSaveDialog( null );
		if( returnValue == JFileChooser.APPROVE_OPTION ) {
			File selectedFile = fc.getSelectedFile();
			String SaveFile = fc.getSelectedFile().toString();
			saveCsv( SaveFile );
		}    // If canceled in the JFileChooser then do this:
	}

private void saveCsv( String SaveFile ) {
		// String FILW_HEADER = "PDE,DATE,PROPERTY,CONTACTNAME,PHONE,EMAIL," +
		// "PROBLEM,TROUBLESHOOT,RESOLUTION,MISNOTES,COMPANY,CATEGORY,TASKCODE";
		String sqlLOAD = "SELECT * FROM TASKLOG";

try( Connection conn = DriverManager.getConnection( "jdbc:derby:ticketLogDB" ) ;
			Statement stmt = conn.createStatement() ;
			ResultSet rst = stmt.executeQuery( sqlLOAD ) ;
			FileWriter fw = new FileWriter( SaveFile ) ;
			CSVPrinter csvPrinter = new CSVPrinter( fw,	CSVFormat.DEFAULT ) ;
        ) {
			ResultSetMetaData metaData = rst.getMetaData();
			int columnCount = metaData.getColumnCount();
			csvPrinter.printHeaders( rst ); //*** ??? should use metaData
			while( rst.next() ) {
				List<String> rowData = new ArrayList<>();
				for( int i = 1 ; i <= columnCount ; i++ ) {
					String data = rst.getString( i );
					if( data != null ) {
						rowData.add( data );
					}
					else {
						rowData.add( "null" );
					}
				}
				csvPrinter.printRecord( rowData );
			}
		}
		catch( IOException ex ) {
			Logger.getLogger( PhoneLog.class.getName() ).log( Level.SEVERE,
					null, ex );
		}
		catch( Exception e ) { // everything else (bad idea)
			// Handle SQLException
		}
	}

JavaRanch-FAQ HowToAskQuestionsOnJavaRanch UseCodeTags DontWriteLongLines ItDoesntWorkIsUseLess FormatCode JavaIndenter SSCCE API-17 JLS JavaLanguageSpecification MainIsAPain KeyboardUtility

Tim Holloway

Saloon Keeper

Posts: 27865

196

I like...

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Ron McLeod wrote:You should either handle the database exceptions or let them bubble up. Hiding them is going to bring you grief, in both development and production.catch( SQLException e ) { }

Hiding them would earn you a good hiding if I ever inherited that code.

The secret of how to be miserable is to constantly expect things are going to happen the way that they are "supposed" to happen.

You can have faith, which carries the understanding that you may be disappointed. Then there's being a willfully-blind idiot, which virtually guarantees it.

Bill Melendez

Ranch Hand

Posts: 74

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

@Carey: Thank you so much for your insight and comments. I will apply what you suggested.

Paul Clapham

Marshal

Posts: 28262

I like...

posted 8 months ago

Number of slices to send:

Optional 'thank-you' note:

Send

Ron McLeod wrote:You should either handle the database exceptions or let them bubble up. Hiding them is going to bring you grief, in both development and production.catch( SQLException e ) { }

A better way to fix this particular instance of exception hiding:

Commonly called "multicatch", released in Java 7.

With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.