How to write into csv sheet with below mentioned specific format

Question

We need to get below format

Redemption Reference Code|Status|Delivery company|Shipper Tracking Number|Comments
2006995040|Shipped|USPS|ABCD12345|Order SHIPPED
2006995042|Cancelled|||INVALID Address
2006995048|Ordered|USPS|ABCD12345|Order SHIPPED

I am using below code

private void accumulateOrdersFromPlacement(){
        int count = 0;
        for (int i = 0; i < orderIds.size(); i++) {


            if (count == 0) {
                outPutLineData.add(orderIds.get(i));
                outPutLineData.add("Cancelled");
                outPutLineData.add("");
                outPutLineData.add(" ");
               outPutLineData.add(" ");
                cancelledStatusLineItems.add(orderIds.get(i));
                count++;
            } else if (count == 1) {
                outPutLineData.add(orderIds.get(i));
                outPutLineData.add("Shipped");
                if (outPutLineData.contains("Shipped")) {
                    outPutLineData.add("USPS");
                    outPutLineData.add("order SHIPPED");
                    outPutLineData.add("");
                }
                shippedStatusLineItems.add(orderIds.get(i));
                count++;
            } else if (count == 2) {
                outPutLineData.add(orderIds.get(i));
                outPutLineData.add("No Longer Available");
                outPutLineData.add("");
                outPutLineData.add(" ");
                outPutLineData.add(" ");
                count++;
                nlaStatusLineItems.add(orderIds.get(i));
            } else if (count == 3) {
                outPutLineData.add(orderIds.get(i));
                outPutLineData.add("Ordered");
                outPutLineData.add("");
                outPutLineData.add(" ");
                outPutLineData.add(" ");
                orderedStatusLineItems.add(orderIds.get(i));
                count = 0;
            }
        }

I am using below code for file creation. This is the detailed coding . This has more readability to understand code.Here i got confused about the code.We are taking order id count andbased on that this code is working.

private File createFile(final File directory) {
    FileWriter fw = null;
    File tempFile = null;
    try {
        directory.mkdir();

       tempFile = new File(".//FidelityFulfillment//" + generateFileName(countyThreeLetterCode, "FidelityFulfillment", ".csv", date));
        logReport(GenericConstants.NEW_LINE + "Fulfillment file creating:", tempFile.getName());
        fw = new FileWriter(tempFile, true);
        try (BufferedWriter writer  = new BufferedWriter(new FileWriter(tempFile, true))) {
            writer.write(generateHeaderLine());
            writer.newLine();


           for (int y = 1; y < outPutLineData.size(); y++) {

                if (y % 5 < 4) {  

                     writer.write(outPutLineData.get(y-1) + fieldSeperator);
                     logReport(outPutLineData.get(y - 1) + fieldSeperator);
                }
                else {

                     writer.write(outPutLineData.get(y-1));
                    logReport(outPutLineData.get(y));
                }
                 if (y % 5 == 0) {
                     writer.newLine();
                     logReport("newline");
                 }  
            }



            writer.close();
        } catch (final IOException e) {
            e.printStackTrace();
            final String err = "Unable to write file due to : " + e;
            logReport(GenericConstants.NEW_LINE + "Unable to create temp local File");
        } finally {
            fw.close();
        }
    }catch (Exception e){
        e.printStackTrace();
    }
    return tempFile;
}

Getting response as

Redemption Reference Code|Status|Delivery company|ShipperTrackingNumber|Comments
2006964032|Cancelled|| |
newline
2006964034|Shipped|USPS||
newline
2006964036|No Longer Available||

Last line one pipline is getting missing

You should use a CSV API instead of writing your own CSV format. — AxelH, May 23 '18 at 08:47
Well, I see a comment field ... how are you gonna manage a comment `"That hold a | character`" ? — AxelH, May 23 '18 at 08:49
In this code last order id has always one pipline is missing — Soorya Thomas, May 23 '18 at 08:50
You don't get it. If the comment value contains your separator, you will need to escape it or use quotes, which an API do perfectly. And for your problem. You don't loop on every items — AxelH, May 23 '18 at 08:51
follow this link https://stackoverflow.com/questions/50423750/reading-a-csv-file-the-system-cannot-find-the-file-specified/50423878?noredirect=1#comment87863925_50423878 @Ryan Jones — Ng Sharma, May 23 '18 at 08:52
@NgSharma; what code fix i can give it for this to work perfecltly? — Soorya Thomas, May 23 '18 at 08:54

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

First, you loop in a strange way :

for (int y = 1; y < outPutLineData.size(); y++) {

In general, we start at 0.

But you tried to correct that with the condition :

if (y % 5 < 4) {  
    //System.out.print("size:"+y);
    writer.write(outPutLineData.get(y-1) + fieldSeperator);
    logReport(outPutLineData.get(y - 1) + fieldSeperator);
}
else {
    //System.out.print("size noseperator:"+y);
    writer.write(outPutLineData.get(y-1));
    logReport(outPutLineData.get(y));
}

Instead, simply use an iterator to read the values, then on read the correct amount of values :

Iterator<String> it = outPutLineData.iterator();
while(it.hasNext()){
    for (int j = 0; j < columnCount; ++j) {
        writer.write(it.next());
        if( j < columnCount - 1)
            writer.write(fieldSeperator);
    }
    writer.newLine();
}

Example with a StringBuilder to print in console :

int columnCount = 2;
String fieldSeperator = "|";

List<String> list = Arrays.asList("foo", "1", "bar", "2");
Iterator<String> it = list.iterator();

//Safe guard !
if(list.size() % columnCount != 0)
    throw new RuntimeException("The list does have the correct amount of data");

    while(it.hasNext()){
    for (int j = 0; j < columnCount; ++j) {
        sb.append( it.next() );
        if( j < columnCount - 1)
            sb.append(fieldSeperator );
    }
    System.out.println(sb.toString());
    sb.setLength(0);
}

foo|1
bar|2

Use a POJO

You are using a List<String> to hold the values, you need to know how many column you need to read to get the value. Instead, use a POJO :

public class Redemption{
    String redemptionReference;
    String code;
    String status;
    String deliveryCompany;
    String shipperTrackingNumber;
    String comments;
}

And create the instance in your first loop :

List<Redemption> list...

That way, you just need to iterate each instance to build your row :

for(Redemption r: list){
    writer.write(r.getRedemptionReference() + fieldSeperator);
    ...
    writer.write(r.getComments());
    writer.newLine();
}

Of course, you need to use getter and setter but this is just to show what you should do.

CSV API

Big warning, writing your own CSV writer is dangerous. If you have a comment like "This is a | character". You will end up with a line like :

2006995040|Shipped|USPS|This is a | character|Order SHIPPED

That one column to many... because you should have

2006995040|Shipped|USPS|"This is a | character"|Order SHIPPED

But you are not checking that case, and this is only one case. Using a CSV API is safer and simpler.

See Any good library to read and write csv files?

After the loop ? What you have is a first loop to iterate the item, in it you build a list that you iterate to create the row. So after that loop, simply add the line. — AxelH, May 23 '18 at 09:19
if i using above code i will get response as 006964032| Cancelled| | | | 2006964034| Shipped| USPS| order SHIPPED| | 2006964036| No Longer Available| | | newline — Soorya Thomas, May 23 '18 at 09:29
@RyanJones My bad, the identation made me miss that. You use a list to put every value then want to "split" the block in rows. Why don't use have a POJO ? I have edited the logic you have using an iterator to be more "readable" but also show you an alternative using correct OOP. — AxelH, May 23 '18 at 09:36
I have edited me create file code. That was great idea to using pojo. But still i was thinking what solution, i can give it for this code. — Soorya Thomas, May 23 '18 at 09:53

How to write into csv sheet with below mentioned specific format

1 Answers1

Use a POJO

CSV API