Text file to Table help (1 Viewer)

[stevek]

Hi,
How do I get group headers, form a text file to a table, to be listed in the same row as the information directly below the header?
I have a text file from another program that I need to import to access. The file has group headers with Tail numbers that I need to be associated with the part and serial numbers below it. The amount of lines below each header is not always the same. I know how to extract either the aircraft number or the part serial number, but I do not know how to put the header with the part and serial number
for instance

Aircraft N3456

456723-101 FD34566
566788-303 GS23445

Aircraft N5684

566788-303 GS23445
456723-101 GD23478
566788-303 GS45444
434545-444 JI345345

How do i get the above information to look like this in table?


N3456 456723-101 FD34566
N3456 566788-303 GS23445
N5684 566788-303 GS23445
N5684 456723-101 GD23478
N5684 566788-303 GS45444
N5684 434545-444 JI345345

Thank you very very much
Steve

 

[jdraw]

General approach.

Read all the data into a single table in Access. let's call it MyTempInput.

Create a table layout (for discussion tblAircfraft) for the required output - something like

TailNumber
PartNumber
SerialNumber

You know that the word "Aircraft" is in the header, and immediately following that record, until the next Header record is the parts and serial numbers for that Aircraft.

So create a loop mechanism ( rough pseudo code)

Read MyTempInput
If header  record then put the Aircraft identifier in a variable (say PlaneIdent)

if all spaces, go to read MyTempInput
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 MyTempInput

That's the general pattern and should get you started.

When you have added a few records then get a new Header, you have to add the last record for the current aircraft, then update the value in PlaneIdent, then read the next record from MyTempInput
Good luck with your project.

 

[stevek]

Thanks,

I will try when I get back to work tomarrow.

Steve

 

[stevek]

Hey, Tried to access this from work and the firewall won't let me so I will try again tomorrow. This time I will print this out. I am not familiar with VBA but I think I understand what you are telling me.

 

[jdraw]

Can you post a file ( YourData.txt) showing more of your data?
And which piece is serialnumber and which is partNo

 

[jdraw]

Steve,

Please see
http://www.access-programmers.co.uk/forums/showthread.php?p=1220069#post1220069 for an update to this thread. I got the posts mixed up somehow -- probably the SteveK.

Good luck with your project.

 

[stevek]

wow

Thank you so much. I will try this. again thanks

 

[stevek]

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.

 

[jdraw]

When it finds a header record, it does a check in line 90, and if it is a header
it assigns PlaneIdent in line 100.
That value in PlaneIdent stays there until a new header is found and refreshes PlaneIdent. Line 110 is there to remove any leading or trailing spaces, if they are present.

180-210 assign values to the new fields and update the rsOut.

'---------------------------------------------------------------------------------------
' 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
'--------------------------------------------------------------------------
'
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
      '
      'tblAircraft structure is
      'id  autonumber PK
      'TailNumber
      'PartNumber
      'SerialNumber
      '
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
      '
      '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

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

 

[stevek]

got it. thank you so very much.