Results 1 to 3 of 3

Thread: How can I....

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Northern, California, USA
    Thanked 0 Times in 0 Posts

    How can I....

    I have a series of text files which contain a very specialized and specific layout. These text files are imported into a database. The layout is something like this.


    However, on occasion, I will get this:


    Currently, I have to manually copy the DATE|INI|AMT|||INCREMENT|TOTAL||| Line, and paste it before the second DESCRIPTION (INCREMENT) line to make it a new entry before the database can accept it. Then I have to adjust the TOTAL to match the AMT the INI charges.

    Does anyone have any insight as to which product in the office suite I can use to accomplish this, and if so, what steps I might take to make it happen?

    <IMG SRC=>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: How can I....

    > Then I have to adjust the TOTAL to match the AMT the INI charges.

    How do you know how much to allocate to each DESCRIPTION?

    If you're VBA-inclined, this sounds like a good application for an array of user-defined types. Digging in an old VBA module...

    <pre>Public Type gudtCltMtrUsed 'clt-mtr info from
    sCMNick As String 'Juris for the user's
    sCCode As String 'date range (shown in
    sMCode As String 'listbox for choosing
    boolSelected As Boolean 'matter to report on)
    End Type
    Public gudtCltMtrArray() As _
    gudtCltMtrUsed 'dynarray of above info

    If your string has more than one DESCRIPTION, as you add stuff to your array, you simply duplicate the values from the fields that don't change (e.g., by referring to the array element at the current loop counter minus 1). If you can't determine the number of DESCRIPTION elements in advance, you could set a flag on all of the entries that form a group (just an integer counter would work) and then process them later in a separate loop. Fun stuff!!

    If you prefer to lay the data down in a document, I think either Word (tables) or Excel could hold the data, and save it to an appropriate file with some futzing.

    Just curious whether this data came from FileMaker Pro? FileMaker has a type of field called a repeating field which could produce the pattern you are describing. I don't know if importing the data into FileMaker and then exporting it again as a more regular format might help. Probably no faster than using VBA.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Youngstown, Ohio, USA
    Thanked 0 Times in 0 Posts

    Re: How can I....

    I use Monarch to handle text files similar to yours. This is not an Office, or even a Microsoft, product but comes from Datawatch (<A target="_blank" HREF=></A>). It allows you to set up a template to trap text that falls into specific patterns and convert it into data tables. There is quite an array of functions & filters you can use from within Monarch, and it supports exporting the table-ized data into spreadsheet or database formats (including Excel and Access).

    For me, Monarch provides the backbone for converting large amounts of printed report data into functional data. It may be worth your time to check it out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts