How to read data from CSV if contains more than excepted separators?

Question

I use CsvJDBC for read data from a CSV. I get CSV from web service request, so not loaded from file. I adjust these properties:

Properties props = new java.util.Properties();
props.put("separator", ";"); // separator is a semicolon
props.put("fileExtension", ".txt"); // file extension is .txt
props.put("charset", "UTF-8"); // UTF-8

My sample1.txt contains these datas:

code;description
c01;d01
c02;d02

my sample2.txt contains these datas:

code;description
c01;d01
c02;d0;;;;;2

It is optional for me deleted headers from CSV. But not optional for me change semi-colon separator.

EDIT: My query for resultSet: SELECT * FROM myCSV

I want to read code column in sample1.txt and sample2.txt with:

resultSet.getString(1)

and read full description column with many semi-colons (d0;;;;;2). Is it possible with CsvJdbc driver or need to change driver?

Thank you any advice!

What is the SQL Query did you use with resultSet ? and what did you get when call resultSet.getString(1) ? — Maher Abuthraa, Oct 17 '13 at 09:42
and what did you get when call resultSet.getString(1)? empty string ? — Maher Abuthraa, Oct 17 '13 at 09:56
Of course not! Th first column work perfectly, because not contains many semi-colons. I have problem with second column. So how to read full `d0;;;;2` text to `description` column if the separator is semi-colon? — herry, Oct 17 '13 at 10:06
if you call index 4 ( value : 2 ), did you get that value ? I mean : resultSet.getString(4) ?= 2 — Maher Abuthraa, Oct 17 '13 at 10:10
I need full text, not only value: 2. I need `d0;;;;;2` text. — herry, Oct 17 '13 at 10:24

score 3 · Answer 1 · answered Oct 17 '13 at 17:01

3

This is a problem that occurs when you have messy, invalid input, which you need to try to interpret, that's being read by a too-high-level package that only handles clean input. A similar example is trying to read arbitrary HTML with an XML parser - close, but no cigar.

You can guess where I'm going: you need to pre-process your input.

The preprocessing may be very easy if you can make some assumptions about the data - for example, if there are guaranteed to be no quoted semi-colons in the first column.

answered Oct 17 '13 at 17:01

Ed Staub

15,480
3
61
91

I agreed with you. The pre-process will be correct solution. In my case is not supported any change in CSV. My responsible create application which only load data and don't validate and modify anything in CSV. It is political conception: if we start validate and modify data from CSV, which it get from web service, we get more unwished responsible. – herry Oct 17 '13 at 17:22
I concur. In fact, you may find that if you waste time trying to clean up the obviously invalid data yourself, that the providing web service suddenly fixes the problem a few days later - and then you realise you have wasted your time on something that was not your responsibility! – Robin Green Oct 26 '13 at 20:53

score 3 · Answer 2 · edited May 23 '17 at 11:50

3

You could try supercsv. We have implemented such a solution in our project. More on this can be found in http://supercsv.sourceforge.net/ and Using CsvBeanReader to read a CSV file with a variable number of columns

edited May 23 '17 at 11:50

Community

1
1

answered Oct 23 '13 at 19:19

Karthik

1,005
8
7

score 3 · Accepted Answer · answered Oct 25 '13 at 07:28

Finally this problem solved without a CSVJdbc or SuperCSV driver. These drivers works fine. There are possible query data form CSV file and many features content. In my case I don't need query data from CSV. Unfortunately, sometimes the description column content one or more semi-colons and which it is my separator.

First I check code in answer of @Maher Abuthraa and modified to:

private String createDescriptionFromResult(ResultSet resultSet, int columnCount) throws SQLException {

        if (columnCount > 2) {
            StringBuilder data_list = new StringBuilder();

            for (int ii = 2; ii <= columnCount; ii++) {
                data_list.append(resultSet.getString(ii));
                if (ii != columnCount)
                    data_list.append(";");
            }
            // data_list has all data from all index you are looking for ..
            return data_list.toString();
        } else {
            // use standard way
            return resultSet.getString(2);
        }
    }

The loop started from 2, because 1 column is code and only description column content many semi-colons. The CSVJdbc driver split columns by separator ; and these semi-colons disappears from columns data. So, I explicit add semi-colons to description, except the last column, because it is not relevant in my case.

This code work fine. But not solved my all problem. When I adjusted two columns in header of CSV I get error in row, which content more than two semi-colons. So I try adjust ignore of headers or add many column name (or simple ;) to a header. In superCSV ignore of headers option work fine.

My colleague opinion was: you are don't need CSV driver, because try load CSV which not would be CSV, if separator is sometimes relevant data.

I think my colleague has right and I loaded CSV data whith following code:

InputStream in = null;
try {
        in = new ByteArrayInputStream(csvData);

        List lines = IOUtils.readLines(in, "UTF-8");
        Iterator it = lines.iterator();
        String line = "";
        while (it.hasNext()) {
        line = (String) it.next();

        String description = null;
        String code = null;
        String[] columns = line.split(";");

        if (columns.length >= 2) {
            code = columns[0];
            String[] dest = new String[columns.length - 1];
            System.arraycopy(columns, 1, dest, 0, columns.length - 1);
            description = org.apache.commons.lang.StringUtils.join(dest, ";");
          (...)

Maher Abuthraa · Answer 4 · 2013-10-17T16:45:44.310

2

ok.. my solution to go and read all fields if columns are more than 2 ... like:

int ccc = meta.getColumnCount();
    if (ccc > 2) {
        ArrayList<String> data_list = new ArrayList<String>();
        for (int ii = 1; ii < ccc; ii++) {
            data_list.add(resultSet.getString(i));
        }
        //data_list has all data from all index you are looking for ..
    } else {
        //use standard way 
        resultSet.getString(1);
    }

edited Oct 17 '13 at 16:45

answered Oct 17 '13 at 10:43

Maher Abuthraa

17,493
11
81
103

1

That's not what OP needs - second column logically contains d0;;;;;2 – Ed Staub Oct 17 '13 at 16:50
@EdStaub, you're right. Unfortunately, this code doesn't load semi-colons. – herry Oct 17 '13 at 17:53

Pekka · Answer 5 · 2013-11-06T08:16:00.560

If the table is defined to have as many columns as there could be semi-colons in the source, ignoring the initial column definitions, then the excess semi-colons would be consumed by the database driver automatically.

The most likely reason for them to appear in the final column is because the parser returns the balance of the row to the terminator in the field.

Simply increasing the number of columns in the table to match the maximum possible in the input will avoid the need for custom parsing in the program. Try:

code;description;dummy1;dummy2;dummy3;dummy4;dummy5
c01;d01
c02;d0;;;;;2

Then, the additional ';' delimiters will be consumed by the parser correctly.

How to read data from CSV if contains more than excepted separators?

5 Answers5