How to change convert different csv files into standard format?

Question

I have 2-3 .csv files with fields like Date, Amount, Transaction Description etc and all the csv files contains these fields but in shuffled order. I want a output file to with a standard order (like if I input the sample .csv file, then I can get the things in order in output file).

I tried to do it for one file by taking substrings from the .csv file (at that time I didn't know that other files have shuffled order fields).

I am kind of new, tell me if I am asking question in a good format! Can I put a link for the sample input and output .csv file for the reference? --> https://drive.google.com/drive/folders/1-NZi5OTMTbOWXAfCTsc-ahNYm1N5DG2g (just because it would be very hard to explain that how file looks like)

What I have done?

I have just tried to extract data from the fields using the BufferReader using split but it can only work for one type of file, I cant have a standard format using this!

Sorry for posting such a long code but what I have done is selected field from the file and copied them into output file corresponding to the standard fields in the output file.

Suggest me if there is any other method with which I can proceed.

File file = new File("C:\\Users\\R\\Desktop\\CSVDemo.csv"); 
        try { 
            // create FileWriter object with file as parameter 
            FileWriter outputfile = new FileWriter(file); 

            CSVWriter writer = new CSVWriter(outputfile, ',', 
                    CSVWriter.NO_QUOTE_CHARACTER, 
                    CSVWriter.DEFAULT_ESCAPE_CHARACTER, 
                    CSVWriter.DEFAULT_LINE_END); 

            // create a List which contains String array 
            String[] header = { "Date", "Transaction Description", "Debit","Credit","Currency","CardName","Transaction","Location" }; 
            writer.writeNext(header); 

            String splitBy = ",";
            BufferedReader br = new BufferedReader(new FileReader("G:\\US\\HDFC-Input-Case1.csv"));
            String line;
            String transaction = "",name  = "";
            while ((line = br.readLine()) != null) {
                // use comma as separator
                String[] cols = line.split(splitBy);

                if(cols.length == 2 && cols[1].equals("Domestic Transactions")) {
                    transaction  = "Domestic";
                    continue;
                }
                else if(cols.length == 2 && cols[1].equals("International Transactions")) {
                    transaction  = "International";
                    continue;
                }
                else if(cols.length == 2) {
                    name = cols[1];
                    continue;
                }
                else if(cols.length<1){
                    continue;
                }
                else if(cols.length>2) {
                    if(cols[0].contains("Date")){
                        continue;
                    }
                    String[] data1 = new String[header.length];
                        data1[0] = cols[0];
                        String curr ;
                        if(cols[1].substring(cols[1].length()-3).equals("USD") || cols[1].substring(cols[1].length()-3).equals("EUR")) {
                            data1[4] = cols[1].substring(cols[1].length()-3);
                            curr = cols[1].substring(0,cols[1].length()-4);
                            data1[1] = curr;
                        }
                        else {
                            data1[4] = "INR";
                            data1[1] = cols[1];
                        }
                        
                        if(cols[2].contains("cr")){
                            data1[3] = cols[2].substring(0,cols[2].length()-2);
                            data1[2] = "0";
                        }
                        else {
                            data1[2] = cols[2];
                            data1[3] = "0";
                        }
                        
                        data1[5] = name;
                        data1[6] = transaction;
                        writer.writeNext(data1); 
                }
                System.out.println();
            } 

            // closing writer connection 
            writer.close(); 
        }

Does this answer your question? [Dealing with commas in a CSV file](https://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file) — Kitswas, Jul 25 '20 at 15:05
Why are you using a `CSVWriter` to write the CSV file, but not an equivalent reader to read the CSV file? Most of your problems would likely be simpler if you used a library that does the heavy lifting for you. — Mark Rotteveel, Jul 26 '20 at 09:12

score 0 · Answer 1 · answered Jul 25 '20 at 19:44

You can read the header of your input csv files first and find the indexes of required field in this given csv file. Once you have required indexes for every header, read those fields using indexes in the standard order you want for your output csv file. sample codes:

`CSVReader reader = new CSVReader(new FileReader(fileName ));
String[] header = reader.readNext();
List<String> list= Arrays.asList(header);
int indexOfFieldTransaction=list.indexOf("transaction");`

Now make a List and insert the field in order you want to write in output file.you will get -1 if the field you are trying to get index of is not present in the input file.

How to change convert different csv files into standard format?

1 Answers1