filewriter - Read complex CSV having custom delimiter and insert into database using PreparedStatement in java - Stack Overflow

admin管理员组

文章数量:1410737

I've googled around multiple options and still clueless on which is the best way to save data from csv file into database. I tried using:

BufferedReader lineReader = new BufferedReader(new FileReader("/tmp/s_nq_acct.csv"));
while((lineText = lineReader.readLine()) != null) {
    String[] data = lineText.split("\\^");
    String user_name = data[0];
    String repository_name = data[1];
...
}

Note-> I am using '^' as a delimiter in csv.

The challenge which I faced with the above code is in one of the columns I have data in multiple lines:

Hence, the flow is stopping there and not reading further cells after this. What could fix this issue or I can change my approach and use some other API?

I've googled around multiple options and still clueless on which is the best way to save data from csv file into database. I tried using:

BufferedReader lineReader = new BufferedReader(new FileReader("/tmp/s_nq_acct.csv"));
while((lineText = lineReader.readLine()) != null) {
    String[] data = lineText.split("\\^");
    String user_name = data[0];
    String repository_name = data[1];
...
}

Note-> I am using '^' as a delimiter in csv.

The challenge which I faced with the above code is in one of the columns I have data in multiple lines:

Hence, the flow is stopping there and not reading further cells after this. What could fix this issue or I can change my approach and use some other API?

• java
• filewriter
• opencsv
• bufferedwriter

Share Improve this question Follow edited Mar 4 at 19:28 James Z 12.3k1010 gold badges2727 silver badges4747 bronze badges asked Mar 4 at 15:35 MeenalMeenal 14777 silver badges1818 bronze badges 10

• 2 As you can clearly see from the error message, your problem is around connecting properly to your Oracle db. The code and description has nothing to do with that. You need to post the correct code and solve the correct problem – g00se Commented Mar 4 at 15:47
• 2 Also don't use images for errors and logs. Put the text into the question formatted with code block format. – aled Commented Mar 4 at 15:55
• 1 @g00se: This is not the error Iam getting. I stated above this is one of the value i have to store in my database. – Meenal Commented Mar 4 at 16:01
• 1 This is not the error Iam getting. I don't understand. You've just posted an error message. That's a connection problem. And the code you've posted is pointless. All it shows is the reading and splitting of lines of text – g00se Commented Mar 4 at 16:27
• 1 My comment also applies to data or code. Don't use an image for textual information. Read How to Ask and minimal reproducible example then edit the question to make it clear. – aled Commented Mar 4 at 17:31

 |  Show 5 more comments

1 Answer 1

Sorted by: Reset to default 0

Sounds like your CSV file might be corrupt. You said

The challenge which I faced with the above code is in one of the column(s) I [have] data in multiple new lines as shown in image below

So the image you posted is a JDBC error which leads us to believe you are having a connection problem rather than what you stated. But, I think it just happens that your data contains JDBC errors in the CSV file then it agrees with what you stated. So I'm assuming this is the data in your CSV file.

The issue is that for an input file to conform to the CSV standard. Data containing new lines in them must be surrounded by double quotes ("). So you may need to modify your input file to escape the new lines in the file. For example if this is CSV file:

name^gender^birthday^comment
Rob Barker^Male^2002-01-15^I like comfort food like:
 Macaroni and Cheese
 Mashed Potatoes
 Green Bean Casserole
Rachael Huns^Female^1993-03-02^I go for Asian flavored foods like:
 Bao Buns
 Ramen Noodles
 Noodles with Peanut Sauce and Green Onions
Curries

It wouldn't conform to the CSV standard because the comment column contains new lines. Data that contains new lines within it must be escaped using double quotes ("). So to fix the file you'd need to do the following:

name^gender^birthday^comment
Rob Barker^Male^2002-01-15^"I like comfort food like:
 Macaroni and Cheese
 Mashed Potatoes
 Green Bean Casserole"
Rachael Huns^Female^1993-03-02^"I go for Asian flavored foods like:
 Bao Buns
 Ramen Noodles
 Noodles with Peanut Sauce and Green Onions
Curries"

Now the file is escaped and can be parsed by a compliant CSV parser. So my recommendation is to write another script to repair your CSV and make it compliant again. Here is the code for that:

import java.io.*;
import java.util.*;

public class CsvRepair {

    public static final int EXPECTED_COLUMNS = 4;
    
    File source;
    File dest;
    
    public CsvRepair(File source, File dest) {
        this.source = source;
        this.dest = dest;
    }
    
    public File repair() throws IOException {
        System.out.println("Repairing data to " + source.getAbsolutePath() + "=>" + dest.getAbsolutePath() );
        try(BufferedReader reader = new BufferedReader( new FileReader( source ) ); 
            PrintWriter writer = new PrintWriter( dest ) ) {
            String current = null;
            List<String> linesCache = new ArrayList<>();
            while( (current = reader.readLine()) != null ) {
                if( isValidLine( current ) ) {
                    if( !linesCache.isEmpty() ) {
                        repairLines( linesCache ).forEach( (l) -> writer.println(l) );
                        linesCache.clear();
                    }
                }
                linesCache.add( current );
            }
            if( !linesCache.isEmpty() ) {
                repairLines( linesCache ).forEach( (l) -> writer.println(l) );
                linesCache.clear();
            }
        }
        return dest;
    }
    
    public List<String> repairLines( List<String> lines ) {
        if( lines.size() > 1 ) {
            String firstLine = lines.get(0);
            String lastLine = lines.get(lines.size()-1);
            int lastColumn = firstLine.lastIndexOf("^");
            if( lastColumn >= 0 ) {
                lines.set(0, firstLine.substring(0,lastColumn) + "^\"" + firstLine.substring(lastColumn+1));
            }
            lines.set(lines.size()-1, lastLine + '"');
        }
        return lines;
    }
    
    public boolean isValidLine(String line) {
       return line.split("\\^").length == EXPECTED_COLUMNS;
    }

    public static void main(String[] args) throws IOException {
        if( args.length < 2 ) {
           System.err.println("Usage: CsvRepair <srcFile> <destFile>");
           System.exit(-1);
        }
        File source = new File( args[0] );
        File dest = new File( args[1] );
        
        System.out.println("Writing data to " + source.getAbsolutePath() );
//         try( PrintWriter w = new PrintWriter(source)) {
//             w.println("""
// name^gender^birthday^comment
// Rob Barker^Male^2002-01-15^I like comfort food like:
// Macaroni and Cheese
// Mashed Potatoes
// Green Bean Casserole
// Rachael Huns^Female^1993-03-02^I go for Asian flavored foods like:
// Bao Buns
// Ramen Noodles
// Noodles with Peanut Sauce and Green Onions
// Curries""");
//         }
        
        new CsvRepair( source, dest ).repair();
        
        // try( BufferedReader reader = new BufferedReader( new FileReader(dest))) {
        //     String line = null;
        //     while( (line = reader.readLine()) != null ) {
        //         System.out.println( line );
        //     }
        // }
   }
}

The example CSV I provided is included but commented out. You need to make sure you adjust the constant EXPECTED_COLUMNS to fit your situation. I think the rest will work as is.

Of course if you are receiving routinely corrupt files like this. You can incorporate some of the ideas from the repair program directly into your parser if you are writing it yourself. Otherwise something like OpenCSV would work only after the files are repaired.

本文标签：