CODEWITHSUNDEEP
javadatabasecsv
Raje
Raje

Reputation: 3333

Importing a CSV file in Java

I'm currently working on a program that needs to read in a CSV file in Java. Now I've got the file to load properly, that's not a problem, and I want add user information into database from csv file. however it comes out as a list of value. following is my file format

first_name1, last_name1,(group1,group2,group3)
first_name2, last_name2,(group1,group2,group3)

so want to add in database as

firstname1,lastname1,group1
firstname1,lastname1,group2
firstname1,lastname1,group1
firstname2,lastname2,group1
...

so how should i split all this and and store in database?

The file has a series of records that I want to put into a database, so if anyone has any ideas about that too, then please let me know.

Any suggestions would be much obliged.

Upvotes: 0

Views: 4304

Answers (3)

maasg
maasg

Reputation: 37435

You should look into openCSV. They support a javabeans mapping that will let you name the columns and map that into your DTO: http://opencsv.sourceforge.net/#javabean-integration One issue I see is the group nesting you're using with the parenthesis. If you have any control on the production of the file, I'd replace the separator inside '(' and ')' to be something else than a comma. That way your CSV parser can ignore those in the first pass. You will have a bean like:

firstname: John
lastname: Doe
tempGroups: (group1;group2)

You can then easily split the groups using 

String[] groups = tempGroups.split("[();]")

To put that in the DB, iterate over the collection of beans and for each bean, iterate over the collection of groups to add each individual user with its group.

for (User user:Users) {
   for  (String group:user.getGroups()) {
       db.insert(user.getFirstname(), user.getLastName(), group);
   }
}

Upvotes: 0

Bohemian
Bohemian

Reputation: 425368

There's a library called opencsv that does all the heavy lifting for you. It automatically handles issues like imbedded commas in double-quoted values, etc.

The maven dependancy definition is:

    <dependency>
        <groupId>net.sf.opencsv</groupId>
        <artifactId>opencsv</artifactId>
        <version>2.1</version>
    </dependency>

I've used it and can recommend it.

Upvotes: 0

Kristof Mols
Kristof Mols

Reputation: 3557

  • Read the file (BufferedFileReader)
  • Iterate over the lines (for-loop)
  • Break up the lines into variables (StringTokenizer)
  • Put together the query (String)

  • Execute the query on your database (SQL)

    public void test(String line) {
    List<String> list = new ArrayList<String>();
    StringTokenizer t1 = new StringTokenizer(line, ",");
    String firstName = (String) t1.nextElement();
    String lastName = (String) t1.nextElement();
    String temp = (String) t1.nextElement();
    temp = temp.replaceAll("(", "").replaceAll(")", "");
    List<String> usergroups = new ArrayList<String>();
    StringTokenizer t2 = new StringTokenizer(temp, ",");
    while (t2.hasMoreElements()) {
        String element = (String) t2.nextElement();
        usergroups.add(element);
    }
}

Upvotes: 1

Related Questions