handling of different delimiters of a csv file

Question

I am new to Java and know the basics by now. I have a csv file which lines are all of the following structure:

Int,,text,text,Int,text,text,text,,text,text,,text,text,,,text,,text,,,Int,Int

I was very confused when I saw that csv file since it is separated by single commas, double commas and triple commas. Sometimes a specific text or int is also empty and excel can´t handle to display the csv in the correct way any more.

So I thought I use Java to write a program to make the columns separated by only one comma. And save the result in a new csv file afterwards. (I haven´t implemented how to write it in another file) With some research I managed to write a File Reader to read the csv file but that´s it. How can I come to my desired result?

What I have done so far:

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;

class Read {
    public static void main(String[] args) {

        FileReader myFile = null;
        BufferedReader buff = null;
        final ArrayList<String> lines = new ArrayList<String>();

        try {
            myFile = new FileReader("thisisthepathofthecsvsource");
            buff = new BufferedReader(myFile);
            String line;
            while ((line = buff.readLine()) != null) {

                lines.add(line);

            }
        } catch (IOException e) {
            System.err.println("Error2 :" + e);
        } finally {
            try {
                buff.close();
                myFile.close();
            } catch (IOException e) {
                System.err.println("Error2 :" + e);
            }
        }

        final String[][] valuesArray = new String[lines.size()][];
        int cnt = 0;
        for (final String line : lines) {
            valuesArray[cnt++] = line.split(",");
        }

        for (String[] arr : valuesArray) {

            System.out.println(Arrays.toString(arr));
        }
    }

}

Are you wanting to remove the blank fields? As far as writing your data back out, look into PrintWriter or this SO thread http://stackoverflow.com/questions/2885173/java-how-to-create-a-file-and-write-to-a-file — Shar1er80, May 14 '15 at 23:21
Ok, thank you very much for your answers. Now I have the lines separated by only one comma. That is good so far. But now I want to fill maybe the character '-' to the columns which are empty. Actually I want the lines to be equal to the structure of all the other lines so that I can easily import it into excel for example and filter it by using filters. I am sorry it sounds easy but it takes me so long because I am new to java :(. I keep trying! — dontknowguy, May 17 '15 at 12:39
Your sample csv line indicates that the line has 23 columns, some columns which are empty (that's the double comma or triple comma). Are all the csv lines like this, that they have 23 columns? — Shar1er80, May 18 '15 at 15:05

score 1 · Answer 1 · answered May 17 '15 at 15:38

Try the open source library uniVocity-parsers, which provides the solution of columns separator as following:

CsvParserSettings settings = new CsvParserSettings();
settings.setSkipEmptyLines(true);
settings.getFormat().setLineSeparator("\n");
settings.getFormat().setQuote(',');       
settings.getFormat().setQuoteEscape('\\');  // escape the double backslash

score 0 · Answer 2 · answered May 14 '15 at 22:53

0

you can do that in your while

String [] dataArr =  line.split(",") ; 
for(String str : dataArr){
     if(str == null || str.equlas("")) continue; 
     System.out.println(str) ; 
}

this will help you to get the comma separator file data.

answered May 14 '15 at 22:53

Alaa Abuzaghleh

1,023
6
11

score 0 · Answer 3 · answered May 14 '15 at 23:19

You want to replace one or more commas with one, so why not use a regex replace instead?

 String fileContent = "file,content,,test";
 fileContent = fileContent.replaceAll(",+", ",");

This will replace one or more comma with one comma and therefor should remove all duplicates.

handling of different delimiters of a csv file

3 Answers3