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

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

public class MSAccessToCSV extends JDBCBase
{
    /*
    
    final static String TABLE_NAME = "WAIKATO_INDEPENDENT";
    
    public static void printDatabaseMetadata(Connection connection)
    {
        try {

        System.out.println("----");
        System.out.println("Database Table Names");
        System.out.println("----");
            DatabaseMetaData dbmd = connection.getMetaData();
            String[] types = {"TABLE"};
            ResultSet rs = dbmd.getTables(null, null, "%", types);
            while (rs.next()) {
                System.out.println(rs.getString("TABLE_NAME"));
            }
        System.out.println("----");
        } 
            catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void printTable(Connection connection, String table_name)
    {
    try {
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery("SELECT * from " + table_name);
        ResultSetMetaData rsmd = rs.getMetaData();
        
        int columnsNumber = rsmd.getColumnCount();                     
        
        // Iterate through the data in the result set and display it. 
        System.out.println("----");
        System.out.println("Table: " + table_name);
        System.out.println("----");

        for(int i = 1; i <= columnsNumber; i++) {
        System.out.print(rsmd.getColumnLabel(i)+ "|");
        }
        System.out.println();
        
        while (rs.next()) {
        for(int i = 1 ; i <= columnsNumber; i++){
            System.out.print(rs.getString(i));
            if (i != columnsNumber) { System.out.print(","); }
        }
        
        System.out.println();
        
        }
            
        System.out.println("----");
        
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    
    }

    */
    
    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);

        
        /*
            String sql = "INSERT INTO Contacts (Full_Name, Email, Phone) VALUES (?, ?, ?)";
             
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "Jim Rohn");
            preparedStatement.setString(2, "[email protected]");
            preparedStatement.setString(3, "0919989998");
             
            int row = preparedStatement.executeUpdate();
             
            if (row > 0) {
                System.out.println("A row has been inserted successfully.");
            }
             
            sql = "SELECT * FROM Contacts";
             
            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery(sql);
             
            while (result.next()) {
                int id = result.getInt("Contact_ID");
                String fullname = result.getString("Full_Name");
                String email = result.getString("Email");
                String phone = result.getString("Phone");
                 
                System.out.println(id + ", " + fullname + ", " + email + ", " + phone);
            }
        */
        
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}