CODEWITHSUNDEEP
javacsv
herry
herry

Reputation: 1738

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

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!

Upvotes: 2

Views: 822

Answers (5)

Pekka
Pekka

Reputation: 3654

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.

Upvotes: 1

herry
herry

Reputation: 1738

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, ";");
          (...)

Upvotes: 3

Karthik
Karthik

Reputation: 1023

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

Upvotes: 3

Ed Staub
Ed Staub

Reputation: 15700

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.

Upvotes: 3

Maher Abuthraa
Maher Abuthraa

Reputation: 17834

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);
    }

Upvotes: 2

Related Questions