How do you parse a difficult .txt file?

Question

I am new to java and trying to read a very complex .txt file and inject it into my MySQL DB.

For me, the file has some very strange delimiting rules. the distinction seems to be all commas, but the other parts just don't make any sense. Here are some examples:

" "," "," "," "," "

" ",,,,,,," "

" ",0.00," "

" ",," ",," ",," "

I know that all fields containing letters will be in the usual format ,"text",.

all columns that have only numbers will follow this format: ,0.00,with the exception of the first column following the normal format"123456789",

Then everything without data will alternate between ,,or," ",

I managed to get the program to read java.sql.Statement correctly, but I need it to work with java.sql.PreparedStatement

, 100 + , , . "Some Company, LLC"

, , , .

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;


public class AccountTest {

  public static void main(String[] args) throws Exception {


        //Declare DB settings
    String dbName = "jdbc:mysql://localhost:3306/local";
    String userName = "root";
    String password = "";
    String fileName = "file.txt";
    String psQuery = "insert into accounttest"
                     + "(account,account_name,address_1,address_2,address_3) values"
                     + "(?,?,?,?,?)";
    Connection connect = null;
    PreparedStatement statement = null;
    String account = null;
    String accountName = null;
    String address1 = null;
    String address2 =null;
    String address3 = null;


        //Load JDBC Driver
    try {
        Class.forName("com.mysql.jdbc.Driver");
    }
    catch (ClassNotFoundException e) {
        System.out.println("JDBC driver not found.");
        e.printStackTrace();
        return;
    }


        //Attempt connection
    try {
    connect = DriverManager.getConnection(dbName,userName,password);
    }
    catch (SQLException e) {
        System.out.println("E1: Connection Failed.");
        e.printStackTrace();
        return;         
    }


        //Verify connection
    if (connect != null) {
        System.out.println("Connection successful.");
    }   
    else {
        System.out.println("E2: Connection Failed.");
    }


      BufferedReader bReader = new BufferedReader(new FileReader(fileName));
        String line;

        //import file into mysql DB
    try {

        //Looping the read block until all lines in the file are read.
    while ((line = bReader.readLine()) != null) {

            //Splitting the content of comma delimited file
        String data[] = line.split("\",\"");

            //Renaming array items for ease of use
        account = data[0];
        accountName = data[1];
        address1 = data[2];
        address2 = data[3];
        address3 = data[4];

            // removing double quotes so they do not get put into the db
        account = account.replaceAll("\"", "");
        accountName = accountName.replaceAll("\"", "");
        address1 = address1.replaceAll("\"", "");
        address2 = address2.replaceAll("\"", "");
        address3 = address3.replaceAll("\"", "");

            //putting data into database
        statement = connect.prepareStatement(psQuery);
        statement.setString(1, account);
        statement.setString(2, accountName);
        statement.setString(3, address1);
        statement.setString(4, address2);
        statement.setString(5, address3);
        statement.executeUpdate();
    }
    }
    catch (Exception e) {
        e.printStackTrace();
        statement = null;
    }
    finally {
        bReader.close();
    }
}   
}

, , , , , , .

: , - ? , ? , , ?

.

: , , txt MySQL, ( ) , ",", ,,,,, ,0.00, - Some Company, LLC. 100 + , 3000 6000 . . , , , .

EDIT2: , , rpc1. String data[] = line.split("\",\""); String data[] = line.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)"); , data[], statement.setString , replaceALL("\"", ""); , , . !

Answer 1

, . !

for (String line = bReader.readLine(); line != null; line = bReader.readLine()) {   

          //Splitting the content of comma delimited file
    String data[] = line.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)");

         //Iterating through the file and updating the table.
    statement = connect.prepareStatement(psQuery);
    for (int i =0; i < data.length;i++) {
        temp =  data[i];
        temp = temp.replaceAll("\"", "");
        statement.setString(i+1, temp);
    }
    statement.executeUpdate();
}

Answer 2

:

    String psQuery = "insert into accounttest"
                         + "(account,account_name,address_1,address_2,address_3,..,adrress_n) values"
                         + "(?,?,?,?,?,?,..,?)";  //you have to put m=n+2 values

.....

     //you can change separator 
            String data[] = line.replace("\",\"",";").replace("\"","").split(";");

              for(int i=0;i<m;i++)
              { 
                  if(i<data.length) //if index smaller then array siz
                      statement.setString(i+1, data[i]);
                  else
                      statement.setString(i+1, ""); //put null
              }
              statement.executeUpdate();

P.S. csv (addBatch())

Pattern p = Pattern.compile(";",""); 
p.split(st);

private static Pattern pSplit = Pattern.compile("[^,\"']+|\"([^\"]*)\"|'([^']*)'"); //set pattern as global var
private static Pattern pReplace = Pattern.compile("\"");
public static Object[] split(String st)
{
   List<String> list = new ArrayList<String>();
   Matcher m = pSplit.matcher(st);
   while (m.find())
   list.add( pReplace.matcher(m.group(0)).replaceAll("")); // Add .replace("\"", "") to remove surrounding quotes.
   return list.toArray();
}

intput string: st="\"1212\",\"LL C ,DDD \",\"CA, SPRINGFIELD\",232.11,3232.00"; 5 :

1212
LL C ,DDD
CA, SPRINGFIELD
232.11
3232.00

EDIT2

this example solves all your problems (even empty values)


private static Pattern pSplit = Pattern.compile(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)");
public static String[] split2(String st)
{
    String[] tokens = pSplit.split(st);       
    return tokens;
}