Write data in CSV with Java
问题
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 that the data in columns, I want that if I have id, manufacturer, product_name, price, this information should be in diferent 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 after I need to re-save 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
解答
跳过目前csv 文件的前 16 行，取需要的列数据，结果导出为无表头的 csv 文件。用 Java 实现则代码较长。
用Java 下的开源包 SPL 很容易写，只要 1 句：
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 提供了 JDBC 供 Java 调用，把上面的脚本存为 export.splx，在 Java 中以存储过程的方式调用脚本文件：
…
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st = con.prepareCall("call export()");
st.execute();
…
English version