Question

Source: https://stackoverflow.com/questions/64697026/write-data-in-csv-with-java

I'm trying to write data in a new CSV generated automatically after reading and extracting my data. However, always write one record only and it hasn't got the format that I want. I want the data in columns, I want that if I have id, manufacturer, product_name, price, information should be in different columns but my result is:

Fabricante,"ID_PRODUCT","PRODUCTO","REFERENCIA","STOCK","EAN13","PRECIO"

,"MANUFACTURER","ID_PRODUCT","PRODUCT","REFERENCE","REFERENCE_PARENT","STOCK","EAN13","SHORT_DESCRIPTION","PRICE_CUSTOMER"

,"EPSON","1","IMPRESORA EPSON LQ-2190","C11CA92001","","0","8715946459172","000003","910.33"

Nevertheless in other script MDB developed for other person, when transform this CSV his format is:

51645A-C 11.86 21 INFOWORK CARTUCHO COMPATIBLE CON HP 45 51645A NEGRO

http://recursos.infowork.es/img/000/084/000084322.jpg

C13T04014020-C 1.01 3 INFOWORK CARTUCHO COMPATIBLE CON EPSON C62-CX3200 NEGRO http://recursos.infowork.es/img/000/084/000084593.jpg

I need this format with Java, to have my process automatically update my DB. Why? Now, I'm using one script for transformation and then I need to re-save the result.

My actual code is:

public static void main(String[] args) throws FileNotFoundException, IOException, CsvValidationException, CsvException {

Path archCSV = Paths.get(System.getProperty("user.dir"), "MYLAR.csv");

CSVParser parser = new CSVParserBuilder().withSeparator(';').build();

try (BufferedReader br = Files.newBufferedReader(archCSV, StandardCharsets.UTF_8);

CSVReader reader = new CSVReaderBuilder(br).withCSVParser(parser).build()) {

String col1 = "";

String col2 = "";

String col3 = "";

String col4 = "";

String col5 = "";

String col6 = "";

String col7 = "";

String col8 = "";

// Skip HTTP Headers

for (String line; (line = br.readLine()) != null; )

if (line.isEmpty())

break; // Found end of HTTP Headers

String[] fila = null;

while((fila = reader.readNext()) != null) {

col1 = fila[0]; //path

col2 = fila[1]; //fabricante

col3 = fila[2]; //ID_PRODUCT

col4 = fila[3]; // producto

col5 = fila[4]; //referencia

col6 = fila[6]; //stock

col7 = fila[7]; //EAN13

col8 = fila[9]; //PRECIO

File file = new File("MYLAR2.CSV");

try {

// create FileWriter object with file as parameter

FileWriter outputfile = new FileWriter(file);

// create CSVWriter object filewriter object as parameter

CSVWriter writer = new CSVWriter(outputfile);

// adding header to csv

String[] header = { "Fabricante", "ID_PRODUCT", "PRODUCTO", "REFERENCIA", "STOCK", "EAN13", "PRECIO"};

writer.writeNext(header);

String[] data1 = new String[fila.length];

// add data to csv

for(int i = 1; i<fila.length; i++){

data1[i] = fila[i];

}

writer.writeNext(data1);

/*String[] data2 = { "Suraj", "10", "630"};

writer.writeNext(data2); */

// closing writer connection

writer.close();

}

catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

// code here

}

}

}

And result for transformation I wrote has only one result, when CSV has many results.

UPDATE

Original File Mylar

HTTP/1.1 200 OK

Date: Wed, 04 Nov 2020 10:33:57 GMT

Server: Apache/2.4.18 (Unix) OpenSSL/1.0.2g PHP/5.6.19 mod_perl/2.0.8-dev Perl/v5.16.3

Vary: Host

X-Powered-By: PHP/5.6.19

Pragma: public

Last-Modified: Wed, 04 Nov 2020 10:35:38 GMT

Cache-Control: no-store, no-cache, must-revalidate

Cache-Control: pre-check=0, post-check=0, max-age=0

Cache-Control: private

Content-Transfer-Encoding: none

Content-Disposition: attachment; filename="tarifa_general.csv"

Content-Length: 1682234

Set-Cookie: 6bc890cb7b5704cff62e4f70e6f2fe91=xjqG7DAXpIyCS%2FBGEFwnApzYUWiwlkMqmx9ULY%2FdgW%2FMqfHk4mNo67rgSQp10%2FacRlHCinJUyWAv0B%2FInmAGaiyJ0ro5yf2yNl1O9tKx50M%3D000074; expires=Tue, 24-Nov-2020 10:33:57 GMT; Max-Age=1727899; path=/; domain=www.mylar.es; httponly

