Multiple file read, then write

[KirkPatrick]

Alright I am having my program read two text files (in csv format) and once it reads them, I am wanting it to do a check to see if two columns match, if they do I want it to write it to a new file.

I have coded it, but am having problems getting it to function properly. I have set up print statements throughout the code (taken them out in the code I am posting so you don't get thrown off) and fixed some small issues here and there but still can't quite seem to find why its not working.

public class BeanInfoMerge {

    private String itemA = "";
    private String itemB = "";
    private String itemC = "";
    private String dataA = "";
    private String dataB = "";
    private String dataC = "";


    public BeanInfoMerge() throws FileNotFoundException {

        BufferedReader reader = new BufferedReader(new FileReader("C:\\file1.csv"));

        ArrayList<String> list1 = new ArrayList<String>();

        try {
            boolean bHeadersDone = false;
            while (reader.ready()) {
                String headerInfo = reader.readLine();

                if (!bHeadersDone) {
                    if (headerInfo.contains("Option 3")) {
                        bHeadersDone = true;
                    }
                }
                else {
                    String[] info = list1Info.split("," , 3);
                    itemA = info[0];
                    itemB = info[1];
                    itemC = info[2];
                    
                    if(!list1.equals(0)) {

                        list1.add(itemA);
                        list1.add(itemB);
                        list1.add(itemC);

        BufferedReader reader2 = new BufferedReader(new FileReader("C:\\file2.csv"));
        ArrayList<String> list2 = new ArrayList<String>();


        try {
            boolean bHeadersDone2 = false;
            while (reader2.ready()) {
                String headerInfo2 = reader2.readLine();

                if (!bHeadersDone2) {
                    if (headerInfo2.contains("Characteristic 3")) {
                        bHeadersDone2 = true;
                    }
                }
                else {
                    String[] values = hostInfo.split("," , 3);
                    dataA = values[0];
                    dataB = values[1];
                    dataC = values[2];

                    if (!itemC.contains("[n/a]")) {

                        list2.add(itemA);
                        list2.add(itemB);


                        if (info[0].equals(values[0])) {

                            com.csvreader.CsvWriter writer = new com.csvreader.CsvWriter("C:\\final.csv");
                            
                            writer.writeRecord(new String[]{"Item 1, Item 2, Option 1, Option 2"});

                            itemA = info[0];
                            itemB = info[1];
                            itemC = info[2];
                            dataA = values[0];
                            dataB = values[1];
                            dataC = values[2];
                            

                            writer.writeRecord(new String[]{ itemB, itemC, dataB, dataC });

                        }

                    }
                }
            }
        } catch (Exception e) {
        e.printStackTrace();
        }
            }
                }
            }
        } catch (Exception e) {
        e.printStackTrace();
        }
      
    }
}

Here is what is located in file1.csv

Option 1, Option 2, Option 3
book, chapters, pages
phone, plastic, wires
ipod, music, video

Here is what is located in file2.csv

Characteristic 1, Characteristic 2, Characteristic 3
book, ink, paper
keyboard, keys, lights
phone, numbers, buttons
ipod, songs, movies

My final.csv remains empty, but here is what I am wanting it to look like:

Option 1, Option 2, Option 3, Characteristic 1, Characteristic 2
book, chapters, pages, ink, paper
phone, plastic, wires, numbers, buttons
ipod, music, video, songs, movies

Also I have noticed that when i put a print statement it prints it out like its a loop.

For instance, if I print out list2:

[book, ink, paper]
[book, ink, paper, keyboard, keys, lights]
[book, ink, paper, keyboard, keys, lights, phone, numbers, buttons]
[book, ink, paper, keyboard, keys, lights, phone, numbers, buttons, ipod, songs, movies]

Where as it should look like:

[book, ink, paper]
[keyboard, keys, lights]
[phone, numbers, buttons]
[ipod, songs, movies]

Would greatly appreciate advice and help. Mainly as to why csv writer isn't writing anything at all to the final.csv

[di2daer]

I didn't read your code very carefully, but this sounds like a problem I experience from time to time. You need to close the file when you're done writing to it. Otherwise you will just create it, but it will remain empty.

[BestJewSinceJC]

It also looks like you need to separate your logic into methods that each do one specific task. Right now your code is pretty hard to read and understand.

[KirkPatrick]

I didn't read your code very carefully, but this sounds like a problem I experience from time to time. You need to close the file when you're done writing to it. Otherwise you will just create it, but it will remain empty.

You are correct in stating that I don't close my writer. I added writer.close() and it still doesn't seem to be saving to a file :/

However I put in an arraylist just to see if the data was catching and the arraylist gets the proper information. So the problem remains that it just isn't saving to a file.

By the way, when I check the file properties it says that the file was last modified when I last run the program. How can it be modifying the file if it doesn't save the information? My problem seems to be very similar to yours, I wish it was as simple as closing the writer like you mentioned

Any other ideas?

It also looks like you need to separate your logic into methods that each do one specific task. Right now your code is pretty hard to read and understand.

I tried making separate methods but when I did that I couldn't find a proper way to make it compare values[0] and info[0]

All the code is suppose to do is:

• Read file1.csv
• Read file2.csv
• Check if column 1 from file1.csv is the same as column 1 from file2.csv
• If they are the same, write each row that is column 1 is equal, to a new file

[JamesCherrill]

com.csvreader.CsvWriter writer = new ...

seems to be inside your innermost loop, so you re-open (and thus overwrite) the file contents every time you go thru the loop.
I think you should open the stream just once, at the beginning.

[KirkPatrick]

com.csvreader.CsvWriter writer = new ...

seems to be inside your innermost loop, so you re-open (and thus overwrite) the file contents every time you go thru the loop.
I think you should open the stream just once, at the beginning.

By moving the new writer up to the top it leaves me with a bunch of the following exceptions:

java.io.IOException: This instance of the CsvWriter class has already been closed.

I have a feeling that for the most part, the way that i had it was correct. Because when I print out the arraylist they have the correct information in them, it just never saves it to the file correctly.

So even if it was constantly overwritten, shouldn't it at least leave one copy when I close the program?

[JamesCherrill]

I can't see the close in your posted code, but it sounds like that needs to be moved out of the loop as well.
As for leaving 1 copy - well, yes, maybe. But the open/close within the loop is definitely wrong, so fix that first & see what happens.
Maybe its writing a blank/empty line in its last pass???

[KirkPatrick]

I can't see the close in your posted code, but it sounds like that needs to be moved out of the loop as well.

Aw you are correct. Thank you. I left my close inside that loop without realizing it.

As for leaving 1 copy - well, yes, maybe. But the open/close within the loop is definitely wrong, so fix that first & see what happens.
Maybe its writing a blank/empty line in its last pass???

I do find it rather odd how it left my file modified without actually adding something to it though.

Thank you to all three of you who posted. Its always nice when one can get something working correctly. And much easier to fix problems with multiple people helping out

//for the meantime I am going to leave this unsolved to give BestJewSinceJC a chance to reply about making them into methods. I had tried this before resorting to putting it altogether and didn't have much success.

If he would like to give it a shot he can post it, if not just post saying so and I will mark it as solved.

New Questions

1.
As you can see when I split the file I give it how many columns it was have. Well I just noticed some of the new data I have doesn't always have 3, sometimes it has 2 or even 1. How can I fix this so that it doesn't give me the ArrayOutOfBoundsException?

2.
The comparison I'm doing doesn't seem to be working correctly. I don't think its actually comparing them. However some of them (not all) are being written to file. Any ideas if I wrote the comparison wrong?

if (info[0].equals(values[0])) {
                           
                            itemA = info[0];
                            itemB = info[1];
                            itemC = info[2];
                            dataA = values[0];
                            dataB = values[1];
                            dataC = values[2];
                            

                            writer.writeRecord(new String[]{ itemB, itemC, dataB, dataC });

[JamesCherrill]

Does that mean the file O/P is working now?

[KirkPatrick]

Yes the output is working now, however some of what the output doesn't seem to be right. I'm thinking my comparison might have been written incorrectly. (check above post to see)

[BestJewSinceJC]

Correct me if I'm wrong: What you are trying to do is compare the first column of file 1 to the first column of file 2. So let's say you put all of the Strings from column 1 of file 1 into an ArrayList. Now, let's say you put all of the Strings from column 1 of file 2 into another ArrayList. This gives you the following variable declarations, if I were you, I would make them class variables (so declare them inside the class, preferably right under the class name):

public class yourBeanInfoClassIForgotTheNameOf{
ArrayList<String> file1Column1 = new ArrayList<String>();
ArrayList<String> file2Column1 = new ArrayList<String>();

//Blah blah blah, your methods go here
}

Now, you have a few issues at this point. You need to read in your data into those ArrayLists, and you want to know whether or not what's in file1Column1 matches anything that's in file2Column1, but you also want to keep all of the other Strings (the ones that aren't in the first column) from the two files, and you want to know what column they were in so you can print them out later. Going back to where we declared our two ArrayLists, at this point, I'd probably make a small class to handle keeping the data together.

public class OneRow{
//This is the String from the first column
String firstColumn;
//This is the remainder of the row
ArrayList<String> restOfTheRow;
}

For example, if you had the following file:

book, chapters, pages
phone, plastic, wires
ipod, music, video

You'd have three OneRow objects.
First OneRow object: firstColumn = book
restOfTheRow holds chapters and pages
Second OneRow object: firstColumn = phone
restOfTheRow holds plastic and wires.
Third OneRow object: firstColumn = ipod
restOfTheRow holds music and video.

Above, we originally had this declaration:

ArrayList<String> file1Column1 = new ArrayList<String>();
ArrayList<String> file2Column1 = new ArrayList<String>();

You could now change it to:

ArrayList<OneRow> file1 = new ArrayList<OneRow>();
ArrayList<OneRow> file2 = new ArrayList<OneRow>();

Now, these ArrayLists will hold everything from both files, but in a format that is pretty easy to keep track of.

Now, on to your comparison method. I think what you had down was wrong, so I'll use your example:

File 1

book, chapters, pages
phone, plastic, wires
ipod, music, video

File 2

book, ink, paper
keyboard, keys, lights
phone, numbers, buttons
ipod, songs, movies

Only looking at the first columns of each file:

File 1:
book
phone
ipod

File 2:
book
keyboard
phone
ipod

As you can see, things from file 1 that match things from file 2 are not necessarily at the same index. For example, File 1's phone matches File 2's phone, but File 1's phone is at index 1 and File 2's phone is at index 2. So it seems to me your comparison method would fail (I could be wrong).

Now, let's assume you successfully read the first column of File 1 and the first column of File 2 into two separate ArrayLists of Strings. Now you are ready to use your comparison method. Your comparison method should loop through the Strings in File 1's ArrayList, checking to see if these Strings are also in File 2's ArrayList (because if so, then they have the same column). If these Strings are there, you need to print out the items in a different method.

public void comparisonMethod(){
int index = - 1;
for (int i = 0; i < file1Column1.size(); i++){
   if ((index = file2Column1.indexOf(file1Column1.get(i)) != -1){
      printTheStuff(index, i);
   }
  index = -1;
}
}

Oh, and this will only work assuming each column only appear once in each file. For example, if File 1 has book in its first column, and File 2 has book in its first column twice, this won't work. But it can easily be modified to work that way in one line of code or so. Also, the reason for the OneRow class that I showed you earlier is because it makes it easy to keep track of which items go with which columns, and will make the printing out to the file a lot easier. I hope this is helpful.

If you were wondering why I gave you the suggestion with OneRow Objects but didn't continue with it, it's because with OneRow Objects, using the ArrayList's indexOf method would require slightly more effort than using Strings. I think this additional effort is worth it: all you would have to do is add the following code to the OneRow class:

public class OneRow{
//This is the String from the first column
String firstColumn;
//This is the remainder of the row
ArrayList<String> restOfTheRow;

@Override
public boolean equals(Object obj){
    //I'll let you implement this method, in here you'd want to compare two OneRow Objects for equality. Since you want to compare columns, you'd just want to test if this (this.) OneRow object's firstColumn is equal to Object obj's firstColumn. If so, you return true, otherwise, return false. 
}
}

As a small explanation you can see that in the comparisonMethod I showed you, I called the method indexOf on the ArrayList. Well, indexOf in turn calls the equals() method, so if you want to do it correctly, you'd override the equals method (if you were to put the code into a Java class that I gave you, and put a println into the equals method, you'd see that it gets printed out every time you call indexOf).

Again, I hope this was helpful, if I wasn't addressing the correct issues, sucks for me because that took quite a while to write out. Lol. If I was addressing the correct issues & you need more explanation, feel free to ask. And thanks for the comment, I feel the same about a lot of other members here (Ezzaral, James, Vernon, etc come to mind) - I wonder how they got to be so good at Java. Practice & motivation I guess.

[JamesCherrill]

I wonder how they got to be so good at Java. Practice & motivation I guess.

12 years using Java in my case. The motivation came from needing to pay the mortgage!

ps Great post BJSJC.

[JamesCherrill]

Just a thought: if you store the data in two HashTables, using the first column in each of the two files as keys and the rest of the columns as the values, then finding matching entries becomes trivial...

[KirkPatrick]

Very helpful reply BestJewSinceJC. Right now I'm testing with my comparison (i got it working, there was a minor mistake in my code that i didn't notice when searching for the end of my headers).

I am going to continue testing to see if mine will work. Once I test it thoroughly I am also going to test your example. The way you have explained it, seems cleaner.

Major thanks for your time and explanation there bud.

Just a thought: if you store the data in two HashTables, using the first column in each of the two files as keys and the rest of the columns as the values, then finding matching entries becomes trivial...

I had brought this up in a thread earlier and I didn't quite understand how to implement it.

One problem still remains though, when I split my text file lines I have to split it into 3 columns. Sometimes I have less than 3 columns and it will throw an arrayoutofbounds exception. Is there a way to fix this?

I'll report back on my findings between the two ways of comparing the files when I'm done testing.

[KirkPatrick]

public class yourBeanInfoClassIForgotTheNameOf{
ArrayList<String> file1Column1 = new ArrayList<String>();
ArrayList<String> file2Column1 = new ArrayList<String>();

//Blah blah blah, your methods go here
}

public class OneRow{
//This is the String from the first column
String firstColumn;
//This is the remainder of the row
ArrayList<String> restOfTheRow;
}

For example, if you had the following file:

book, chapters, pages
phone, plastic, wires
ipod, music, video

You'd have three OneRow objects.
First OneRow object: firstColumn = book
restOfTheRow holds chapters and pages
Second OneRow object: firstColumn = phone
restOfTheRow holds plastic and wires.
Third OneRow object: firstColumn = ipod
restOfTheRow holds music and video.

I have read over your post more thoroughly here and I am wondering if this will work when we won't know what the data is in the first column. As that was just an example of data that could be stored in the file.

I assume thats where this comes in?

public class OneRow{
//This is the String from the first column
String firstColumn;
//This is the remainder of the row
ArrayList<String> restOfTheRow;

@Override
public boolean equals(Object obj){
    //I'll let you implement this method, in here you'd want to compare two OneRow Objects for equality. Since you want to compare columns, you'd just want to test if this (this.) OneRow object's firstColumn is equal to Object obj's firstColumn. If so, you return true, otherwise, return false. 
}
}

Is that where I have it decide whether the firstColumn and OneRow objects belong together for when I write a new text file with the info?

As you can see, things from file 1 that match things from file 2 are not necessarily at the same index. For example, File 1's phone matches File 2's phone, but File 1's phone is at index 1 and File 2's phone is at index 2. So it seems to me your comparison method would fail (I could be wrong).

After doing my testing you were 100% correct about your assumption here. I'll now be checking out your suggestion to see how it goes.

Just a thought: if you store the data in two HashTables, using the first column in each of the two files as keys and the rest of the columns as the values, then finding matching entries becomes trivial...

Do you mind elaborating a little bit about how this would work?

[KirkPatrick]

Alright I didn't have a lot of time to work on this today, however I had a brief 10 minutes to try it out and see how the outcome would be.

By the time I got to changing the code around and working with the methods that you showed, however I am having some problems.

Your comparison method tells me that it is having incompatible types, its giving a boolean and wanting an int.

public void comparisonMethod(){
int index = - 1;
for (int i = 0; i < file1Column1.size(); i++){
   if ((index = file2Column1.indexOf(file1Column1.get(i)) != -1){
      printTheStuff(index, i);
   }
  index = -1;
}
}

Then when trying to add strings to the arraylist its showing that there isn't an add method for it.

ArrayList<OneRow> file1Column1 = new ArrayList<OneRow>();
ArrayList<OneRow> file2Column1 = new ArrayList<OneRow>();


//read file, split line of info into columns, and assign strings to each column here

//add split columns to arraylist
file1Column1.add(itemA);
file1Column1.add(itemB); 
etc
etc

I've had an off morning so that may be why I'm not seeing what I'm missing as I'm sure its something obvious. Hope you don't mind pointing me in the right direction again because when I read it, the logic made sense until trying to make it into code.

[BestJewSinceJC]

Are you sure you're looking at http://java.sun.com/j2se/1.4.2/docs/api/java/util/ArrayList.html ? It's possible that you have used the wrong import statement, it should be import java.util.ArrayList. And I typed the code up in this thread, I never compiled it. For the comparison method, it seems like I forgot a parenthesis. It should be:

if ((index = file2Column1.indexOf(file1Column1.get(i))) != -1)

For future reference, a good way to check that is to count the opening parens first, then go back and count the closing parens. The way the computer probably checks this is by pushing any open parens on a stack, then popping an open parens every time it sees a closed parens.

[KirkPatrick]

Are you sure you're looking at http://java.sun.com/j2se/1.4.2/docs/api/java/util/ArrayList.html ? It's possible that you have used the wrong import statement, it should be import java.util.ArrayList. And I typed the code up in this thread, I never compiled it.

I have the correct import, what I think it is, is that I don't fully understand how the class OneRow works.

Here is what is showing next to all the Strings I want to add into my ArrayList:

cannot find symbol
symbol: method add(java.lang.String)
location: class java.util.ArrayList<filelocation.OneRow>

Here are the options it gives me:

add(OneRow e)
add(int index, OneRow element)
addAll(Collection< ? extends OneRow> c)
addAll(int index, Collection< ? extends OneRow> c)

Do you mind explaining a little bit more about the OneRow class, just so I can get a better understanding? At first it seemed pretty basic with the way that you had explained it, but now I'm not quite sure how its supposed to work.

For the comparison method, it seems like I forgot a parenthesis. It should be:

if ((index = file2Column1.indexOf(file1Column1.get(i))) != -1)

For future reference, a good way to check that is to count the opening parens first, then go back and count the closing parens. The way the computer probably checks this is by pushing any open parens on a stack, then popping an open parens every time it sees a closed parens.

Ah, yeah I had noticed that there was a mismatch in the number of parenthesis. However, instead of adding one, I removed one of the opening parenthesis thinking perhaps you accidentally hit it one too many times.

[BestJewSinceJC]

File: "first word in the sentence"

Code to contain that file:

//Lets pretend this Scanner is linked to the file.
Scanner input = new Scanner();
ArrayList<OneRow> file1 = new ArrayList<OneRow>();
OneRow theOnlyRowInTheFile = new OneRow();
//store "first" in firstColumn
theOnlyRowInTheFile.firstColumn = input.next();
//store "word" in the ArrayList that contains the rest of the row
theOnlyRowInTheFile.restOfTheRow.add(input.next());
//store "in" in the ArrayList that contains the rest of the row
theOnlyRowInTheFile.restOfTheRow.add(input.next());
//store "the" in the ArrayList that contains the rest of the row
theOnlyRowInTheFile.restOfTheRow.add(input.next());
//store "sentence" in the ArrayList that contains the rest of the row
theOnlyRowInTheFile.restOfTheRow.add(input.next());

You see, one OneRow object stores one row/line of a File. In order to store the entire File (assuming the file is more than one row/line long), you need an ArrayList of OneRow objects.

[KirkPatrick]

Okay then I had the right idea about how to go about setting it up (although slightly different source since I didn't use Scanner).

However, I still ended up with the same problem while trying your code. Where it doesn't want to recognize the .add method.

cannot find symbol
symbol: method add(java.lang.String)
location: class java.util.ArrayList<filelocation.OneRow>

add(OneRow e)
add(int index, OneRow element)
addAll(Collection< ? extends OneRow> c)
addAll(int index, Collection< ? extends OneRow> c)

Do you know what could be the problem?

[JamesCherrill]

It says you are trying to add a String to an ArrayList of OneRow objects. You an only add a OneRow or a Collection of OneRows

[KirkPatrick]

It says you are trying to add a String to an ArrayList of OneRow objects. You an only add a OneRow or a Collection of OneRows

I found my problem, it is highlighted in bold below:

String firstColumn;
ArrayList<OneRow> restOfTheRow;

Scanner input = new Scanner(new File("myFile"));
ArrayList<OneRow> file1 = new ArrayList<OneRow>();

OneRow theOnlyRowInTheFile = new OneRow();
theOnlyRowInTheFile.firstColumn = input.next();
theOnlyRowInTheFile.restOfTheRow.add(input.next());
theOnlyRowInTheFile.restOfTheRow.add(input.next());
theOnlyRowInTheFile.restOfTheRow.add(input.next());
theOnlyRowInTheFile.restOfTheRow.add(input.next());
theOnlyRowInTheFile.restOfTheRow.add(input.next());

Changed it to:

ArrayList restOfTheRow;

Now to finish my class I would add a second scanner

public class OneRow{
        
        String firstColumn;
        ArrayList restOfTheRow;

        public OneRow() throws FileNotFoundException {

            Scanner input = new Scanner(new File("myFile"));
            ArrayList<OneRow> file1 = new ArrayList<OneRow>();

            OneRow theOnlyRowInTheFile = new OneRow();
            theOnlyRowInTheFile.firstColumn = input.next();
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            
            Scanner input = new Scanner(new File("myOtherFile"));
            ArrayList<OneRow> file2 = new ArrayList<OneRow>();

            OneRow theOnlyRowInTheFile = new OneRow();
            theOnlyRowInTheFile.firstColumn = input.next();
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());
            theOnlyRowInTheFile.restOfTheRow.add(input.next());

and then I create the comparison method

public void comparisonMethod(){
           int index = -1;
           for (int i = 0; i < file1.size(); i ++) {
       	       if ((index = file2.indexOf(file1Column1.get(i))) != -1) {
                   writer.writeRecord(new String[]{ itemApart2, itemB, itemC, item1part2, item2, item3 });
               }
              index = -1;
           }
       }

Does that sound correct?

[BestJewSinceJC]

Other than the comparison method (which I don't have time right now to look at), the rest of it looks good.

ArrayList restOfTheRow; could be changed to ArrayList<String> restOfTheRow; and you also need to make sure to instantiate it so ArrayList<String> restOfTheRow = new ArrayList<String>();