Link to home
Start Free TrialLog in
Avatar of saras_js
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,PONDICHERRY,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

Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

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.
Avatar of Cyril Joudieh
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
Avatar of saras_js
saras_js

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.
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)

ASKER CERTIFIED SOLUTION
Avatar of Cyril Joudieh
Cyril Joudieh
Flag of Lebanon image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can You upload the file so that i can take a look at the file ?