Read .txt file (in Java) and edit it

Hello experts,
I need to do the following (preferrably in java)

Read a .txt file containing sql input data in a messed format. The format currently is :

COLUMN1NAME;COLUMN2NAME; ...
ValueForColumn1;ValueForColumn2; ...
ValueForColumn1;ValueForColumn2; ...
...

Some of the values also have incorrect formating.
I need to be able to read this file of mine in Java and edit it.
Some of the things I need to do : change all the ';' into ',' , change all the values for COLUMN2 by using a query, put single quotation marks around some of the values, place a '(' infront of all the lines and a ')' behind them all.  
I have had some limited dealings with Java and none having to do with reading and editing text document.

Please tell me if this can be done more easily in some other enviroment than Java, I only mentioned it because I have used it a little. I'm open to anything, I just wan't to avoid editing this file manually.

Thanks for your help.
MannsiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
It could be done in Java but might be better off done in some other way. Can you attach the file?
0
MannsiAuthor Commented:
No prob, here it is.
If it's only marginally easier without Java, I would prefer a solution in Java, but beggers can't be choosers.

A word of warning, it's all in Icelandic so it will make 0 sens to you.
data2.txt
0
CEHJCommented:
You essentially have a CSV file there. You should be able (if that's your objective) to use it more or less as is to insert into a db
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

MannsiAuthor Commented:
How would I insert the data into the db directly ? The first line is the column names and the rest is data, but how do I insert ?
My problem is that the data are incompatible with the db as it stands. F.x. the first date in the first data line is 17-Dec-2008 but in order to get into my db I need to assign it an integer value that corresponds to that particular day. I also need to make sure that the data in the first column is of a correct format, it should contain 10 integers but I know that in some cases it contains 9 and I need to fix that.
0
CEHJCommented:
>>I need to assign it an integer value that corresponds to that particular day

How does that work?

>>I also need to make sure that the data in the first column is of a correct format, it should contain 10 integers but I know that in some cases it contains 9 and I need to fix that.

*Can* you fix it, or would you ignore it?
0
MannsiAuthor Commented:
I have a table that links dates with integers so I would have to query that table with my date from the file and replace the date with the corresponding integer from the table.

The 'fix' for the first column is to insert a 0 in front of a integer containing only 9 integers. I can obviously do this manually but I will have to do this task many more times in the future and having it done automatically would be great.
0
CEHJCommented:
I can give you a template solution. I would make your first step getting hold of the Ostermiller CSV class


http://ostermiller.org/utils/CSV.html
0
MannsiAuthor Commented:
Got the CSV class
0
CEHJCommented:
This should get you started, you need to fill in the mapDate method:
import com.Ostermiller.util.CSVParser;
import java.io.*;
 
public class CsvInsert {
    public static void main(String[] args) {
        try {
            CsvInsert.toSql(args[0]);
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
 
    public static void toSql(String csvFile) throws IOException {
        String[][] vals = CSVParser.parse(new FileReader(csvFile), ';');
        for(int i = 1;i < vals.length;i++) {
            vals[i][0] = toTenDigits(vals[i][0]);
            vals[i][5] = toTenDigits(vals[i][5]);
            vals[i][6] = toTenDigits(vals[i][6]);
            System.out.println(java.util.Arrays.toString(vals[i]));
        }
    }
 
    public static String toTenDigits(String number) {
        return String.format("%010d", Long.parseLong(number));
    }
 
    public static String mapDate(int colNumber, String dateIn) {
        return "100";
    }
}

Open in new window

0
CEHJCommented:
One thing bothers me about the above: does it support the character encodings properly?
0
CEHJCommented:
That should have been the temporary:
    public static void toSql(String csvFile) throws IOException {
        String[][] vals = CSVParser.parse(new FileReader(csvFile), ';');
        for(int i = 1;i < vals.length;i++) {
            vals[i][0] = toTenDigits(vals[i][0]);
            vals[i][5] = mapDate(5, vals[i][5]);
            vals[i][6] = mapDate(6, vals[i][6]);
            System.out.println(java.util.Arrays.toString(vals[i]));
        }
    }

Open in new window

0
MannsiAuthor Commented:
I haven't tried anything yet.  Just to make sure, I should replace all instances of csvFile with the path to my data file, right ?
0
MannsiAuthor Commented:
I haven't tried anything yet.  Just to make sure, I should replace all instances of csvFile with the path to my data file, right ?
0
CEHJCommented:
Yes
0
MannsiAuthor Commented:
I get errors if I simply change csvFile to my file location in toSql method and try to compile. I don't get any errors if I compile without changing, but I get an error when I try to run the program.
0
MannsiAuthor Commented:
should I maybe have the path within quotation marks ???
0
CEHJCommented:
Certainly, if it contains spaces
0
MannsiAuthor Commented:
it contains no spaces,  it gives me an error for each '/' sign in my path.
0
CEHJCommented:
please post what you're doing
0
MannsiAuthor Commented:
.
import com.Ostermiller.util.CSVParser;
import java.io.*;
 
public class CsvInsert {
    public static void main(String[] args) {
        try {
            CsvInsert.toSql(args[0]);
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
 
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
        String[][] vals = CSVParser.parse(new FileReader(/home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt), ';');
        for(int i = 1;i < vals.length;i++) {
            vals[i][0] = toTenDigits(vals[i][0]);
            vals[i][5] = toTenDigits(vals[i][5]);
            vals[i][6] = toTenDigits(vals[i][6]);
            System.out.println(java.util.Arrays.toString(vals[i]));
        }
    }
 
    public static String toTenDigits(String number) {
        return String.format("%010d", Long.parseLong(number));
    }
 
    public static String mapDate(int colNumber, String dateIn) {
        return "100";
    }
}

Open in new window

0
CEHJCommented:
The only difference at this point should be line 15 of my code, which you should replace with
        String[][] vals = CSVParser.parse(new FileReader("/home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt"), ';');

Open in new window

0
MannsiAuthor Commented:
After changing I get the following error msg
javac CsvInsert.java 
 
CsvInsert.java:14: <identifier> expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                   ^
CsvInsert.java:14: ';' expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                     ^
CsvInsert.java:14: illegal start of type
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                         ^
CsvInsert.java:14: ';' expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                                ^
CsvInsert.java:14: <identifier> expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                                           ^
CsvInsert.java:14: <identifier> expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                                                        ^
CsvInsert.java:14: <identifier> expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                                                                       ^
CsvInsert.java:14: <identifier> expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                                                                                 ^
CsvInsert.java:14: illegal start of type
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                                                                                   ^
CsvInsert.java:14: ';' expected
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
                                                                                                                     ^
10 errors

Open in new window

0
CEHJCommented:
Perhaps i didn't explain properly, the only difference (at this point) between my code and yours is line 15. One line difference only. That line should be as i just posted
0
MannsiAuthor Commented:
Sorry for the delayed post, I have been very busy.

ATM this is how my code looks like, and I get the errors posted by me above.

Am I not following your instructions ?
import com.Ostermiller.util.CSVParser;
import java.io.*;
 
public class CsvInsert {
    public static void main(String[] args) {
        try {
            CsvInsert.toSql(args[0]);
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
 
    public static void toSql(String /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt) throws IOException {
        String[][] vals = CSVParser.parse(new FileReader("/home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt"), ';');
        for(int i = 1;i < vals.length;i++) {
            vals[i][0] = toTenDigits(vals[i][0]);
            vals[i][5] = toTenDigits(vals[i][5]);
            vals[i][6] = toTenDigits(vals[i][6]);
            System.out.println(java.util.Arrays.toString(vals[i]));
        }
    }
 
    public static String toTenDigits(String number) {
        return String.format("%010d", Long.parseLong(number));
    }
 
    public static String mapDate(int colNumber, String dateIn) {
        return "100";
    }
}

Open in new window

0
CEHJCommented:
>>Am I not following your instructions ?

No. Read them carefully - they're precise (i hope)
0
MannsiAuthor Commented:
???

"Perhaps i didn't explain properly, the only difference (at this point) between my code and yours is line 15. One line difference only. That line should be as i just posted"

So I take my code, the one I posted 3 days ago and I changed line 15 to what you said. I still get errors. I have posted my latest code and I have posted the errors I get from it.

What am I missing ?
0
CEHJCommented:
OK. Let's do it like this. Make sure you pass the name of the file on the command line when you start the app:
   public static void toSql(String pathToFile) throws IOException {
        String[][] vals = CSVParser.parse(new FileReader(pathToFile), ';');
        for(int i = 1;i < vals.length;i++) {
            vals[i][0] = toTenDigits(vals[i][0]);
            vals[i][5] = toTenDigits(vals[i][5]);
            vals[i][6] = toTenDigits(vals[i][6]);
            System.out.println(java.util.Arrays.toString(vals[i]));
        }
    }

Open in new window

0
CEHJCommented:
Sorry - i lost concentration a bit before


What i just posted is the literal, actual code. When you start the app, do it like this


java CSVInsert  /home/mannsi/tolvunarfr/gagnasafnsfr/skilaverkefni2/data2.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MannsiAuthor Commented:
Thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.