saras_js
asked on
How to convert text file with multiple lines into a database?
How to Fetch the records from text files. Text data is not of fixed size or in single line. Data carried over to multiple lines all pertained to one record. i.e each record contains mutliple items Pls. help me to convert the text file as in below format to a database.
SB No. SB Date Port of Destination Inv Inv Item Item Rate Quantity UQC Sch
Exporter Name & Address Consignee Name & Address SNo Cur No. FOB Value Item Description Code
-------------------------- ---------- ---------- ---------- ---------- ----- --- --- ---- -------------------------- ---------- ---
2711316 02/07/2007 Port :Saarlouis Country : DE 1 EUR 1 1.26000 3888.000 NOS 50
MOTHERSON SUMI SYSTEMS LTD,MATE FORD WERKE GMBH...,D-66742 240786.42 MOTOR VEHICLES PARTS COWL
DIV.CHANGALPATTU- SAARLOUIS,C/O AUTOMOTIVE SERVICE RITC:87082900 LH PART NO :4M51 R 02349
SRIPERUMBUDUR,ROAD, PONDUR VILLAGE ANTERIST + S,CHNEIDER GMBH AA32N5.
& POST NEAR,SRIPERUMBUDUR TALUK, 2 1.26000 3888.000 NOS 50
KANCHEEPURAM,TAMILNADU Pin:602105 240786.42 MOTOR VEHICLES PARTS COWL
RITC:87082900 RH PART NO :4M51 R 02348
AA32N5.
3 0.51060 2880.000 NOS 50
72278.48 MOTOR VEHICLES PARTS SCUFF
RITC:87082900 LH PART NO :4M51 R 13201
AA32N5.
4 0.51060 2880.000 NOS 50
72278.48 MOTOR VEHICLES PARTS SCUFF
RITC:87082900 RH PART NO :4M51 R 13200
AA32N5.
2711317 02/07/2007 Port :Johannesburg Country : ZA 1 GBP 1 1.67760 600.000 NOS 19
COOPER BUSSMANN INDIA PVT. M/S. ADVANCE PRODUCT 78663.22 HRC FUSES 160AMP 415V AC
LTD.,,FACTORY: 3 A E.V.R TECHNOLOGY,CORNER REMBLOK & LANGWA RITC:85351040 TYPE J
STREET,,SEDDARAPET,PONDICH ERRY,POND STREET,STRIJDOM PARK RANDBURG,SOUTH 2 0.65370 1500.000 NOS 19
ICHERRY Pin:605111 AFRICA 76630.52 HRC FUSES 80A 415V AC HOUSE
RITC:85351040 SERVICE
3 0.34343 400.000 NOS 19
10735.54 HRC FUSES 16AMP 550V AC
RITC:85351040 BS88 FUSE
Thanks in advance
Regards
Saraswathy
SB No. SB Date Port of Destination Inv Inv Item Item Rate Quantity UQC Sch
Exporter Name & Address Consignee Name & Address SNo Cur No. FOB Value Item Description Code
--------------------------
2711316 02/07/2007 Port :Saarlouis Country : DE 1 EUR 1 1.26000 3888.000 NOS 50
MOTHERSON SUMI SYSTEMS LTD,MATE FORD WERKE GMBH...,D-66742 240786.42 MOTOR VEHICLES PARTS COWL
DIV.CHANGALPATTU- SAARLOUIS,C/O AUTOMOTIVE SERVICE RITC:87082900 LH PART NO :4M51 R 02349
SRIPERUMBUDUR,ROAD, PONDUR VILLAGE ANTERIST + S,CHNEIDER GMBH AA32N5.
& POST NEAR,SRIPERUMBUDUR TALUK, 2 1.26000 3888.000 NOS 50
KANCHEEPURAM,TAMILNADU Pin:602105 240786.42 MOTOR VEHICLES PARTS COWL
RITC:87082900 RH PART NO :4M51 R 02348
AA32N5.
3 0.51060 2880.000 NOS 50
72278.48 MOTOR VEHICLES PARTS SCUFF
RITC:87082900 LH PART NO :4M51 R 13201
AA32N5.
4 0.51060 2880.000 NOS 50
72278.48 MOTOR VEHICLES PARTS SCUFF
RITC:87082900 RH PART NO :4M51 R 13200
AA32N5.
2711317 02/07/2007 Port :Johannesburg Country : ZA 1 GBP 1 1.67760 600.000 NOS 19
COOPER BUSSMANN INDIA PVT. M/S. ADVANCE PRODUCT 78663.22 HRC FUSES 160AMP 415V AC
LTD.,,FACTORY: 3 A E.V.R TECHNOLOGY,CORNER REMBLOK & LANGWA RITC:85351040 TYPE J
STREET,,SEDDARAPET,PONDICH
ICHERRY Pin:605111 AFRICA 76630.52 HRC FUSES 80A 415V AC HOUSE
RITC:85351040 SERVICE
3 0.34343 400.000 NOS 19
10735.54 HRC FUSES 16AMP 550V AC
RITC:85351040 BS88 FUSE
Thanks in advance
Regards
Saraswathy
This seems to me like the contents of a Word file were copied into a text file as it. Files without any set format and unformity are in my opinion quite difficult to load directly into a DB. My suggestion would be try to open it in Excel and lend some structure to it. Then you can save it as a CSV and easily load it into any database.
You either need:
1) Set positions in the text file
2) Delimiters in the text file "," TAB or another
3) Tags like this <product#>C-125</product#>
4) or a certain format which can be programmed to be picked up
1) Set positions in the text file
2) Delimiters in the text file "," TAB or another
3) Tags like this <product#>C-125</product#>
4) or a certain format which can be programmed to be picked up
ASKER
I tried to convert CSV file also. When opening a file in excel, all data appears in a line. Columnwise data extraction is not possbile. I think this output - report in text format, is from multiple data files. Is there any possibility to break and set a delimiter to fields etc?
This file can be imported but it needs a whole lot of work.
What I did is paste it in Notepad and Unchecked WordWrap. This way you can get the feel of it and here is what you need to do. It will take you a little while to write the program but it's very doable.
You open the file with FOPEN
You scan the file with FGETS or FREAD
You parse the lines or delete unneceesary ones
Here you need to delete headers, underlines and empty lines
You have to follow positions. When INV SNO, Currency is not empty, it means you are at the beginning of a group/item. When INV SNO, Currency are empty and Item No is not empty, it means you are at the beginning of a new item. Each time you are at the beginning of a new item you read 3 lines.
You close the file with FCLOSE
To parse a line let's say it's in cLine
cCurrency = SUBSTR(cLine,81,3)
I gave you the complete solution. You can write it out. It should take you half a day's work if you are experience and around 2 days if you are not.
What I did is paste it in Notepad and Unchecked WordWrap. This way you can get the feel of it and here is what you need to do. It will take you a little while to write the program but it's very doable.
You open the file with FOPEN
You scan the file with FGETS or FREAD
You parse the lines or delete unneceesary ones
Here you need to delete headers, underlines and empty lines
You have to follow positions. When INV SNO, Currency is not empty, it means you are at the beginning of a group/item. When INV SNO, Currency are empty and Item No is not empty, it means you are at the beginning of a new item. Each time you are at the beginning of a new item you read 3 lines.
You close the file with FCLOSE
To parse a line let's say it's in cLine
cCurrency = SUBSTR(cLine,81,3)
I gave you the complete solution. You can write it out. It should take you half a day's work if you are experience and around 2 days if you are not.
If you need to remove certain delimiters cPartNo = RITC:87082900:
cPartNo = STRTRAN(cPartNo,'RITC:','' )
If you need to convert from string to numeric
nFOB = VAL(cFOB)
cPartNo = STRTRAN(cPartNo,'RITC:',''
If you need to convert from string to numeric
nFOB = VAL(cFOB)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can You upload the file so that i can take a look at the file ?