How to convert text file with multiple lines into a database?

Posted on 2007-09-28
Last Modified: 2008-01-09
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
                                                                                        3           0.51060     2880.000   NOS 50
                                                                                                72278.48 MOTOR VEHICLES PARTS SCUFF
                                                                                           RITC:87082900 LH  PART NO :4M51 R 13201
                                                                                        4           0.51060     2880.000   NOS 50
                                                                                                72278.48 MOTOR VEHICLES PARTS SCUFF
                                                                                           RITC:87082900 RH  PART NO :4M51 R 13200
  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
  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

Question by:saras_js
    LVL 18

    Expert Comment

    by:Jinesh Kamdar
    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.
    LVL 27

    Expert Comment

    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

    Author Comment

    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?
    LVL 27

    Expert Comment

    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.
    LVL 27

    Expert Comment

    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)

    LVL 27

    Accepted Solution

    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
    = FCLOSE(nFileHandle)
    LVL 8

    Expert Comment

    Can You upload the file so that i can take a look at the file ?

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Greater Than Current Month 2 26
    how do you view Microsoft Access Relationships 2 20
    Order by 8 21
    Displaying on a report 2 0
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now