Content-Type: text/csv; charset=utf-8

CATEGORY_PATH;MANUFACTURER;ID_PRODUCT;PRODUCT;REFERENCE;REFERENCE_PARENT;STOCK;EAN13;SHORT_DESCRIPTION;PRICE_CUSTOMER

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;1;"IMPRESORA EPSON LQ-2190";C11CA92001;;0;8715946459172;000003;910.33

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;3;"IMPRESORA EPSON LX-1170 II";C11C641001;;0;8715946316963;000008;428.91

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;4;"IMPRESORA EPSON FX-890 II";C11C524025;;0;8715946333137;000012;595.29

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;5;"IMPRESORA EPSON FX-2190";C11C526022;;0;8715946204420;000013;714.87

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;6;"IMPRESORA EPSON LQ-680 PRO";C11C376125;;0;8715946333205;000021;664.98

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;8;"IMPRESORA EPSON LQ-630";C11C480019;;0;8715946538068;000025;498.67

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;11;"IMPRESORA EPSON LQ-690";C11CA13041;;0;8715946429939;000091;583.8

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;12;"IMPRESORA EPSON LX-350";C11CC24031;;0;8715946502939;000092;171.23

"PERIFÉRICOS > SCANNERS > EPSON";EPSON;17;"SCANNER EPSON WORKFORCE DS-7500N";B11B205331BT;;0;8715946510705;000157;1063.95

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > ACCESORIOS";EPSON;18;"TRACTOR PUSH/PULL (FX-2190) C12C800212*";C12C800212;;0;0;000230;59.54

"PERIFÉRICOS > TERMINAL PUNTO VENTA (TPV) > IMPRESORAS TICKETS";EPSON;19;"IMPRESORA EPSON TMU-950 PARALELO BLANCA";C31C176252LG;;0;8715946367842;000248;512.8

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > LASER";EPSON;21;"IMPRESORA EPSON ACULASER COLOR C2800N";C11CA09001BZ;;0;8715946403878;000251;294.51

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > LASER";EPSON;22;"IMPRESORA EPSON EPL-6200N";C11C533011BR;;0;9314020599675;000254;168.41

"PERIFÉRICOS > SCANNERS > EPSON";EPSON;24;"SCANNER EPSON PERFECTION V500 OFFICE ADF";B11B189081;;0;8715946448664;000257;194.32

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > LASER";EPSON;25;"IMPRESORA EPSON ACULASER M 7000N";C11CB61011;;0;8715946311173;000264;0

"PERIFÉRICOS > SCANNERS > EPSON";EPSON;26;"SCANNER EPSON GT-S55";B11B209301BT;;0;8715946490922;000266;381.92

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > EPSON > MATRICIALES";EPSON;51;"IMPRESORA EPSON LQ-350";C11CC25001;;0;8715946521886;000854;188.01

"CONSUMIBLES > ALMACENAMIENTO > MEMORIAS USB / PENDRIVES";KINGSTON;68;"PENDRIVE KINGSTON 32GB USB 2.0";32GB;;0;740617297737;001439;3.29

"CONSUMIBLES > ALMACENAMIENTO > MEMORIAS USB / PENDRIVES";;77;"PENDRIVE INTEGRAL 64GB* USB 2.0";"INFD64GBPASBLS PEN64GB";;0;5055288427822;001460;7.34

"CONSUMIBLES > ALMACENAMIENTO > MEMORIAS USB / PENDRIVES";TOSHIBA;79;"PENDRIVE TOSHIBA 16GB USB 2.0";"THN U202";;0;4047999400226;001498;3.91

"PERIFÉRICOS > IMPRESORAS / FAX > IMPRESORAS > BROTHER > ACCESORIOS";BROTHER;81;"DISCO DURO BROTHER 10 GB PARA HL4000CN";HD40CL;;0;4977766603645;001677;411.4

the result with script to other developed with mdb script is:

C11CA92001 910.33 0 MYLAR IMPRESORA EPSON LQ-2190

C11C641001 428.91 0 MYLAR IMPRESORA EPSON LX-1170 II

C11C524025 595.29 0 MYLAR IMPRESORA EPSON FX-890 II

C11C526022 714.87 0 MYLAR IMPRESORA EPSON FX-2190

