Importing a Text Files (List) into access

[TonyLackey]

Hi,

I have the following Text file that can be extracted from our legacy system (see example below..) but it is extracted as a list. Is it possible to import this in such a way so that it is in a standard table foramt (accross)?

Thanks in advance

T

C0_1000000ð860001
C1_*** M/T "SHIP" ***
C8_1166
C30_1166
C0_1010000ð860001
C1_*** DRAWINGS & MANUALS
C8_1166
C30_1166
C0_1010100ð860001
C1_MACHINERY DRAWINGS/MANUALS
C8_1166
C30_1166
C0_1010200ð860001
C1_ELECTRICAL DRAWINGS/MANUALS
C8_1166
C30_1166
C0_1010300ð860001
C1_HULL DRAWINGS/MANUALS
C8_1166
C30_1166
C0_2840000ð860001
C1_*** CARGO TANKS
C0_2840110ð860001
C1_CARGO OIL WING TANK No1 - STBD
C2_15,436 m3/98% 15,127
C3_F: 95 - 105 / S
C8_1166
C12_1
C15_C704
C16_M12
C17_0M19960101
C19_D
C21_G1
C30_1166
C0_2840120ð860001
C1_CARGO OIL TANK No1 - CNTR
C2_33,190 m3/98% 32,526
C3_F: 95 - 105 / C
C8_1166
C12_1
C15_C704
C16_M12
C17_24M19960124
C19_D
C21_G1
C30_1166
C0_2840130ð860001
C1_CARGO OIL WING TANK No1 - PORT
C2_15,436 m3/98% 15,127
C3_F: 95 - 105 / P
C8_1166
C12_1
C15_C704
C16_M12
C17_24M19960124
C19_D
C21_G1
C30_1166
C0_2840210ð860001
C1_CARGO OIL WING TANK No2 - STBD
C2_21,925 m3/98% 21,487
C3_F: 85 - 95 / S
C8_1166
C12_1
C15_C704
C16_M12
C17_0M19960101
C19_D
C21_G1
C30_1166
C0_2840220ð860001
C1_CARGO OIL TANK No2 - CNTR
C2_33,152 m3/98% 32,489
C3_F: 85 - 95 / C
C8_1166
C12_1
C15_C704
C16_M12
C17_24M19960124
C19_D
C21_G1
C30_1166
C0_2840230ð860001
C1_CARGO OIL WING TANK No2 - PORT
C2_21,925 m3/98% 21,487
C3_F: 85 - 95 / P
C8_1166
C12_1
C15_C704
C16_M12
C17_24M19960124
C19_D
C21_G1
C30_1166
C0_2840310ð860001
C1_CARGO OIL WING TANK No3 - STBD
C2_15,348 m3/98% 15,041
C3_F: 75 - 82 / S
C8_1166
C12_1
C15_C704
C16_M12
C17_0M19960101
C19_D
C21_G1
C30_1166
C0_2840320ð860001
C1_CARGO OIL TANK No3 - CNTR
C2_33,001 m3/98% 32,341
C3_F: 75 - 85 / C
C8_1166
C12_1
C15_C704
C16_M12
C17_24M19960124
C19_D
C21_G1
C30_1166

 

[___]

Import your text file into Excel, use the following code in the click event of a button in Excel to convert column A to a single row.

dr = 118 'how many data rows

For x = 2 To dr
    Range(Range("A" & x), Range("A65536").End(xlUp)).Copy Destination:=Cells(1, x)
Next x

For x = Range("A65536").End(xlUp).Row To 1 Step -1
    If x Mod dr <> 1 Then Range("A" & x).EntireRow.Delete
Next x

HTH

 

[namliam]

I am guessing every C0_ is the start of a new record?

In what kind off table would it end up ?

Regards

 

[TonyLackey]

I am guessing every C0_ is the start of a new record?

In what kind off table would it end up ?

Regards

*0_ is always the begining of the initial data set.
C = Component Table

However, if there is no data contained in a field, then the representing column ID is not exported... (reduces file size).

 

[namliam]

I will throw something together later today for you, should not be to hard....

Regards

 

[TonyLackey]

Thanks

I will throw something together later today for you, should not be to hard....

Regards

That would be so useful. Appreciate if you could, i can then modify for other tables etc...

Thanks

 

[namliam]

If the numbers are the fieldnumbers try this:

Sub importMyc()
    Dim myString As String
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Table1")
    Open "P:\Billing\test.txt" For Input As #1
    Line Input #1, myString
    Do While Not EOF(1)
        If Left(myString, 2) = "C0" Then
            On Error Resume Next
            rst.Update
            On Error GoTo 0
            rst.AddNew
        End If
        rst.Fields(Mid(myString, 2, InStr(1, myString, "_") - 2)) = Mid(myString, InStr(1, myString, "_") + 1)
    
        Input #1, myString
    Loop
    rst.Update
End Sub

If you fiddle with it a little you should be able to get it working....

Regards

 

[TonyLackey]

Dim rst As DAO.Recordset

Namliam,

Thanks for your input....

Having the following error
"User defined Type not Defined"

As you can guess i'm not a VB programmer. Can modify code and fairly useful at using Access.

Any advise appreciated....

T

If the numbers are the fieldnumbers try this:

Sub importMyc()
    Dim myString As String
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Table1")
    Open "P:\Billing\test.txt" For Input As #1
    Line Input #1, myString
    Do While Not EOF(1)
        If Left(myString, 2) = "C0" Then
            On Error Resume Next
            rst.Update
            On Error GoTo 0
            rst.AddNew
        End If
        rst.Fields(Mid(myString, 2, InStr(1, myString, "_") - 2)) = Mid(myString, InStr(1, myString, "_") + 1)
    
        Input #1, myString
    Loop
    rst.Update
End Sub

If you fiddle with it a little you should be able to get it working....

Regards

 

[___]

Make sure you have a reference set for Microsoft DAO object library. Open the form in design view, goto View >> Code then Tools >> References and select it from the list. Click on Priority until it reaches third in the list.

 

[TonyLackey]

That worked, but next problem

Cheers,

That worked, but now it crashes at following line

rst.Fields(Mid(myString, 2, InStr(1, myString, "_") - 2)) = Mid(myString, InStr(1, myString, "_") + 1)

Will work on it, may ask for help later

Thanks

 

[namliam]

What is the value of myString?

Goto the debug window (CTRL + G)
type ?mystring<hit enter>

Regards