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
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