Programmatically move to a new line within a cell in .csv file through an Android application in Java

Question

I am creating a .csv file from my Android application and storing the data in .csv format. However, when the length of a particular cell's value exceeds a certain number of characters, it leaves that cell and moves to a completely new row towards the first column. How can I display within the same cell?

Here is the code I am using to create and save data in .csv format from my android application.

public class ReportListingActivity extends AppCompatActivity {


    @Override
    protected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) {
        super.onActivityResult(requestCode, resultCode, data);
        if (resultCode != RESULT_OK)
            return;

        switch (requestCode) {
            case CREATE_FILE:
                if (data != null) {
                    Uri uri = data.getData();
                    if (uri != null) {
                        new ExportCSV(this).execute(uri);
                    }
                }
                break;
        }
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_report_listing);



    private void createFile() {
        Intent intent = new Intent(Intent.ACTION_CREATE_DOCUMENT);
        intent.addCategory(Intent.CATEGORY_OPENABLE);
        intent.setType("text/csv");
        intent.putExtra(Intent.EXTRA_TITLE, "stored_data.csv");


        startActivityForResult(intent, CREATE_FILE);
    }

    private class ExportCSV extends AsyncTask<Uri, Void, Boolean> {
        private final WeakReference<Context> context;

        ExportCSV(Context c) {
            context = new WeakReference<>(c);
        }

        @Override
        protected Boolean doInBackground(Uri... uris) {
            Uri uri = uris[0];
            Context c = context.get();


            if( c == null ) {
                return false;
            }

//updated code
StringBuilder data = new StringBuilder();
data.append("Flight Number, Departure Date, Comments");

String first_comment = "hello this is an example of the comments that I am talking about in this case. If I keep typing it will move to the next row which i do not want at all. lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum";

String second_comment = "It is long and it moves" + "\n" + "to the first column and subsequent rows";

pre_comments1 = "1) " + first_comment  + "\n";
pre_comments2 = "2) " + second_comment + "\n";

pre_comments_consolidated = pre_comments1 + pre_comments2;

data.append("\n" + flightNumber + "," + departureDate + ","+ pre_comments_consolidated);

//until here
            try {
                OutputStream os = c.getContentResolver().openOutputStream(uri);
                if(os!=null) {
                    os.write(data.toString().getBytes());
   
                    os.close();
                }
            }
            catch(Exception e) {
                e.printStackTrace();
            }
            return true;
        }
    }

}

Below is a screenshot of the output I am getting when I open the csv file in Excel

The text from Column3 moves to Column1 and subsequent rows when the text is too long or when it has new line characters (\n). How can I fix it and just display it within the same cell i.e. Column3?

The second image shows the new output when I use double quotes on my variables to append data to my csv file.

Answer 1

You need to understand the difference between

"\n" + "\"" +  flightnumber  + "\""

and

"\n" + "\"" + "flightnumber" + "\""
"\n" + "\"flightnumber\""
"\n\"flightnumber\""

For better readability, and since StringBuilder is already used to concatenate strings, here's everything on a separate line:

data.append("Flight Number");
data.append(',');
data.append("Departure Date");
data.append(',');
data.append("Comments");
data.append('\n');
// ...
data.append(flightNumber);
data.append(',');
data.append(departureDate);
data.append(',');
data.append('"' + pre_comments_consolidated + '"');

Notes:

• Put single characters in single quotes : '\n', 'a', 'b', 'c'
• You don't have to escape double quotes inside single quotes and vice versa: "'", '"'
• You can concatenate characters and strings: "example" + '\n'
• You're joining literal double quotes with variable pre_comments_consolidated.

This will not work properly if the CSV value contains quotes. You'll need to CSV-escape them. You should consider putting all that logic in a separate function.

private String escapeCsv(String in) {
    // Double double quotes that are already part of the value.
    String out = in.replace("\"", "\"\"");
    // Wrap the whole thing in double quotes.
    return '"' + out '"'
}

Use like this:

data.append(escapeCsv(pre_comments_consolidated));

Be mindful of

• which quotes you need to denote a Java string,
• which quotes you need printed literally,
• which quotes need escaping (either in Java or in a format down the line, like CSV).