re-format CSV input file

Question

what I want to do is just create CSV file initially and append to it every month end. So as to generate annual CSV file in following format:

Month 1, Nokia8800, 156.0
Month 1, Nokia3120, 113.0
Month 2, Nokia8800, 16.0
Month 2, Nokia3120, 152.0
Month 3, Nokia8800, 44.0
Month 3, Nokia3120, 52.0 and so on for 12 months.

Now what i need is to re-format this CSV in following file format: Month 1,2,3,4,5,6,7,8,9,10,11,12
Nokia8800,156.0,16.0,44.0
Nokia3120, 113.0,152.0,52.0 and so on for 12 months.

How can i get this done?

thanks in advance.

*hope this can be solve with an arraylist..

Nico Huysamen · Accepted Answer · 2010-11-09 17:24:53Z

You can easily append to a file in Java with the PrintWriter class. When creating it, simply use the overloaded constructor to tell the PrintWriter to append to the file:

PrintWriter printWriter
    = new PrintWriter(new FileWriter(new File("filename"), true));

As for the second part, you can do this by reading everything into a Map and then rewriting it in the format you want.

Map<String, ArrayList<Double>> REGISTRY = new HashMap<String, ArrayList<Double>>();
REGISTRY.add("Month", new ArrayList<Double>());

Scanner scanner = new Scanner(new File("filename"));
while (scanner.hashNextLine()) {
    String[] line = scanner.nextLine().split(",");

    // for the month key
    String month = line[0].split(" ")[1];
    REGISTRY.get("Month").add(new Double(month));

    // then for the rest of the entries
    if (!REGISTRY.containsKey(line[1])) {
        REGISTRY.add(line[1], new ArrayList<Double>());
    }
    REGISTRY.get(line[1]).add(new Double(line[2]));
}

Now that everything is nice and sorted, you can easily write it out:

// first the months
printWriter2.print("Month");
for (Double d : REGISTRY.get("Month")) {
    printWriter2.print("," + d);
}
printWriter2.println();

// now the rest
for (Map.Entry<String, ArrayList<Double>> tuple : REGISTRY.entrySet()) {
    if (!tuple.getKey().equals("Month")) {
        printWriter2.print(tuple.getKey());
        for (Double d : tuple.getValue()) {
            printWriter2.print("," + d);
        }
        printWriter2.println();
    }
}

That should be it.

** I could have made a mistake, not in front of an IDE at the moment. Please let me know so I can correct it.

** Also, please note this is NOT a robust answer, but one that is very specific to the format of you input file. It has no exception handling which is a bad thing, but you can flesh it out.

Alan Geleynse · Accepted Answer · 2010-11-09 17:06:12Z

The best solution for this would be to use a List and a Map.

Since you only have 12 months, you can create a List and add 12 items to it, 1 for each month.

For each of these items, you can create a Map<String, Double> where the key is the name of the item and the double is your value for that item for that month.

When you need to output the annual report, you can then loop through each item in the first month, and pull the value for that key from each of the other maps.

It would look something like this:

List<Map<String, Double>> list = new ArrayList<Map<String, Double>>();
for(int i = 0; i < 12; i++)
    list.add(new HashMap<String, Double>());

for(Entry <String, Double> entry : list.get(0).entrySet())
{
    String row = entry.getKey() + "," + entry.getValue().toString();
    for(Map<String, Double> map : list.subList(1, list.size()))
    {
        row += map.get(entry.getKey()).toString();
    }
}

ZeissS · Accepted Answer · 2010-11-09 17:03:16Z

0

Just read it line by line, split it by the , and store it in a List>. When you have read it, just iterate over the list and only pring the first item. then do it again for the second and so on. this way you 'turn' your csv as you wished.

answered Nov 9, 2010 at 17:03

ZeissS

12.2k4 gold badges38 silver badges50 bronze badges

Collectives™ on Stack Overflow

re-format CSV input file

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related