Writing/appending data to a CSV file, column wise, in JAVA

Question

I want to write/append data to a CSV file, column-by-column, in below fashion:

    query1       query2       query3
    data_item1   data_item7   data_item12
    data_item2   data_item8   data_item13
    data_item3   data_item9   data_item14
    data_item4   data_item10 
    data_item5   data_item11
    data_item6

I have the data in a hashMap, with the queryID (i.e. query1,query2) being the key and data_items for the corresponding queries being the values. The values(data_items for every query) are in a list. Therefore, my hash map looks like this :

HashMap<String,List<String>> hm = new HashMap<String,List<String>>();

How can I write this data, column by column to a csv, as demonstrated above, using JAVA ? I tried CSVWriter, but couldn't do it. Can anyone please help me out ?

1) A CSV file is a text file, and you have to write the data row-by-row, so your desire to write column-by-column can't be done. If you meant that you want to layout the data in columns, then *you* must organize the data in such fashion, and write it row-by-row. --- 2) Why would you try using a CSV**Reader** to **write** the data? Which CSV library were you trying to use? — Andreas, Feb 21 '20 at 03:39
I was using CSVWriter and I meant csvwriter, wrote "csvreader" by mistake, apologies. — Anant Vikram Singh, Feb 21 '20 at 03:50
do you need it to be csv? or would you be happy with other formats? I think storing this data as json could be a good choice — Bentaye, Feb 21 '20 at 09:55

score 0 · Answer 1 · answered Feb 21 '20 at 09:33

csv files are mostly used to persist data structured like a table... meaning data with columns and rows that are in a close context.

In your example there seems to be only a very loose connection between query1, 2 and 3, and no connection horizontally between item 1,7 and 12, or 2, 8 and 13 and so on.

On top of that writing into files are usually facilitated along rows or lines. So you open your file write one line, and then another and so on.

So to write the data columnwise as you are asking, you have to either restructure your data in your code alrady to have all the data which is written into one line available on writing that line, or run through your csv file and it's lines several times, each time adding another item to a row. Of course the latter option is very time consuming and would not make much sense.

So i would suggest if there is really no connection between the data of the 3 queries, you either write your data into 3 different csv files: query1.csv, 2.csv and 3.csv.

Or, if you have a horizontal connection i.e. between item 1,7 and 12, and so on you write it into one csv file, organizing the data into rows and columns. Something like:

queryNo     columnX     columnY     columnZ
1           item1       item2       item3
2           item7       item8       item9
3           item12      item13      item14

How to do that is well described in this thread: Java - Writing strings to a CSV file. Other examples you can also find here https://mkyong.com/java/how-to-export-data-to-csv-file-java/

score 0 · Answer 2 · answered Feb 27 '20 at 08:31

After days of tinkering around, I finally succeeded. Here is the implementation :

for(int k=0;k<maxRows;k++) {
            List<String> rowValues  = new ArrayList<String>();
            for(int i=0;i<queryIdListArr.length;i++) {
                subList = qValuesList.subList(i, i+1);
                List<String> subList2 = subList.stream().flatMap(List::stream).collect(Collectors.toList());
                if(subList2.size()<=k) {
                    rowValues.add("");
                }else{
                    rowValues.add(subList2.get(k));
                }

            }
            String[] rowValuesArr = new String[rowValues.size()];
            rowValuesArr = rowValues.toArray(rowValuesArr); 
//          System.out.println(rowValues);
            writer.writeNext(rowValuesArr);
        }

maxRows : Size of the value list with max size. I have a list of values for each key. My hash map looks like this

 HashMap<String,List<String>> hm = new HashMap<String,List<String>>();

queryIdListArr : List of all the values obtained from the hash map.

qValuesList : List of all the value lists.

List<List<String>> qValuesList = new ArrayList<List<String>>();

subList2 : sublist obtained from qValuesList using the below syntax :

qValuesList.subList(i, i+1);

rowValuesArr is an array that gets populated with the index wise value for each value fetched from qValuesList. The idea is to fetch all the values for each index from all the sublists and then write those values to the row. If for that index, no value is found, write a blank character.

Writing/appending data to a CSV file, column wise, in JAVA

2 Answers2