Table From pasted txt file (1 Viewer)

stevek · Dec 22, 2012

Hi,
I am fairly new to access please help. I have a report from another data system that is a text file that I paste into a table then I have a query sort the information out. In the text file there is a header with information that I could use and make other tables work better but I need to have a piece of information from the header be attached to the lines of text directly below.
So, how do I take that header information and put it with a varied amount of line text below.
example txt

header 1234 someinformationthatIneedtouse

4566655 jon smith 12/11/2012
6545666 dave jones 12/13/2012

header 1783 someinformationthatIneedtouse

2333333 sammy davis 12/23/2012
.....

I am looking for the data to show up in the table like so

1234 4566655 jon smith 12/11/2012
1234 6545666 dave jones 12/13/2012
1783 2333333 sammy davis 12/23/2012

Thanks

JHB · Dec 26, 2012

Could you show some raw data from the text file and also how the data in your table looks like?

Pat Hartman · Dec 26, 2012

You cannot do this with TransferSpreadsheet or TransferText because you have rows of different formats. You will need to use VBA to automate Excel to read each line and determine its format. You would then add rows to your tables.

stevek · Dec 28, 2012

I was really hoping not to use VBA. I can’t post the exact data but here is a sample. I only used two groups of data but there is more and it varies day to day how many groups and how many items per group there is.

Thanks for helping

Steve

jdraw · Dec 28, 2012

You are working with processed data (from a report). Can you get the original data?
Someone working with the original data should be able to build some tables to supply you with data in a more useable form.

stevek · Dec 28, 2012

No I can't get access to the data other than in the proccessed reports. The system we use was desgined a long time a go. I think the system is a old unix type of program. Most of the stuff we have to do in that system requires us to type in a line of code to get our reports. they are updating it so we can have more of a GUI but it is a work in progress. Even when they are done with that the reports will look the same.

Thanks

Steve

jdraw · Dec 28, 2012

This post is really in response to
http://www.access-programmers.co.uk/forums/showthread.php?t=239095
Somehow I got the posts mixed up for poster SteveK

Steve,
I had a bit of time so I took the records from your original post and made a text file.

I imported that data into a table called AircraftRaw with1 field named "fld'. This is the temp table(new name) I referenced earlier.

I wrote a small proc based on the rough outline I posted to process the data in the temp table. The proc(below) reads table AircraftRaw, checks each record, and writes the record to the immediate window. The code the parses each record and adds records to the output table (tblAircraft) in the format you suggested.
There is a count of the records read and the records written to the new table written to the immediate window at the end of processing.

The attached jpg shows the temp table Aircraftraw (rawdata) and the final table tblAircraft.

This should serve as a model to get your raw data into the format you are seeking. Good luck with your project.

Code:

'---------------------------------------------------------------------------------------
[COLOR="Green"]' Procedure : air_manipulation
' Author    : Jack
' Date      : 27-12-2012
' Purpose   : To read raw text that was input to a temporary table (AircraftRaw)
' and to parse each record in the temporary table as outlined in the psuedo code
' and to out put the Tail number, part number and serial number into a strucured table
'called (tblAircraft) which has the following structure
'
'  'tblAircraft structure is
'      'id  autonumber PK
'      'TailNumber
'      'PartNumber
'      'SerialNumber
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'[/COLOR]
Sub air_manipulation()
      Dim rs As DAO.Recordset  'incoming raw record
      Dim db As DAO.Database
      Dim PlaneIdent As String 'tail Number
      Dim PartNo As String     'part number
      Dim serialNo As String   'serial number
      Dim rsOut As DAO.Recordset
      Dim iRawReads As Integer  'count input records
      Dim iOutRecords As Integer 'count output records

10       On Error GoTo air_manipulation_Error

20    Set db = CurrentDb
30    Set rs = db.OpenRecordset("AircraftRaw")  'raw text file
40    Set rsOut = db.OpenRecordset("tblAircraft") ' desired output structure
      [COLOR="Green"]'
      'tblAircraft structure is
      'id  autonumber PK
      'TailNumber
      'PartNumber
      'SerialNumber[/COLOR]
      '
50    Do While Not rs.EOF
60    iRawReads = iRawReads + 1
70    Debug.Print "record  " & iRawReads & "  " & Nz(rs!fld, "NULL") 'for debugging
80      If IsNull(rs!fld) Then GoTo exitIf
90        If InStr(rs!fld, "aircraft") > 0 Then
              'this is a header record
100           PlaneIdent = Mid(rs!fld, InStr(rs!fld, "aircraft") + 8)
110           PlaneIdent = Trim(PlaneIdent)
             ' Debug.Print PlaneIdent         'for debugging
          
120       Else
130         rsOut.AddNew
140           PartNo = rs!fld
150           PartNo = Mid(rs!fld, 1, InStr(rs!fld, " ") - 1) 'left side of rs!fld
160           serialNo = Mid(rs!fld, InStr(rs!fld, " ") + 1) 'right side of rs!fld
170           Debug.Print "       output-->>>> " & PlaneIdent & " " & PartNo & " " & serialNo
180           rsOut!TailNumber = PlaneIdent
190           rsOut!PartNumber = PartNo
200           rsOut!SerialNumber = serialNo
210          rsOut.Update
220          iOutRecords = iOutRecords + 1
230       End If
exitIf:
240   rs.MoveNext
250   Loop
260   rs.Close
270   rsOut.Close
280   Debug.Print "recs read    " & iRawReads
290   Debug.Print "recs written " & iOutRecords
     [COLOR="Green"] '
      'pseudo code
      'Read AircraftRaw
      'If header  record then put the Aircraft identifier in a variable (say PlaneIdent)''

      'if all spaces, go to read AircraftRaw
      'if not space, then parse the data to part and serial
      '  Add a record to tblAircraft using
      '       TailNumber = planeIdent
      '       PartNumber = parsed part value
      '       SerialNumber = parsed serial number
      'GO to READ AircraftRaw[/COLOR]

300      On Error GoTo 0
310      Exit Sub

air_manipulation_Error:

320       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure air_manipulation of Module Module1"
End Sub

Here is the info from the Immediate window

Code:

record  1  Aircraft N3456
record  2  NULL
record  3  456723-101 FD34566
       output-->>>> N3456 456723-101 FD34566
record  4  566788-303 GS23445
       output-->>>> N3456 566788-303 GS23445
record  5  NULL
record  6  Aircraft N5684
record  7  NULL
record  8  566788-303 GS23445
       output-->>>> N5684 566788-303 GS23445
record  9  456723-101 GD23478
       output-->>>> N5684 456723-101 GD23478
record  10  566788-303 GS45444
       output-->>>> N5684 566788-303 GS45444
record  11  434545-444 JI345345
       output-->>>> N5684 434545-444 JI345345
recs read    11
recs written 6

stevek · Dec 29, 2012

This code worked perfectly. I don't understand where it says; to use this TailNumber for the next lines untill the next TailNumber is found. I will do some searching though.
This is a big HELP to me. Thank you very much.

Table From pasted txt file (1 Viewer)

stevek

Registered User.

JHB

Have been here a while

Pat Hartman

Super Moderator

stevek

Registered User.

Attachments

jdraw

Super Moderator

stevek

Registered User.

jdraw

Super Moderator

Attachments

stevek

Registered User.

Similar threads

Users who are viewing this thread