• Post Reply Bookmark Topic Watch Topic
  • New Topic

how to load bypassing some headers from a .txt into a table.  RSS feed

 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a txt file with | as delimiter. It contains some sections with starting words as ***begin: SOME VALUE and ***end: SOME VALUE as lines.

I need to look for specific sections with line BEGINNING WITH ***begin: AND THE VALUE I WANT TO and skip the first line which is a header and load the remaining records till I hit the end statement to database.
The file looks something like this ,
***begin: RESTURANT :
ID|CHEF NAME|YEAR|SENIORITY
1|RONI|2015|5
***end: RESTURANT :
***begin: TABLES:
ID|YEAR|PERIOD|DATE
1|2015|2|02-10-2015
2|2014|3|09-10-2014
***END: TABLES :

now I need to look for the word TABLES AFTER THE ***begin:
if it exists, skip the first record, if it starts with ID
and load from the next record onwards TILL I HIT THE ***END SENTENCE.

what is the best method to do it,

since I need to select some sections only from the file, I am not sure, if I can bulk insert into table.
Is it a good idea to insert record by record by reading from java and loading using a insert proc call within java.

I just want to know what is the best approach to get some sections data into respective tables.

Also I need to collect the data from RESTURANT section and use that data. Is HASHMAP a best way to store saying ID, 1
YEAR,2015
in that way, I will be storing the 4 values in four hashtables to fetch them for loading some columns of the tables.

Please advise me, as I am kind of learning to use java properly.

 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That is not Java®. That is formats of text files. You must define the exact format of the text file and the exact format of the headers and footers. Once that is decided, then all you need to do is to identify the lines.

Tell us the exact format of the lines you wish to pick up. Remember you can create a Scanner for a text file and have it read line by line.
 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you should create Restaurant objects from the lines in your file. You have not explained why you want to keep the details in a Map. Nor why you are using hash tables at all. It sounds as if you are guessing about how to store the information, and that means you will probably have guessed wrongly.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sir,

first of all thank You for the reply.
I need to load sections of a file to tables in DB.
The RESTURANT section gives me data for some columns of the table, so I need to store that one record and insert each value to one column along with data from other sections.

I need to use Java, as the file needs to be taken in parts to load into different tables.

I don't know exactly what to use and how to do this,

This the file format:
***begin: RESTURANT :
ID|CHEF NAME|YEAR|SENIORITY
1|RONI|2015|5
***end: RESTURANT :
***begin: TABLES:
ID|YEAR|PERIOD|DATE
1|2015|2|02-10-2015
2|2014|3|09-10-2014
***END: TABLES :

My databse table say TABLE 1 has following columns
TABLE 1:-
RESTURANT_ID NAME YEAR SENIORITY TABLE_ID TABLE_YEAR TABLE_PERIOD TABLE_DATE
----------------- ------- ------ ------------ ----------- -------------- ----------------- ---------------


Please tell me how should I be doing,
Get the file and use the delimiter | and bring the values into ArrayList from TABLES SECTION
And also use 4 HASHMAP'S to get the first 4 columns values into table while loading the records from the ArrayList using iterator into INSERT PROCEDURE to load into TABLE.

Am I correct on this approach?

 
Carey Brown
Saloon Keeper
Posts: 3317
46
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
rekha sen wrote:Please tell me how should I be doing,
Get the file and use the delimiter | and bring the values into ArrayList from TABLES SECTION
And also use 4 HASHMAP'S to get the first 4 columns values into table while loading the records from the ArrayList using iterator into INSERT PROCEDURE to load into TABLE.

Am I correct on this approach?


1. Open the file
2. Read each line
3. Identify lines with begin/end
4. Ignore first line after begin
5. Parse lines with delimiter
6. Create Restaurant and Table objects
7. Write data to DB

Take each step in turn. Test each step before going to the next step. Show us what you've done. Ask specific questions about any problems you are having.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sir,

In step3:

How do I do, Step 4: you have mentioned. Ignore first line after BEGIN?

 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That won't work because you cannot match the *s
What is wrong with the beginsWith/startsWith/endsWith methods of the String class?
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How do I specifically skip the header line which is right below the BEGIN line in each of the sections.

I have 4 BEGIN and END sections, which contain one header line and other data lines.
I need to load the data lines after parsing into DB.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first BEGIN AND END section should be containing one HEADER line and one data line only.
How do I eliminate HEADER line and check if there is only one data line between BEGIN and END.

***begin: RESTURANT :
ID|CHEF NAME|YEAR|SENIORITY
1|RONI|2015|5
***end: RESTURANT :
 
Carey Brown
Saloon Keeper
Posts: 3317
46
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You'll need to keep track of the "state" of your file reading. You can use a state number which is easy, but as you get better with Java an enum would be the better choice.
State:
1 - just starting out
2 - found begin RESTAURANT
3 - read header
4 - reading restaurant record(s)
5 - found end RESTAURANT
6 - found begin TABLES
7 - read header
8 - read TABLES record(s)
9 - found end TABLES

States 1,2,3,5,66,7,9 should only occur for a single line of the input file. When you get to state 4 you'll stay there until you find END and when you reach state 8 you'll stay there until you find END. At the point you reach 9 you quit.


This is what the code would look like for a single state. The other states are similar.

That is one approach. Another approach would be to have multiple calls to readLine(), one for each state (no switch required with this approach and no 'state' variable required). States 4 and 8 would require loops to read multiple records, the remaining states would not require a loop.

