Importing text file with headers and page breaks 1

[UongSaki]

I'm trying to import text files into Access. Is there a way to import these files without having to manually clean them up first in Wordpad. These are huge files that are difficult to clean up in Wordpad. Here's a sample file that I copied from Wordpad and pasted here. Thank you

12/01/2003 Dynamic List Display 1
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
| DocumentNo|RefDocNo|Cost ctr|ActTyp|Cost elem.|Cost element name | ValueCOCur| Quantity|PUM|Postg date|Pers.no.|Partner object |Doc. date |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 301462585 |731778 |12000 |COUN05|600040 |Counsel | 47.16-| 0.300-|H |07/31/2003| 8542|P-0003.3.1.1 |07/01/2003|
| 301465504 |741128 |12000 |COUN10|600040 |Counsel | 122.87-| 1.000-|H |07/31/2003| 10929|P-0003.3.1.1 |07/09/2003|
| 301465505 |741134 |12000 |COUN10|600040 |Counsel | 92.15-| 0.750-|H |07/31/2003| 10929|P-0003.3.1.1 |07/11/2003|
| 301465518 |741137 |12000 |COUN10|600040 |Counsel | 61.44-| 0.500-|H |07/31/2003| 10929|P-0003.3.1.1 |07/14/2003|
| 301465684 |742446 |12000 |COUN10|600040 |Counsel | 122.87-| 1.000-|H |07/31/2003| 5508|P-0003.3.1.1 |07/07/2003|
------------------------------------------------------------------------------------------------------------------------------------------------------
|* | |12000 | | | | 15,080.37-| | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
12/01/2003 Dynamic List Display 2
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
| DocumentNo|RefDocNo|Cost ctr|ActTyp|Cost elem.|Cost element name | ValueCOCur| Quantity|PUM|Postg date|Pers.no.|Partner object |Doc. date |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 301466544 |745423 |14005 |CONT10|600045 |Contract Adm | 199.46-| 2.000-|H |07/31/2003| 9132|P-0243.3.1.1 |07/14/2003|
| 301466545 |745426 |14005 |CONT10|600045 |Contract Adm | 199.46-| 2.000-|H |07/31/2003| 9132|P-0243.3.1.1 |07/15/2003|
| 301466546 |745428 |14005 |CONT10|600045 |Contract Adm | 299.19-| 3.000-|H |07/31/2003| 9132|P-0243.3.1.1 |07/16/2003|
| 301466577 |745458 |14005 | |600045 |Contract Adm | 173.70 | 2.000 |H |07/31/2003| 11137|14005/CONT30 |07/09/2003|
| 301466578 |745460 |14005 | |600045 |Contract Adm | 347.40 | 4.000 |H |07/31/2003| 11137|14005/CONT30 |07/10/2003|
| 301466589 |745490 |14005 | |600045 |Contract Adm | 347.40 | 4.000 |H |07/31/2003| 11137|14005/CONT30 |07/17/2003|
| 301466590 |745492 |14005 | |600045 |Contract Adm | 347.40 | 4.000 |H |07/31/2003| 11137|14005/CONT30 |07/18/2003|
------------------------------------------------------------------------------------------------------------------------------------------------------
|* | |14005 | | | | 56,081.86-| | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
12/01/2003 Dynamic List Display 3
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
| DocumentNo|RefDocNo|Cost ctr|ActTyp|Cost elem.|Cost element name | ValueCOCur| Quantity|PUM|Postg date|Pers.no.|Partner object |Doc. date |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 301462256 |730534 |14044 |ACCT50|600099 |Misc Positions | 41.82-| 1.000-|H |07/31/2003| 10923|P-0003.3.1.1 |07/02/2003|
| 301464791 |739319 |82200 |SPEC10|600099 |Misc Positions | 308.72-| 8.000-|H |07/31/2003| 10804|P-0452.3.1.1 |07/18/2003|
------------------------------------------------------------------------------------------------------------------------------------------------------
|* | |82200 | | | | 117,901.88-| | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
12/01/2003 Dynamic List Display 45
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
| DocumentNo|RefDocNo|Cost ctr|ActTyp|Cost elem.|Cost element name | ValueCOCur| Quantity|PUM|Postg date|Pers.no.|Partner object |Doc. date |
------------------------------------------------------------------------------------------------------------------------------------------------------
|** | | | | | | 1,481,362.38-| | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------

 

[MichaelRed]

You will 'always' need to edit files of htis nature. You should not need to do so 'manually'. I have NO idea how 'proficient' you are in either SQL / database manipulation or VB (code), either/both can be used to 'groom' the information to the desired content.

