Parse CSV file and store values in variable

Question

I am fairly new to Java Programming. I have this requirement to Read row of Lines from CSV file (Approximately 25 Column) and load it into MySQL Data Base using JAVA.

1. The Data has to inserted 1 row at a time , as Multiple Table has to be Inserted or Updated with each row of Data.

2. I need some mechanism to Map the Header (1st Row in CSV with its corresponding values) , so that in future if i am to add another column in the CSV file , it will not affect my program.

3. Their might be fields in my CSV file which consist of "," comma as part of field value.

4. The separator being used is also "," comma.

Any suggestion on where should i start and steps to be followed. I need to perform Multiple Select statement from DB before inserting each row , as few of the Data might already be filled in related Tables and i need to query out the Item ID to insert the Main Record Table.

Lets say if i have the CSV with Four items ,

ProductID , Name  , Store Name , Country
   101    , Pencil, Evergreen , Finland

So i need to insert these Values into Product Table with Following Schema .

**Product_Table:**
+------------------+----------------------+------+-----+---------+-------+
| Field            | Type                 | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| Product_ID       | varchar(16)          | NO   | PRI | NULL    |       |
| Name             | varchar(64)          | NO   | MUL | NULL    |       |
| Store_ID         | int(10) unsigned     | NO   |     | NULL    |       |
| Country_ID       | smallint(5) unsigned | NO   |     | NULL    |       |


**Store_Table**
+------------------+----------------------+------+-----+---------+-------+
| Field            | Type                 | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| Store_ID         | varchar(16)          | NO   | PRI | NULL    |  AI   |
| Name             | varchar(64)          | NO   | MUL | NULL    |       |


**Country_Table**
+------------------+----------------------+------+-----+---------+-------+
| Field            | Type                 | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| Country_ID       | varchar(16)          | NO   | PRI | NULL    |  AI   |
| Country_Name     | varchar(64)          | NO   | MUL | NULL    |       |

So as u can see , I need to pass in Store Name and Country Name field value from CSV to get the Corresponding ID (If they exist) , and if they don't then Insert those records first to get the Corresponding ID before Inserting data into Product_Table:

The first loading of CSV will have less than 1000 Records Maximum and the the following loading will only have max 50 records any day. So time is not an issue. The logic and

I was thinking of some logic to store each value from CSV in some variable like

ProductID  = ProductID value from CSV 
ProductName = Name value from CSV

So that in future, if the order of values in CSV changes , even then i can just use these variable to Insert and query my MYSQL tables schema.

Answer 1

Use the CSV parser that comes with uniVocity-parsers to handle this for you reliably:

CsvParserSettings settings = new CsvParserSettings(); // many options here, check the tutorial.

CsvParser parser = new CsvParser(settings);
List<String[]> allRows = parser.parseAll(new FileReader(new File("path/to/file.csv")));

On the JDBC side, assuming you are using a PreparedStatement, just iterate over allRows and insert the data using statement.setObject(index + 1, row[index]) (assuming index starts at 0). For most databases - and MySQL is one of them - you don't need to convert the Strings to the type expected by the database.

Disclosure: I am the author of this library. It's open-source and free (Apache V2.0 license).