Question

Source:https://stackoverflow.com/questions/65033185/how-to-capture-the-field-values-in-the-csv-file-using-bufferedreader

The csv file looks like this:

\$\$NAME$$ JOHN

\$\$STATE$$ CA

\$\$CITY$$ SF

\$\$REGION$$ XYZ

\$\$WEATHER$$

\$\$AGE$$ 25

\$\$GROUP$$ CATEGORY

\$\$TIME$$

5

5

5

I'm trying to get field values like name, it is the value after \$\$NAME$$ (there is a space after the identifier). How do I store the value for each field by using BufferedReader in Java? The fields could be in any line number and not in a fixed place or format, and also throw out an error if there is any special characters or null value is encountered.

int n = 100; // Max lines

String line;

try (BufferedReader br = new BufferedReader(new FileReader(str)))

{

while ((line = br.readLine()) != null && i++ < n)

{

br.readLine();

line = br.readLine();

System.out.println(line);

}

}

Once the values are extracted from the CSV file, I need to store them in a string variable and use it later to insert into the database for each column values.

Case 2: Also, for the last field \$\$GROUP$$ CATEGORY the value is "5" in cell 9 to 11 and I need to match that the column CATEGORY in the database has to be 5 stored in a string to be inserted into the database column of the same name. The regex won’t find the exact match when I used line.matches condition.

Answer

You need to extract data from a CSV file according to the format of “\$\$field name$$ field value”. The code will be rather long if you try to do this in Java.

Try using SPL, the open-source Java package, to get it done. It is easy with only two lines of code:

	A
1	=file("data.csv").read@n().select(left(~,2)=="\$\$").(right(~,-2).split("\$\$"))
2	=create(${A1.(~(1)).concat@c()}).record(A1.(~(2)))

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as csv2tbl.splx and invoke it in Java as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st = con.prepareCall("call csv2tbl()");
st.execute();

…

View SPL source code.