CSV to HTML Converter
I have written a little program that converts a CSV-File to an HTML-Table. It works for my purposes. But are there parts in my code that can be written more clean? Can you improve maybe the performance? Are there maybe any bugs? I searched for bugs and fortunately I did not find some. Postscript Maybe I should have provided some background information: I am working on a database documentation that I am writing as an HTML document, because I dont like Word-documents. However, creating a tabular description of the columns with dozens of tags is painful. That is why I wrote this script: Now I only have to export the table information as CSV and can convert it directly without having to enter many tags myself. This is why there are no HTML and body tags: The tables created should not be separate HTML documents, but parts of a single, large HTML document. CsvToHtmlTable.java
import java.io.BufferedReader; import java.io.FileReader; import java.io.IOException; import java.io.FileWriter; import java.util.List; import java.util.ArrayList; public class CsvToHtmlTable < public static void main(String[] args) < // print info and show user how to call the program if needed System.out.println("This program is tested only for UTF-8 files."); if (args[0].equalsIgnoreCase("help") || args[0].equalsIgnoreCase("-help") || args.length != 2) < System.out.println("java CsvToHtmlTable "); System.out.println("Example: java CsvToHtmlTable nice.csv nice.html"); System.exit(0); > String csvFile = args[0]; String outputFile = args[1]; // read lines of csv to a string array list List lines = new ArrayList(); try (BufferedReader reader = new BufferedReader(new FileReader(csvFile))) < String currentLine; while ((currentLine = reader.readLine()) != null) < lines.add(currentLine); >> catch (IOException e) < e.printStackTrace(); >//embrace and for lines and columns for (int i = 0; i < lines.size(); i++) < lines.set(i, " " + lines.get(i) + " ")); > // embrace and
lines.set(0, "" + lines.get(0)); lines.set(lines.size() - 1, lines.get(lines.size() - 1) + "
"); // output result try (FileWriter writer = new FileWriter(outputFile)) < for (String line : lines) < writer.write(line + "\n"); >> catch (IOException e) < e.printStackTrace(); >> > java CsvToHtmlTable ExampleInput.csv ExampleOutput.html
Name,Vorname,Alter Ulbrecht,Klaus Dieter,12 Meier,Bertha,102
Name Vorname Alter Ulbrecht Klaus Dieter 12 Meier Bertha 102
Java: How to Load CSV file into Database
Loading CSV file into Database can be cumbersome task if your Database provider does not offer an out of box feature for this. Most of the time you’ll spend up in creating valid insert statements and putting up values escaping all special characters. Importing CSV files gets a bit complicated when you start doing things like importing files with description fields that can contain punctuation (such as commas or single-double quotation marks). So here’s a simple Java Utility class that can be used to load CSV file into Database. Note how we used some of the best practices for loading data. The CSV file is parsed line by line and SQL insert query is created. The values in query are binded and query is added to SQL batch. Each batch is executed when a limit is reached (in this case 1000 queries per batch).
Import CSV into Database example
Let’s us check an example. Below is the sample CSV file that I want to upload in database table Customer. employee.csv – Sample CSV file:
EMPLOYEE_ID,FIRSTNAME,LASTNAME,BIRTHDATE,SALARY 1,Dean,Winchester,27.03.1975,60000 2,John,Winchester,01.05.1960,120000 3,Sam,Winchester,04.01.1980,56000Code language: CSS (css)
The Table customer contains few fields. We added fields of different types like VARCHAR, DATE, NUMBER to check our load method works properly. Table: Customer – Database table
CREATE TABLE Customer ( EMPLOYEE_ID NUMBER, FIRSTNAME VARCHAR2(50 BYTE), LASTNAME VARCHAR2(50 BYTE), BIRTHDATE DATE, SALARY NUMBER )Code language: SQL (Structured Query Language) (sql)
Following is a sample Java class that will use CSVLoader utility class (we will come to this shortly). Main.java – Load sample.csv to database
package net.viralpatel.java; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class Main < private static String JDBC_CONNECTION_URL = "jdbc:oracle:thin:SCOTT/[email protected]:1500:MyDB"; public static void main(String[] args) < try < CSVLoader loader = new CSVLoader(getCon()); loader.loadCSV("C:\\employee.sql", "CUSTOMER", true); > catch (Exception e) < e.printStackTrace(); >> private static Connection getCon() < Connection connection = null; try < Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection(JDBC_CONNECTION_URL); > catch (ClassNotFoundException e) < e.printStackTrace(); >catch (SQLException e) < e.printStackTrace(); >return connection; > > Code language: Java (java)
In above Main class, we created an object of class CSVLoader using parameterized constructor and passed java.sql.Connection object. Then we called the loadCSV method with three arguments. First the path of CSV file, second the table name where data needs to be loaded and third boolean parameter which decides whether table has to be truncated before inserting new records. Execute this Java class and you’ll see the records getting inserted in table.
The CSV is successfully loaded in database. Let’s check the Utility class now. I strongly recommend you to go through below tutorials as the Utility class combines the idea from these tutorials.
The utility class uses OpenCSV library to load and parse CSV file. Then it uses the idea of Batching in JDBC to batch insert queries and execute them. Each CSV value is checked if it is valid date before inserting. CSVLoader.java – Utility class to load CSV into Database
package net.viralpatel.java; import java.io.FileNotFoundException; import java.io.FileReader; import java.sql.Connection; import java.sql.PreparedStatement; import java.util.Date; import org.apache.commons.lang.StringUtils; import au.com.bytecode.opencsv.CSVReader; /** * * @author viralpatel.net * */ public class CSVLoader < private static final String SQL_INSERT = "INSERT INTO $($) VALUES($)"; private static final String TABLE_REGEX = "\\$\\"; private static final String KEYS_REGEX = "\\$\\"; private static final String VALUES_REGEX = "\\$\\"; private Connection connection; private char seprator; /** * Public constructor to build CSVLoader object with * Connection details. The connection is closed on success * or failure. * @param connection */ public CSVLoader(Connection connection) < this.connection = connection; //Set default separator this.seprator = ','; > /** * Parse CSV file using OpenCSV library and load in * given database table. * @param csvFile Input CSV file * @param tableName Database table name to import data * @param truncateBeforeLoad Truncate the table before inserting * new records. * @throws Exception */ public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad) throws Exception < CSVReader csvReader = null; if(null == this.connection) < throw new Exception("Not a valid connection."); > try < csvReader = new CSVReader(new FileReader(csvFile), this.seprator); > catch (Exception e) < e.printStackTrace(); throw new Exception("Error occured while executing file. " + e.getMessage()); > String[] headerRow = csvReader.readNext(); if (null == headerRow) < throw new FileNotFoundException( "No columns defined in given CSV file." + "Please check the CSV file format."); > String questionmarks = StringUtils.repeat("?,", headerRow.length); questionmarks = (String) questionmarks.subSequence(0, questionmarks .length() - 1); String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName); query = query .replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ",")); query = query.replaceFirst(VALUES_REGEX, questionmarks); System.out.println("Query: " + query); String[] nextLine; Connection con = null; PreparedStatement ps = null; try < con = this.connection; con.setAutoCommit(false); ps = con.prepareStatement(query); if(truncateBeforeLoad) < //delete data from table before loading csv con.createStatement().execute("DELETE FROM " + tableName); > final int batchSize = 1000; int count = 0; Date date = null; while ((nextLine = csvReader.readNext()) != null) < if (null != nextLine) < int index = 1; for (String string : nextLine) < date = DateUtil.convertToDate(string); if (null != date) < ps.setDate(index++, new java.sql.Date(date .getTime())); > else < ps.setString(index++, string); >> ps.addBatch(); > if (++count % batchSize == 0) < ps.executeBatch(); >> ps.executeBatch(); // insert remaining records con.commit(); > catch (Exception e) < con.rollback(); e.printStackTrace(); throw new Exception( "Error occured while loading data from file to database." + e.getMessage()); > finally < if (null != ps) ps.close(); if (null != con) con.close(); csvReader.close(); > > public char getSeprator() < return seprator; > public void setSeprator(char seprator) < this.seprator = seprator; > > Code language: Java (java)
The class looks complicated but it is simple 🙂 The loadCSV methods combines the idea from above three tutorials and create insert queries. Following is the usage of this class if you want to use it in your project: Usage
CSVLoader loader = new CSVLoader(connection); loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true); Code language: Java (java)
Load file with semicolon as delimeter:
CSVLoader loader = new CSVLoader(connection); loader.setSeparator(';'); loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true); Code language: Java (java)
Load file without truncating the table:
CSVLoader loader = new CSVLoader(connection); loader.loadCSV("C:\\employee.csv", "TABLE_NAME", false); Code language: Java (java)
Import CSV file to MySQL table Java Example
In this example,we shall see what are the different ways we can use to insert data in the MySQL Database from a CSV File.
A CSV File is a de-limiter separated file with a comma as a de-limiter. The programme that we shall write, can work for other types of de-limiters as well with minor modifications.
The data is read line-by-line by our program. The field separator is comma( ‘,’ ) while the record separator is the new line character( \n ).
The data read, is not pushed on each record basis. Rather, we create a batch with some threshold number of records and then we push the records onto the database at once to save some network traffic.(i.e.acquire and release connection everytime.)
Instead of using the BufferedReader and other Java API directly, we use the open-csv library. The library reads the CSV File and provides us with a com.opencsv.CSVReader Object. The Reader.readNext() method in this class returns a String array for each row. This row can be iterated to extract field values and set them in the java.sql.PreparedStatement .
A sample program below extract data from a CSV File and inserts them in a table using the logic described above.
package com.javacodegeeks.examples; import java.io.FileReader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.Statement; import com.opencsv.CSVReader; public class ImportCsv < public static void main(String[] args) < readCsv(); readCsvUsingLoad(); >private static void readCsv() < try (CSVReader reader = new CSVReader(new FileReader("upload.csv"), ','); Connection connection = DBConnection.getConnection();) < String insertQuery = "Insert into txn_tbl (txn_id,txn_amount, card_number, terminal_id) values (null. )"; PreparedStatement pstmt = connection.prepareStatement(insertQuery); String[] rowData = null; int i = 0; while((rowData = reader.readNext()) != null) < for (String data : rowData) < pstmt.setString((i % 3) + 1, data); if (++i % 3 == 0) pstmt.addBatch();// add batch if (i % 30 == 0)// insert when the batch size is 10 pstmt.executeBatch(); >> System.out.println("Data Successfully Uploaded"); > catch (Exception e) < e.printStackTrace(); >> private static void readCsvUsingLoad() < try (Connection connection = DBConnection.getConnection()) < String loadQuery = "LOAD DATA LOCAL INFILE '" + "C:\\upload.csv" + "' INTO TABLE txn_tbl FIELDS TERMINATED BY ','" + " LINES TERMINATED BY '\n' (txn_amount, card_number, terminal_id) "; System.out.println(loadQuery); Statement stmt = connection.createStatement(); stmt.execute(loadQuery); >catch (Exception e) < e.printStackTrace(); >> > 254.23,123456789,12345 2854.00,987654321,87924 8724.03,598767812,56568
CREATE TABLE `txn_tbl` ( `txn_id` int(11) NOT NULL AUTO_INCREMENT , `txn_amount` double NOT NULL , `card_number` bigint(20) NOT NULL , `terminal_id` bigint(20) NULL DEFAULT NULL , PRIMARY KEY (`txn_id`) )
The readCsvUsingLoad() method provides yet another and even cleaner way to insert records in a mysql table. The Load Data command in mysql accepts the CSV/DSV file and inserts the records into the table.
The Load data is command is quite flexible as in, it accepts any delimiter specified using the FIELDS TERMINATED BY clause and LINES TERMINATED BY to mark the line termination character.
We used the ARM Blocks to avoid some boilerplate code like closing connections and other resources.
TIP:
For other databases too, such utility are available. For example, the Oracle provides the SQLLOADER utility for loading data directly from files into database tables
Summary :
Here, we tried to understand the different ways to insert records from a DSV/CSV file to the MySql database.