To (BRIEFLY) outline a db/SQL approach:

The text file appears to consist of "fixed width" fields, so an "import/export" spec can be generated to delimit the fields. Note that ONE of the features of the import spec is to not import a field, so if you define the "|" character positions as fields and mark them as 'do not import', they will simply disappear. Next, the various liees of 'unwanted' stuff cna be removed by a group of Delete Queries, based on field(s) content. e.g. the lines consisting of "-" characters are easily identified by a where clause which is simply that character repeated for the specification field width. The header lines indicating the field names can similarly be removed, and other unwanted lines might be identified by one or more fields being filled with spaces.

When doing almost any bulk import, I recommend the use of one or more temporary tables. The first is simply a table with all text fields of the lengto appropiate to the information expected. The first step is to delete the content of the table (Delete * from tblTemp), followed by re-populating with the 'new' data set to be introduced. The series of delete queries mentioned above then cleans the 'trash' out of the basic input. Then, a set of V&V queries would check the remaining 'records' are properly formed for the eventual target, and ;records which fail these tests are removed to another temp table for eventual additional processing of the organization's choice (fix, return to origin for review/repair, or what-ever). finally each valid record is appended (or used to UPDATE) the target recordset.

The same general steps can be performed in code, although this is generally slower, it does give some additional control over individual record / field decisions.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[UongSaki]

I'm not proficient with SQL nor VB. I tried to follow your suggestion but there were too many ImportErrors, all were "Type Conversion Failure."

Thanks

 

[MichaelRed]

While I am not at all confident that the 'real' files will follow the example, the following procedures will create a New file from, the sampe you posted in a "Pipe" delimited format. From this, you should be able to simply use the: File => Get Exter Data => Import to generate hte import specification (using the "Pipe" symbol as the delimiter) and retrieve the date. the first function requires the name of the file with the original info, and needs a (hopefull new) file name to place the record lines into.

See the sample usage, but (of course) you need to use the path and file names for your installation.

The second function is just a 'helper' which grabs a file and places the entire content into a single string variable and returns it to the calling function.

Public Function basImprtTmp(FilIn As String, MyOut As String) As Boolean

    'Sample Usage:
    '? basImprtTmp("C:\My Documents\MsAccess\Txt2Imprt.Txt", "C:\My Documents\MsAccess\RecTxt2Imprt.Txt")


    Dim FilOut As Integer
    Dim Idx As Long
    Dim Jdx As Long
    Dim MyIn As String
    Dim MyStr() As String
    Dim MyNewStr() As String

    MyIn = basGrabFile(FilIn)       'Get the WHOLE File

    MyStr() = Split(MyIn, vbCrLf)   'Split into Individual Lines

    ReDim MyNewStr(0)
    While Idx <= UBound(MyStr)      'Remove Non-Records

        If (Left(MyStr(Idx), 1) <> &quot;|&quot;) Then    'Record Lines have &quot;Pipe&quot; in First Position
            GoTo NextRec
        End If

        If (Mid(MyStr(Idx), 2, 1) = &quot;*&quot;) Then    'Subtotal Lines have &quot;*&quot; in Second Position
            GoTo NextRec
        End If

        If (Mid(MyStr(Idx), 2, 11) = &quot; DocumentNo&quot;) Then    'Subtotal Lines have &quot;*&quot; in Second Position
            GoTo NextRec
        End If

        MyNewStr(Jdx) = MyStr(Idx)
        Jdx = Jdx + 1                       'Incr New Record Pointer
        ReDim Preserve MyNewStr(Jdx)        'Enlarge New Record Array

NextRec:
        Idx = Idx + 1
    Wend

    ReDim Preserve MyNewStr(Jdx - 1)            'Loose last (Empty) Line

    FilOut = FreeFile
    Open MyOut For Output As #FilOut           'Get a file to write to
    Jdx = 0                                     'Reset Index
    While Jdx <= UBound(MyNewStr)               'Loop to Output
        Print #FilOut, MyNewStr(Jdx)
        Jdx = Jdx + 1
    Wend
    Close FilOut

    basImprtTmp = True

End Function

Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim MyFil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    MyFil = FreeFile

    Open FilIn For Binary As #MyFil

    MyTxt = String(LOF(MyFil), " ")
    Get #MyFil, 1, MyTxt

    Close #MyFil

    basGrabFile = MyTxt

End Function
[code]

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[UongSaki]

Thank you very much for your help. I'm still trying to make sense of the codes you've written. It's learning a new language for me. I'll let you know how much I understand your codes soon. Again, thank you!

Uong Saki