source: main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/MSAccessToCSV/src/main/java/org/greenstone/accessdb/MSAccessToCSV.java@ 34501

package org.greenstone.accessdb;
 
import java.sql.*;
import java.io.OutputStreamWriter;

public class MSAccessToCSV extends JDBCBase
{
  
    public static void msAccessTableToCSVFile(Connection connection, String table_name, String csv_filename)
    {
    try {
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery("SELECT * from " + table_name);
        ResultSetMetaData rsmd = rs.getMetaData();
        
        int columnsNumber = rsmd.getColumnCount();                     

        System.out.println("Converting MS Access Database Table '" + table_name + "' to CSV file: " + csv_filename);
        
        OutputStreamWriter csv_osw = UtilIO.openTextFile(csv_filename);

        if (csv_osw != null) {
        
        for (int i=1; i<=columnsNumber; i++) {
            String heading = rsmd.getColumnLabel(i);
            String heading_safe = heading;

            if (heading_safe != null) {
            UtilIO.appendTextToFile(csv_osw,heading_safe);
            }

            if (i < columnsNumber) { UtilIO.appendTextToFile(csv_osw,","); }
            
        }
        UtilIO.appendTextToFile(csv_osw,"\n");

        
        int line_count = 0;
        
        while (rs.next()) {
            for (int i=1 ; i<=columnsNumber; i++){
            String val = rs.getString(i);
            
            if (val != null) {

                int val_len = val.length();
                
                //if ((val_len>=2) && ((val.charAt(0) == '"') && (val.charAt(val_len-1) == '"'))) {
                //val = val.substring(1,val_len-2);
                //}
            
                String val_safe = val.replaceAll(",","\\\\,").replaceAll("\"","\\\\\"");

                UtilIO.appendTextToFile(csv_osw,'"'+val_safe+'"');
            }
            
            if (i < columnsNumber) { UtilIO.appendTextToFile(csv_osw,","); }
            }

            UtilIO.appendTextToFile(csv_osw,"\n");          

            line_count++;
            if ((line_count % 1000) == 0) {
            System.out.println("Processed lines " + (line_count - 999) + "-" + line_count);
            }
        }
        
        
        UtilIO.closeTextFile(csv_osw);
        
        }       
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    
    }

    public static void main( String[] args )
    {

    /* Note:

       If needing to load via a absolute filename on Windows, example for this is:
          "jdbc:ucanaccess://d:/cygwin64/home/<USERNAME>/.../waikato-independent-db/Waikato Independent.accdb"
    */

    // Assuming database is relative to directory where script is run from:
    String inputDatabaseURI = "jdbc:ucanaccess://Waikato Independent.accdb";
        
    System.out.println("Opening: '" + inputDatabaseURI + "' ...");
    
        try (Connection connection = DriverManager.getConnection(inputDatabaseURI)) {
        System.out.println("... connnected");

        printDatabaseMetadata(connection);

        System.out.println();

        //printTable(connection, TABLE_NAME);
        
        String csv_filename = TABLE_NAME + ".csv";
        
        msAccessTableToCSVFile(connection, TABLE_NAME, csv_filename);
        
        }
    catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}