Write data in CSV with Java

Question

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:

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

Answer 1

So, the csv has a header, while the mdb does not and the later one seems normal text file. The following writes both to a text file and csv file at the same time, you can customize it if there is something particular that I didnt understand.

    Path archCSV = Paths.get(System.getProperty("user.dir"), "MYLAR.csv");
        CSVParser parser = new CSVParserBuilder().withSeparator(';').build();
        File fileCsv = new File("MYLAR2.CSV");
        File fileTxt = new File("MYLAR2.txt");
        try (BufferedReader br = Files.newBufferedReader(archCSV, StandardCharsets.UTF_8);
                CSVReader reader = new CSVReaderBuilder(br).withCSVParser(parser).withSkipLines(1).build();
                FileWriter outputfile = new FileWriter(fileCsv);
                CSVWriter writer = new new CSVWriter(outputfile, ' ', ' ', ' ', ICSVWriter.DEFAULT_LINE_END)) { //mind the single space between the simgle quotes '*space*'

            // Skip HTTP Headers
            for (String line; (line = br.readLine()) != null;)
                if (line.isEmpty())
                    break; // Found end of HTTP Headers

            List<String[]> toCsv = new ArrayList<>();
            List<String[]> filas = reader.readAll(); // use this if the file is  not gigantic
            for(String[] line : filas) {
                if(line.length == 0) 
                    continue;
                
                String[] app = new String[5];
                for(int i = 0; i < line.length; i++) {
                    app[0] = line[4];
                    app[1] = line[9];
                    app[2] = line[6];
                    app[3] = "MYLAR";
                    app[4] = line[3];
                }
                toCsv.add(app);
                
            }
            
            writer.writeAll(toCsv); // only one IO Operation
        }
    }

UPDATED OUTPUT

 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  

Answer 2

For each line of file MYLAR.csv that you read, you are creating a new MYLAR2.csv file. This effectively deletes the file contents. That's why you only have one line in file MYLAR2.csv. Open file MYLAR2.csv after you open file MYLAR.csv. Also create the CSVWriter after you open file MYLAR2.csv

Try the following.

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();
    File file = new File("MYLAR2.CSV");
    try (BufferedReader br = Files.newBufferedReader(archCSV, StandardCharsets.UTF_8);
         CSVReader reader = new CSVReaderBuilder(br).withCSVParser(parser).build();
         FileWriter outputfile = new FileWriter(file);
         CSVWriter writer = new CSVWriter(outputfile)) {

        // adding header to csv
        String[] header = {"Fabricante", "ID_PRODUCT", "PRODUCTO", "REFERENCIA", "STOCK", "EAN13", "PRECIO"};
        writer.writeNext(header);

        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

            try {
                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
        }
    }
}    

UPDATE

This is what I understand from your updated question.

• The input file, MYLAR.csv, contains ten fields as follows (in the order listed).
  1. CATEGORY_PATH
  2. MANUFACTURER
  3. ID_PRODUCT
  4. PRODUCT
  5. REFERENCE
  6. REFERENCE_PARENT
  7. STOCK
  8. EAN13
  9. SHORT_DESCRIPTION
  10. PRICE_CUSTOMER
• Of those ten columns, you want to extract only the following columns (again, in the listed order)
  1. REFERENCE
  2. PRICE_CUSTOMER
  3. STOCK
  4. The literal string MYLAR
  5. PRODUCT
• The input file contains some [HTML] header lines and you want to ignore them.
• An empty line in the input file separates the header lines with the actual data.
• The first line of the data are field headers which you also want to ignore.
• You don't want any quote characters, i.e. ", in the output file.
• You want to separate the fields in the output file with a tab character.

Based on the above, here is the code. Note that I referred to the javadoc of opencsv, for example CSVParserBuilder

public static void main(String[] args) {
    CSVParserBuilder builder = new CSVParserBuilder();
    builder.withSeparator(';');
    builder.withIgnoreQuotations(true);
    builder.withIgnoreLeadingWhiteSpace(true);
    CSVParser parser = builder.build();
    CSVReader csvReader = null;
    ICSVWriter writer = null;
    Path path = Paths.get("MYLAR.csv");
    File file = new File("MYLAR2.CSV");
    String[] out = new String[5];
    try (BufferedReader br = Files.newBufferedReader(path, StandardCharsets.UTF_8);
         FileWriter outputfile = new FileWriter(file)) {
        CSVWriterBuilder writerBuilder = new CSVWriterBuilder(outputfile);
        writerBuilder.withQuoteChar('\u0000');
        writerBuilder.withSeparator('\t');
        writer = writerBuilder.build();
        CSVReaderBuilder readerBuilder = new CSVReaderBuilder(br);
        readerBuilder.withCSVParser(parser);
        csvReader = readerBuilder.build();
        String[] line = csvReader.readNext();
        boolean start = false;
        boolean first = true;
        while (line != null) {
            if (!start) {
                if (line.length == 1  &&  line[0].isEmpty()) {
                    start = true;
                }
            }
            else {
                if (first) {
                    first = false;
                }
                else {
                    out[0] = line[4];
                    out[1] = line[9];
                    out[2] = line[6];
                    out[3] = "MYLAR";
                    out[4] = line[3];
                    writer.writeNext(out);
                }
            }
            line = csvReader.readNext();
        }
    }
    catch (CsvValidationException | IOException x) {
        x.printStackTrace();
    }
    finally {
        if (csvReader != null) {
            try {
                csvReader.close();
            }
            catch (IOException xIo) {
                System.out.println("WARNING (ignored): Failed to close CSV reader.");
                xIo.printStackTrace();
            }
        }
    }
}

And here is the contents of file MYLAR2.CSV after running the above code and using the sample data from your question.

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

Answer 3

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();
…