[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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

0
saras_js
Asked:
saras_js
1 Solution
 
Jinesh KamdarCommented:
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.
0
 
CaptainCyrilCommented:
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
0
 
saras_jsAuthor Commented:
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CaptainCyrilCommented:
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.
0
 
CaptainCyrilCommented:
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)

0
 
CaptainCyrilCommented:
Here is how to get started.

cFileName = GETFILE('TXT')
nFileHandle = FOPEN(cFileName,0)
nLine = 0
DO WHILE NOT FEOF(nFileHandle)
  nLine = nLine + 1
  WAIT WINDOW "Reading " + ALLTRIM(STR(nLine)) NOWAIT
  cLine = FGETS(nFileHandle,256)
  DO CASE
    ** CASE header
    ** CASE underline
    ** CASE empty line
    ** CASE end of page
    ** CASE new group of items
  ENDCASE
ENDDO
= FCLOSE(nFileHandle)
0
 
suhashegdeCommented:
Can You upload the file so that i can take a look at the file ?
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now