?
Solved

Read .txt file (in Java) and edit it

Posted on 2008-11-16
29
Medium Priority
?
4,166 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Mannsi
  • 15
  • 14
29 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970391
It could be done in Java but might be better off done in some other way. Can you attach the file?
0
 

Author Comment

by:Mannsi
ID: 22970415
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970465
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Mannsi
ID: 22970502
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970515
>>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
 

Author Comment

by:Mannsi
ID: 22970527
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970543
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
 

Author Comment

by:Mannsi
ID: 22970659
Got the CSV class
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970703
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970707
One thing bothers me about the above: does it support the character encodings properly?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970729
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
 

Author Comment

by:Mannsi
ID: 22970767
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
 

Author Comment

by:Mannsi
ID: 22970769
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22970808
Yes
0
 

Author Comment

by:Mannsi
ID: 22971115
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
 

Author Comment

by:Mannsi
ID: 22971121
should I maybe have the path within quotation marks ???
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22971174
Certainly, if it contains spaces
0
 

Author Comment

by:Mannsi
ID: 22971238
it contains no spaces,  it gives me an error for each '/' sign in my path.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22971534
please post what you're doing
0
 

Author Comment

by:Mannsi
ID: 22971553
.
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22971684
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
 

Author Comment

by:Mannsi
ID: 22972097
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22972104
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
 

Author Comment

by:Mannsi
ID: 22985916
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22986752
>>Am I not following your instructions ?

No. Read them carefully - they're precise (i hope)
0
 

Author Comment

by:Mannsi
ID: 22994410
???

"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
 
LVL 86

Expert Comment

by:CEHJ
ID: 22998548
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
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 22998860
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
 

Author Closing Comment

by:Mannsi
ID: 31517230
Thank you very much
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This video teaches viewers about errors in exception handling.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question