0

I'm trying to import data from csv to database. Below is my code. The problem is while importing some files it shows below error and other gets loaded successfully. 

java.sql.SQLException: syntax error, unexpected STYLE_, expecting identifier_chain2 or OFFSET_ [line 1, column 303] (Session: 1596182455914086215)
at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:164)
at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:21)
at com.exasol.jdbc.AbstractEXAPreparedStatement.<init>(AbstractEXAPreparedStatement.java:62)
at com.exasol.jdbc.AbstractEXAPreparedStatement_14.<init>(AbstractEXAPreparedStatement_14.java:14)
at com.exasol.jdbc.EXAPreparedStatement.<init>(EXAPreparedStatement.java:12)
at com.exasol.jdbc.DialectGeneric.createPreparedStatement(DialectGeneric.java:10)
at com.exasol.jdbc.AbstractEXAConnection.prepareStatement(AbstractEXAConnection.java:578)

Code:

import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import com.opencsv.CSVReader;

public class ImportCsv
{
    public static void main(String[] args)
    {
            readCsv();
            readCsvUsingLoad();
    }

    private static void readCsv()
    {

        try (CSVReader reader = new CSVReader(new FileReader("upload.csv"), ','); 
                     Connection connection = DBConnection.getConnection();)
        {
                String insertQuery = "Insert into txn_tbl (txn_id,txn_amount, card_number, terminal_id) values (null,?,?,?)";
                PreparedStatement pstmt = connection.prepareStatement(insertQuery);
                String[] rowData = null;
                int i = 0;
                while((rowData = reader.readNext()) != null)
                {
                    for (String data : rowData)
                    {
                            pstmt.setString((i % 3) + 1, data);

                            if (++i % 3 == 0)
                                    pstmt.addBatch();// add batch

                            if (i % 30 == 0)// insert when the batch size is 10
                                    pstmt.executeBatch();
                    }
                }
                System.out.println("Data Successfully Uploaded");
        }
        catch (Exception e)
        {
                e.printStackTrace();
        }

    }

    private static void readCsvUsingLoad()
    {
        try (Connection connection = DBConnection.getConnection())
        {

                String loadQuery = "LOAD DATA LOCAL INFILE '" + "C:\\upload.csv" + "' INTO TABLE txn_tbl FIELDS TERMINATED BY ','" + " LINES TERMINATED BY '\n' (txn_amount, card_number, terminal_id) ";
                System.out.println(loadQuery);
                Statement stmt = connection.createStatement();
                stmt.execute(loadQuery);
        }
        catch (Exception e)
        {
                e.printStackTrace();
        }
    }

}

May I know please what this error trying to say ? It doesn't seems to me data problem because when I compare with other files who gets loaded successfully there is no difference.

Any suggestion please ? For this error what needs to be looked ?

Squeez
  • 343
  • 2
  • 3
  • 15
  • 1
    Complains about syntax, this mean some of your data might have ',' in it and breaks insert statement. may be 'txn_amount' causing it. – smile Mar 29 '18 at 03:04
  • @sky_light, Ah good point..! Lemme check man..will update you...! – Squeez Mar 29 '18 at 03:12
  • @sky_light, I have tried replacing all "," but still same error. And the crazy thing is even if I try same file with no records but metadata, it shows same error...! Any clue...? – Squeez Mar 29 '18 at 03:38
  • May be it is creating sql statement form metadata, because you said no record,check if code fail after 'while((rowData = reader.readNext()) != null)' or before. Also what will happen if you remove 'txn_id' and 'null' from statement – smile Mar 29 '18 at 03:48
  • @sky_light, But it shows same error, with "syntax error, unexpected STYLE_, expecting identifier_chain2 or OFFSET_ " and there is no "," in metadata as well...! – Squeez Mar 29 '18 at 03:51
  • I will say put System.out.println () in every line and see where it fails and what data cause to fail. – smile Mar 29 '18 at 04:03

0 Answers0