Importing Repeating Row Record Text File Data into ACCESS? (1 Viewer)

[mr_fish]

Hi all,

Done a bit of research on this already, but not sure if I'm heading in the right direction. I regularly get a text file that is exported from another system that comes out in rows with records repeating one after the other. The way the other system exports data can't be altered so I've got to find a way of getting massive text file row records into columns to import into an access database. The data comes out as shown below: -

Name U78E-8 .net
Advertising Rates Full Page Colour GBP 2850.00
Mechanical Data Type Area 265 x 215mm
Bleed Size 286 x 238mm
Trim Size 280 x 232mm
Col Length 265mm
Film Digital
Page Width 215mm

Name U78E-5 3D World
Advertising Rates Full Page Colour GBP 2289.00
Mechanical Data Page Width 215mm
Type Area 265 x 215mm
Col Length 265mm
Film Digital

Name U78E-9 Computer Arts
Advertising Rates Full Page Colour GBP 2470.00
Mechanical Data Page Width 190mm
Film Positive, right reading, emulsion side down
Trim Size 297 x 210mm
Type Area 272 x 190mm
Col Length 272mm
Screen 60 lpc


The only records I need to be listed in separate colums are name code i.e. U87E-8, advertising rates as currency only and mechanical data - type area, e.g. 265 x 215 without the mm. So they'll appear delimited in some way

U87E-8 2850.00 265 x 215
U78E-5 2289.00 265 x 215
U78E-9 2470.00 272 x 190

What I've read so far rules out the transfer text function as the data isn't column delimited for it to work (correct me if I'm wrong). Some threads have mentioned parsing text files using VBA to extract the data, but I'm not entirely sure how to do this, but I'll kick it off here for your amusement if nothing else.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim buffer As String

Set dbs = CurrentDb

Open "c:\testdata.txt" For Input As #1
While Not EOF(1)
Line Input #1, buffer
If InStr (1, buffer, "Name") = 1 then ' trying to identify when a certain word occurs then parse the data after it, but not sure how to yet


Is there a program, vba code or template that anyone can point me to to help. Any assistance as always is greatly appreciated. Thanks

 

[Pat Hartman]

There is code posted here that can be used as an example. Try searching for things like "For Input As" and "Line Input #".

 

[mr_fish]

Attempting to write code

OK have found some code that I've adapted and ran it on the attached text file. As a first step I just want to read the whole line in (I'll work out how to trim it down later) the only problem is that it adds only 8 records of the 25 text file entries into the database for some reason. Code I'm using below and .txt file I'm using is attached. If I work out the issue I'll update this thread.

Private Sub Command0_Click()
Dim dbs As DAO.Database, rst1 As DAO.Recordset
Dim StRomFile As String
Dim stLineBuffer As String
Dim stAdRate As String, stMediaCode As String, stPageSize As String
Dim TimeA As String, TimeB As String

TimeA = Time ' marks beginning time

StRomFile = "c:\medialist.txt" ' location of the text file
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("MAGAZINE_DATA")

' Delete existing tables
dbs.Execute "DELETE * FROM MAGAZINE_DATA", dbFailOnError

Close #1 'just in case code is restarted after being interrupted before completion

Open StRomFile For Input As #1

Do Until EOF(1)
Line Input #1, stLineBuffer ' line pulled into string
'First check to see if line contains "Name"
If InStr(1, stLineBuffer, "Name") Then
stMediaCode = stLineBuffer
Line Input #1, stLineBuffer ' next line pulled into string
'Next check to see if next line contains "Colour"
If InStr(1, stLineBuffer, "Full Page Colour") Then
stAdRate = stLineBuffer ' next line pulled into string
Line Input #1, stLineBuffer
'Check to make sure next line reads "Type Area"
If InStr(1, stLineBuffer, "Type Area") Then
stPageSize = stLineBuffer
With rst1
.AddNew
!MEDIA_CODE = stMediaCode
!MEDIA_AD_COST = stAdRate
!MEDIA_PAGE_SIZE = stPageSize
.Update
End With

End If
End If
End If
Loop

Close #1
rst1.Close

TimeB = Time
MsgBox (TimeA & TimeB) ' Displays elapsed time to run the code

End Sub

 

[Bat17]

The way you have it, if the rows are not consecutive it breaks the loops. try it this way

Do Until EOF(1)
    Line Input #1, stLineBuffer ' line pulled into string
    'First check to see if line contains "Name"
    If InStr(1, stLineBuffer, "Name") Then
        stMediaCode = stLineBuffer
    ElseIf InStr(1, stLineBuffer, "Full Page Colour") Then
        stAdRate = stLineBuffer ' next line pulled into string
    ElseIf InStr(1, stLineBuffer, "Type Area") Then
        stPageSize = stLineBuffer
        With rst1
'        Debug.Print stMediaCode; stAdRate; stPageSize
                .AddNew
                !MEDIA_CODE = stMediaCode
                !MEDIA_AD_COST = stAdRate
                !MEDIA_PAGE_SIZE = stPageSize
                .update
        End With
    End If
Loop

This assumes that the rows will always be in the same order but don't need to be sequential

HTH

Peter

 

[mr_fish]

Thanks Peter that works a treat.

Been looking into trimming down the text so only the relevant bits get put into the database.

I've been able to use the trim, mid and left/right functions to chop out the crap before and after the fields so instead of this

Name U78E-8 Computer Weekly
Full Page Colour GBP 4125.00
Mechanical Data Type Area 280 X 270mm

it looks like this

U2A-66 Computer Weekly
4125.00
280 X 270

The only problem I have now is extracting the Media code (U2A-66) and Magazine Name (Computer Weekly) from each other as the code and mag name lengths change and all that is between them is a single space.

I was thinking that it would be possible to count the characters up to the first space and then put this figure into a function to chop that bit into a string variable. Not sure how I'd go about it though, how would I detect a space?

 

[mr_fish]

Solved! Found out that I can set up a Long variable (iPos in this case) and pass the character number at which the space occurs to it using InStr function.

iPos = InStr(1, stMediaCode, " ")
stMediaCode = Left(stMediaCode, iPos)

 

[Bat17]

Glad you have it sorted

Peter