C11C376125 664.98 0 MYLAR IMPRESORA EPSON LQ-680 PRO

C11C480019 498.67 0 MYLAR IMPRESORA EPSON LQ-630

C11CA13041 583.8 0 MYLAR IMPRESORA EPSON LQ-690

C11CC24031 171.23 0 MYLAR IMPRESORA EPSON LX-350

B11B205331BT 1063.95 0 MYLAR SCANNER EPSON WORKFORCE DS-7500N

C12C800212 59.54 0 MYLAR TRACTOR PUSH/PULL (FX-2190) C12C800212*

C31C176252LG 512.8 0 MYLAR IMPRESORA EPSON TMU-950 PARALELO BLANCA

C11CA09001BZ 294.51 0 MYLAR IMPRESORA EPSON ACULASER COLOR C2800N

C11C533011BR 168.41 0 MYLAR IMPRESORA EPSON EPL-6200N

B11B189081 194.32 0 MYLAR SCANNER EPSON PERFECTION V500 OFFICE ADF

C11CB61011 0 0 MYLAR IMPRESORA EPSON ACULASER M 7000N

B11B209301BT 381.92 0 MYLAR SCANNER EPSON GT-S55

C11CC25001 188.01 0 MYLAR IMPRESORA EPSON LQ-350

32GB 3.29 0 MYLAR PENDRIVE KINGSTON 32GB USB 2.0

INFD64GBPASBLS PEN64GB 7.34 0 MYLAR PENDRIVE INTEGRAL 64GB* USB 2.0

THN U202 3.91 0 MYLAR PENDRIVE TOSHIBA 16GB USB 2.0

HD40CL 411.4 0 MYLAR DISCO DURO BROTHER 10 GB PARA HL4000CN

FAX2845 159.2 0 MYLAR FAX BROTHER 2845 LASER CON AURICULAR

I need this format (up)

With my actual code result is:

Fabricante,"ID_PRODUCT","PRODUCTO","REFERENCIA","STOCK","EAN13","PRECIO"

,"MANUFACTURER","ID_PRODUCT","PRODUCT","REFERENCE","REFERENCE_PARENT","STOCK","EAN13","SHORT_DESCRIPTION","PRICE_CUSTOMER"

,"EPSON","1","IMPRESORA EPSON LQ-2190","C11CA92001","","0","8715946459172","000003","910.33"

,"EPSON","3","IMPRESORA EPSON LX-1170 II","C11C641001","","0","8715946316963","000008","428.91"

,"EPSON","4","IMPRESORA EPSON FX-890 II","C11C524025","","0","8715946333137","000012","595.29"

,"EPSON","5","IMPRESORA EPSON FX-2190","C11C526022","","0","8715946204420","000013","714.87"

,"EPSON","6","IMPRESORA EPSON LQ-680 PRO","C11C376125","","0","8715946333205","000021","664.98"

,"EPSON","8","IMPRESORA EPSON LQ-630","C11C480019","","0","8715946538068","000025","498.67"

,"EPSON","11","IMPRESORA EPSON LQ-690","C11CA13041","","0","8715946429939","000091","583.8"

,"EPSON","12","IMPRESORA EPSON LX-350","C11CC24031","","0","8715946502939","000092","171.23"

Explanation

I can´t pass the complete CSV file of a company distributor, I have passed some example lines that with that should give an idea of what the whole file is like. Anyway, I'll try to make it clearer ... The first example after the update is my original mylar file. There was a former developer who made a script with MDB that transformed it as seen in the first example, which is the original from the distributor to the second example ... and finally with my current java script I get the third example. I need to put myself as the second example. I have terms in Spanish and English because this is how the CSVs of the providers come ... What I need to do is an automatic update of a database, with these CSVs, but for this, I have to transform them previously obtaining the data that is there in my script, but keep the CSV as in the example of the MDB script.

Answer

Just skip the first 16 rows of the current CSV file, get the desired column data, and export result as a non-header CSV file. The code will be rather long if you use Java to express the process.

But, You can use SPL, the open-source Java package, to do this. It is easy and only one line of code is enough:

	A
1	>file("MYLAR2.csv").export@c(file("MYLAR.csv";"UTF-8").read@n().m(17:).concat@n().import@qt(REFERENCE,PRICE_CUSTOMER,STOCK,PRODUCT;";").new(REFERENCE,PRICE_CUSTOMER,STOCK,"MYLAR":MYLAR,PRODUCT))

 

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as export.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 export()");
st.execute();

…

View SPL source code.