I'm not sure which approach would be easiest for you. Probably the second.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another approach would be to have multiple calls to readLine(), one for each state (no switch required with this approach and no 'state' variable required). States 4 and 8 would require loops to read multiple records, the remaining states would not require a loop.

I'm not sure which approach would be easiest for you. Probably the second.

Sir,

Can you please show me how the second approach would look like.
it is more visual with code at this point for me. Thank You.
 
Carey Brown
Saloon Keeper
Posts: 3317
46
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1 - just starting out
2 - found begin RESTAURANT
3 - read header
4 - reading restaurant record(s)
5 - found end RESTAURANT
6 - found begin TABLES
7 - read header
8 - read TABLES record(s)
9 - found end TABLES


Note this does not take into account error checking to see if readLine() is not null or that you find the begin/ends in the appropriate places.
 
Carey Brown
Saloon Keeper
Posts: 3317
46
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At this point, if it still doesn't make sense I'd say it's time to break out the pencil and paper and play computer. Go step by step through your sample data file and write down what you, as a human, would do to interpret the data.
 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yesterday, I wrote:That won't work because you cannot match the *s
. . .
And I was mistaken. I had forgotten that the *** is actually part of the text. Sorry.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank You Sir,

working with the switch logic.

 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Input record is something like this,
SchoolReport|SchoolType|SchoolID|SchoolYear|SchoolPeriod|SchoolClass|SchoolNum|SchoolData|SchoolCol|SchoolDesc|SchoolRow|SchoolLogo
Report1|Public|123|2015|Q2||No1|High|10|Good|10th|

If you see, the SchoolClass and SchoolLogo has no value. I have handled no values in the below code.
Still I get this error message


the column length is: 11
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 11






Please, let me know where I have gone wrong. Thank You.
 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where on earth does that magic number 12 come from? You don't want to put numbers in for loop headers, well maybe 0 and ±1 only. Never the length of the array as a number. Always write myArray.length.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

The header is skipped and the records are loaded into the DB.
Please see the record I wrote in my earlier email, it has no value in 2 places.







I still get the same error,

the column length is: 11
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 11



 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
rekha sen wrote: . . . <= . . .
And where did you get that from? It is <
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This input record
Report1|Public|123|2015|Q2||No1|High|10|Good|10th|

The last value after | has no space.
And the code I wrote is failing there saying,
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 11


Now all my records do not have space after the last delimiter.
And I have to capture it as null.

So I wrote code as


And when it reaches this point, it is giving me an error.
 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And where does [11] come from?
 
Liutauras Vilda
Sheriff
Posts: 4917
334
BSD
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
11 is wrong, 10 is better, but not good enough. Declare it as a constant "final int LOGO = 10;"
Looks clearer, isn't it?

[edit]corrected mistake, checking length of String, so added brackets. And i'm not sure you have column 10th, but in anyway, declare it as a constant the one you need.
 
Campbell Ritchie
Marshal
Posts: 56540
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you know it is the last column then try
columns[columns.length - 1]
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sir,
these are my real records which I am parsing using the delimiter.
Now the first record, has no space after the last delimiter and the length of the array is 11 for it.If I do length - 1 as you told me, it is picking the 10th value ie., {LINE NUMBER} where as it should pick up nothing and I want to assign a value null, if there is nothing.
The second line has a last value after the delimiter, so it is picking the last value when I say length - 1.

please see the real records and guide me in handling records which do not have anything after the last delimiter.
IS it actually possible to code and give a null value, if nothing exists after the last delimiter.

INPUT LINES:-

POR|POR|TBL32A|2015|SA|139|1|STRING|{LINE NUMBER}|{LINE NUMBER}|{LINE NUMBER}|
POR|POR|TBL32A|2015|SA|32006|1|DOUBLE|TOTAL K MEDICAL HOME PAYABLE|TOTAL|{FORMULA}|{0:#,##0}

The output from the java code is as follows for the above 2 input records:
the column length is: 11
dCostReport is: POR
dReportType is: POR
dTableId is: TBL32A
dReportYr is: 2015
dReportPeriod is: SA
dClassCode is: 139
dLineNum is: 1
dDataType is: STRING
dColType is: {LINE NUMBER}
dRowDesc is: {LINE NUMBER}
dRowType is: {LINE NUMBER}
dMask is: {LINE NUMBER}
the column length is: 12
dCostReport is: POR
dReportType is: POR
dTableId is: TBL32A
dReportYr is: 2015
dReportPeriod is: SA
dClassCode is: 32006
dLineNum is: 1
dDataType is: DOUBLE
dColType is: TOTAL K MEDICAL HOME PAYABLE
dRowDesc is: TOTAL
dRowType is: {FORMULA}
dMask is: {0:#,##0}




I want to know, is it a must to have atleast a single space, if there is no value in between 2 delimiters and even for the last delimiter.
how to I consider a null value, if there is nothing after a delimiter at the end and in between 2 delimiters, so where in the record.

Please let me know. Thank You.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sir,

it is the last delimiter with not even a space is bringing out a wrong value when I say,


Please tell me a solution for working with files where, after the last delimiter there is not even a space and we need to consider a value null to it.
 
rekha sen
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


The above code solved my problem.

Thank You Again for giving me a algorithm to start and finish the Program.
 
Liutauras Vilda
Sheriff
Posts: 4917
334
BSD
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What would happen if you'd have string "space"? Would you get a wanted null value?
 
Carey Brown
Saloon Keeper
Posts: 3317
46
Eclipse IDE Firefox Browser Java MySQL Database VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For those two different style records

Your column.length will be different for those two types of records. You can use this information to your advantage.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!