Changeset 34502 for main/trunk
- Timestamp:
- 2020-10-20T15:31:04+13:00 (4 years ago)
- Location:
- main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/MSAccessToCSV
- Files:
-
- 1 added
- 3 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/MSAccessToCSV/src/main/java/org/greenstone/accessdb/JDBCBase.java
r34501 r34502 1 1 package org.greenstone.accessdb; 2 2 3 import java.util.ArrayList; 3 4 import java.sql.*; 4 5 5 6 public class JDBCBase 6 7 { 8 // Name of the table inside the MS Access DB 9 final static String TABLE_NAME = "WAIKATO_INDEPENDENT"; 10 11 public static ArrayList<String> getColumnLabels(Connection connection, String table_name) 12 { 13 // Read in column labels into ArrayList 14 ArrayList<String> column_labels = null; 15 16 try { 17 Statement st = connection.createStatement(); 18 ResultSet rs = st.executeQuery("SELECT * from " + table_name); 19 20 ResultSetMetaData rs_md = rs.getMetaData(); 21 22 int columnCount = rs_md.getColumnCount(); 23 24 25 column_labels = new ArrayList<String>(columnCount); 26 27 for (int i=1; i<=columnCount; i++) { 28 String heading = rs_md.getColumnLabel(i); 29 column_labels.add(heading); // add even if null, so can keep count 30 if (heading == null) { 31 System.err.println("*** Encountered null column label at column postion: " + i); 32 } 33 } 34 } 35 catch (Exception e) { 36 e.printStackTrace(); 37 } 38 39 return column_labels; 40 } 7 41 8 final static String TABLE_NAME = "WAIKATO_INDEPENDENT"; 9 42 10 43 public static void printDatabaseMetadata(Connection connection) 11 44 { … … 33 66 Statement st = connection.createStatement(); 34 67 ResultSet rs = st.executeQuery("SELECT * from " + table_name); 35 ResultSetMetaData rs md = rs.getMetaData();68 ResultSetMetaData rs_md = rs.getMetaData(); 36 69 37 int column sNumber = rsmd.getColumnCount();70 int columnCount = rs_md.getColumnCount(); 38 71 39 72 // Iterate through the data in the result set and display it. … … 42 75 System.out.println("----"); 43 76 44 for(int i = 1; i <= column sNumber; i++) {45 System.out.print(rs md.getColumnLabel(i)+ "|");77 for(int i = 1; i <= columnCount; i++) { 78 System.out.print(rs_md.getColumnLabel(i)+ "|"); 46 79 } 47 80 System.out.println(); 48 81 49 82 while (rs.next()) { 50 for(int i = 1 ; i <= column sNumber; i++){83 for(int i = 1 ; i <= columnCount; i++){ 51 84 System.out.print(rs.getString(i)); 52 if (i != column sNumber) { System.out.print(","); }85 if (i != columnCount) { System.out.print(","); } 53 86 } 54 87 -
main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/MSAccessToCSV/src/main/java/org/greenstone/accessdb/MSAccessToCSV.java
r34501 r34502 17 17 18 18 System.out.println("Converting MS Access Database Table '" + table_name + "' to CSV file: " + csv_filename); 19 19 20 20 OutputStreamWriter csv_osw = UtilIO.openTextFile(csv_filename); 21 21 … … 37 37 38 38 int line_count = 0; 39 int max_varchar_len = -1; 40 39 41 40 42 while (rs.next()) { … … 45 47 46 48 int val_len = val.length(); 49 50 if (val_len > max_varchar_len) { 51 max_varchar_len = val_len; 52 } 47 53 48 54 //if ((val_len>=2) && ((val.charAt(0) == '"') && (val.charAt(val_len-1) == '"'))) { … … 68 74 69 75 UtilIO.closeTextFile(csv_osw); 70 76 77 78 System.out.println("****"); 79 System.out.println("* Max VARCHAR len: " + max_varchar_len); 80 System.out.println("****"); 71 81 } 72 82 } -
main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/MSAccessToCSV/src/main/java/org/greenstone/accessdb/MSAccessToSQLite.java
r34500 r34502 1 1 package org.greenstone.accessdb; 2 2 3 import java.util.ArrayList; 3 4 import java.sql.*; 4 import java.io.OutputStreamWriter;5 5 6 6 public class MSAccessToSQLite extends JDBCBase 7 7 { 8 8 9 public static void msAccessTableTo CSVFile(Connection connection, String table_name, String csv_filename)9 public static void msAccessTableToSQLite(Connection input_connection, String table_name, String output_database_uri) 10 10 { 11 try { 12 Statement st = connection.createStatement(); 13 ResultSet rs = st.executeQuery("SELECT * from " + table_name); 14 ResultSetMetaData rsmd = rs.getMetaData(); 15 16 int columnsNumber = rsmd.getColumnCount(); 17 18 System.out.println("Converting MS Access Database Table '" + table_name + "' to CSV file: " + csv_filename); 19 20 OutputStreamWriter csv_osw = UtilIO.openTextFile(csv_filename); 21 22 if (csv_osw != null) { 23 24 for (int i=1; i<=columnsNumber; i++) { 25 String heading = rsmd.getColumnLabel(i); 26 String heading_safe = heading; 27 28 if (heading_safe != null) { 29 UtilIO.appendTextToFile(csv_osw,heading_safe); 11 // Create table in output DB (wiping out existing one) 12 // Then 13 // 1. setup column labels 14 // 2. populate with values 15 16 try (Connection output_connection = DriverManager.getConnection(output_database_uri)) { 17 // Lookup the number of columns in the input database 18 ArrayList<String> column_labels = getColumnLabels(input_connection,table_name); 19 int column_count = column_labels.size(); 20 21 System.out.println("Converting MS Access Database Table '" + table_name + "' to SQLLite DB: " + output_database_uri); 22 23 // drop/delete table 24 Statement drop_statement = output_connection.createStatement(); 25 String sql_drop_str = "DROP TABLE IF EXISTS " + table_name; 26 drop_statement.executeUpdate(sql_drop_str); 27 28 // build up buffer into the form: 29 // CREATE TABLE <table_name> (id INTEGER not NULL, Title VARCHAR(255), Name VARCHAR(255), PRIMARY KEY ( id)) 30 StringBuffer sql_createtab_strbuf = new StringBuffer(); 31 32 // build up buffer into the form 33 // INSERT INTO <table_name> (Title, Name) VALUES (?, ?) 34 // which is then used in conjunction with preparedStatement.setString() 35 // Note: INSERT no need to specify 'id', as this is generated automatically as part of it being the primary key 36 StringBuffer sql_insertvals_strbuf = new StringBuffer(); 37 StringBuffer sql_valholders_strbuf = new StringBuffer(); 38 39 sql_createtab_strbuf.append("CREATE TABLE " + table_name + " ("); 40 sql_createtab_strbuf.append("id INTEGER not NULL"); 41 42 sql_insertvals_strbuf.append("INSERT INTO " + table_name + "("); 43 sql_valholders_strbuf.append("VALUES ("); 44 45 for (int i=0; i<column_count; i++) { 46 String heading = column_labels.get(i); 47 48 String heading_safe = heading; // if escaping is needed, do it here! 49 if (heading_safe != null) { 50 sql_createtab_strbuf.append(", " + heading_safe + " VARCHAR(255)"); 51 } 52 else { 53 sql_createtab_strbuf.append(", VARCHAR(255)"); 54 } 55 56 57 if (i > 0) { 58 sql_insertvals_strbuf.append(", " + heading_safe); 59 sql_valholders_strbuf.append(", ?"); 60 } 61 else { 62 sql_insertvals_strbuf.append(heading_safe); 63 sql_valholders_strbuf.append("?"); 64 } 65 } 66 67 sql_createtab_strbuf.append(", PRIMARY KEY ( id ))"); 68 sql_insertvals_strbuf.append(")"); 69 sql_valholders_strbuf.append(")"); 70 71 String sql_insertvals_str = sql_insertvals_strbuf.toString() + " " + sql_valholders_strbuf.toString(); 72 73 74 // Create the table 75 Statement createtab_statement = output_connection.createStatement(); 76 String sql_createtab_str = sql_createtab_strbuf.toString() ; 77 createtab_statement.executeUpdate(sql_createtab_str); 78 79 80 // Now insert the data values 81 82 Statement input_statement = input_connection.createStatement(); 83 ResultSet input_resultset = input_statement.executeQuery("SELECT * from " + table_name); 84 85 int line_count = 0; 86 87 while (input_resultset.next()) { 88 89 PreparedStatement prepared_output_statement = output_connection.prepareStatement(sql_insertvals_str); 90 91 for (int i=1; i<=column_count; i++) { 92 String val = input_resultset.getString(i); 93 94 if (val != null) { 95 String val_safe = val; // escape here if needed 96 prepared_output_statement.setString(i, val_safe); 30 97 } 31 32 if (i < columnsNumber) { UtilIO.appendTextToFile(csv_osw,","); } 33 34 } 35 UtilIO.appendTextToFile(csv_osw,"\n"); 36 37 38 int line_count = 0; 39 40 while (rs.next()) { 41 for (int i=1 ; i<=columnsNumber; i++){ 42 String val = rs.getString(i); 43 44 if (val != null) { 45 46 int val_len = val.length(); 47 48 //if ((val_len>=2) && ((val.charAt(0) == '"') && (val.charAt(val_len-1) == '"'))) { 49 //val = val.substring(1,val_len-2); 50 //} 51 52 String val_safe = val.replaceAll(",","\\\\,").replaceAll("\"","\\\\\""); 53 54 UtilIO.appendTextToFile(csv_osw,'"'+val_safe+'"'); 55 } 56 57 if (i < columnsNumber) { UtilIO.appendTextToFile(csv_osw,","); } 98 else { 99 prepared_output_statement.setString(i, ""); 58 100 } 59 60 UtilIO.appendTextToFile(csv_osw,"\n"); 61 62 line_count++; 63 if ((line_count % 1000) == 0) { 64 System.out.println("Processed lines " + (line_count - 999) + "-" + line_count); 65 } 66 } 67 68 69 UtilIO.closeTextFile(csv_osw); 70 71 } 72 } 73 catch (Exception e) { 74 e.printStackTrace(); 75 } 76 77 } 78 79 public static void main( String[] args ) 80 { 81 82 /* Note: 83 84 If needing to load via a absolute filename on Windows, example for this is: 85 "jdbc:ucanaccess://d:/cygwin64/home/<USERNAME>/.../waikato-independent-db/Waikato Independent.accdb" 86 */ 87 88 // Assuming database is relative to directory where script is run from: 89 String inputDatabaseURI = "jdbc:ucanaccess://Waikato Independent.accdb"; 90 91 System.out.println("Opening: '" + inputDatabaseURI + "' ..."); 92 93 try (Connection connection = DriverManager.getConnection(inputDatabaseURI)) { 94 System.out.println("... connnected"); 95 96 printDatabaseMetadata(connection); 97 98 System.out.println(); 99 100 //printTable(connection, TABLE_NAME); 101 102 String csv_filename = TABLE_NAME + ".csv"; 103 104 msAccessTableToCSVFile(connection, TABLE_NAME, csv_filename); 105 106 101 } 102 103 int output_row = prepared_output_statement.executeUpdate(); 104 105 if (output_row != 1) { 106 System.err.println("Error: failed to insert table row for line " + line_count + "of input database"); 107 } 108 109 110 line_count++; 111 if ((line_count % 1000) == 0) { 112 System.out.println("Processed lines " + (line_count - 999) + "-" + line_count); 113 } 114 } 115 116 117 107 118 /* 108 119 String sql = "INSERT INTO Contacts (Full_Name, Email, Phone) VALUES (?, ?, ?)"; … … 133 144 } 134 145 */ 146 147 148 output_connection.close(); 149 150 } 151 catch (Exception e) { 152 e.printStackTrace(); 153 } 154 155 } 156 157 public static void main( String[] args ) 158 { 159 try { 160 // Included to trigger maven including the necessary Java class and native files 161 // in the generated 'with-dependencies.jar' file 162 163 // Looks like this step could be avoided if more details are stored in the maven 164 // pom.xml file: 165 // https://stackoverflow.com/questions/16725377/no-suitable-driver-found-sqlite 166 167 Class.forName("org.sqlite.JDBC"); 168 } 169 catch (Exception e) { 170 e.printStackTrace(); 171 } 172 173 174 /* Note: 175 176 If needing to load via a absolute filename on Windows, example for this is: 177 "jdbc:ucanaccess://d:/cygwin64/home/<USERNAME>/.../waikato-independent-db/Waikato Independent.accdb" 178 */ 179 180 // Assuming database is relative to directory where script is run from: 181 String input_database_uri = "jdbc:ucanaccess://Waikato Independent.accdb"; 182 String output_database_uri = "jdbc:sqlite:Waikato-Independent.sqlite"; 183 184 System.out.println("Opening: '" + input_database_uri + "' ..."); 185 186 try (Connection input_connection = DriverManager.getConnection(input_database_uri)) { 187 System.out.println("... connnected"); 188 189 printDatabaseMetadata(input_connection); 190 191 System.out.println(); 192 193 //printTable(input_connection, TABLE_NAME); 194 195 String csv_filename = TABLE_NAME + ".csv"; 196 197 msAccessTableToSQLite(input_connection, TABLE_NAME, output_database_uri); 198 199 input_connection.close(); 135 200 136 201 } catch (SQLException ex) {
Note:
See TracChangeset
for help on using the changeset viewer.