Convert table with only one column into a table with several

[kiplook]

I have a machine that spits out data like this:

C00 28894
C01 0805-05
C02 Res, 10_ohm
C03 FALSE
C08 8mm
C081 TAPE_MAG
#
C00 28897
C01 0805-05
C02 Res. 1k 1/8w 1%
C03 TRUE
C08 8mm
C081 TAPE_MAG
#
C00 28898
C01 0806-05
C02 Res, 2.21K_ohm
C03 FALSE
C08 8mm
C081 TAPE_MAG
#
C00 28899
C01 0805-05
C02 Res 2.5 Kohm
C03 FALSE
C08 8mm
C081 TAPE_MAG
#


Using MS Access, I'd like to convert it to a table with the column headings:

C00,C01, C02, C03, C08, and C081

 

[ezfriend]

Since the fields are always going to be the same, it is easier to just create the table and then read the data in.

 

[kiplook]

Ok. How do I do that?

 

[gemma-the-husky]

i think you don't realy want to do it that way exactly.

what exactly do the values in C00, C01, C02 etc represent.

analysing this data is the key to your solution

 

[kiplook]

I don't have any control over how the data comes to me. I will be using C00 as the key and looking at C01, C02, and C08 for sure.

C00 is a part number and should be unique
C01 is the package type of the part
C02 is a description of the part
C03 I don't know what this is - I'm not using it
C05 I don't know what this is - I'm not using it
C06 I don't know what this is - I'm not using it
C07 Same as C00 - don't ask why
C08 This is the size of the tape that the part is on
C081 This tells me that is is on tape

 

[DCrake]

Is this going to be a once only event or are you going to be doing this on a regular basis. The reason for asking is that you either want a quick fix or a more sophisticated solution. What type of file is the source data being generated into?

David

 

[kiplook]

This is a recurring process.

I believe the list I gave you is called a component list. There is also a magazine list, a feeder list and a few others that I can't remember right now.

The source data is just a text file. They are cleverly named:

cmp.cmp
mag.mag
fdr.fdr

They all follow the same basic format. I chose the easiest to start with but being as you're asking, from here it does get more complicated. Here's a sample of the Magazine list:

M10 23 28262 -86102 "A-12-mm" ""
M15 0 -1
M20 0 false 12mmtape 90000 0 "32148" "" 0
M30-TM 15642 1
M20 1 false 12mmtape -90000 2462 "32148" "" 50
M30-TM 35816 1
M20 2 false 12mmtape 90000 1236 "32109" "" 50
M30-TM 55464 2
M20 3 false 12mmtape -90000 1770 "28931" "" 50
M30-TM 75906 2
M20 4 false 12mmtape -90000 0 "32621" "" 0
M30-TM 96250 2
M20 5 false 12mmtape -90000 1919 "31967" "" 0
M30-TM 116101 2
M20 6 false 12mmtape 180000 67 "31921" "" 50
M30-TM 136044 2
M20 7 false 12mmtape 90000 0 "34172" "" 0
M30-TM 155991 3
#
M10 23 40971 -86866 "A-12-mm-M" ""
M15 0 -1
M20 0 false 12mmtape
M30-TM 16034 1
M20 1 false 12mmtape
M30-TM 36281 1
M20 2 false 12mmtape
M30-TM 56329 2
M20 3 false 12mmtape
M30-TM 76740 2
M20 4 false 12mmtape
M30-TM 96429 2
M20 5 false 12mmtape
M30-TM 116641 2
M20 6 false 12mmtape
M30-TM 136737 2
M20 7 false 12mmtape
M30-TM 156505 3
#

Where:

M10 Is identification for the magazine
M15 Is the slot where the magazine resides (-1 means that it's not inserted)
M20 Slot number and feeder type
M30 Feeder identification number

So for every M10 you will have several M20s and M30s. The table headings would be M10, M15, M20, and M30 with no unique key.

 

[DCrake]

I think for this you would have to use the Open file for Input #1 methodology. By reading each line one at a time and evelauating its contents before deciding which field the row belongs to. Are you familiar with this VBA?

David

 

[kiplook]

No I'm not familiar with Open file for Input #1. I should point out that I'm using MS Access 97.

 

[petehilljnr]

I did a little bit of code (haven't tested) that you can alter for the other tables. I based it on your first set of data (C00, C01, ... C081).

If you make a table called "MyTable" with your fields (just as text fields for now to test) C00, C01, ... C081, then alter the code strFile = "C:\xxxxx.xxx" to be a test file then give it a run.

It kinda gives you an idea where the solution could lie.

Sub read_file()
Dim rst As dao.Recordset
Dim strFile As String
Dim strLine As String
Dim blnNew As Boolean
Dim strField As String
 
Set rst = CurrentDb.OpenRecordset("[COLOR=red]MyTable[/COLOR]")
 
strFile = "C:\[COLOR=red]xxxxx.xxx[/COLOR]"
 
blnNew = True
 
Open strFile For Input As #1
    Do While Not EOF(1)
 
        If blnNew Then
 
            rst.AddNew
            blnNew = False
 
        End If
 
        Line Input #1, strLine
 
        If Trim(strLine) = "#" Then
 
            rst.Update
            blnNew = True
 
        Else
 
            strField = Trim(Left(strLine, 4))
 
            rst(strField) = CStr(Trim(Right(strLine, Len(strLine) - 4)))
 
        End If
 
    Loop
 
rst.Close
Close #1
 
End Sub

 

[kiplook]

Wow!

That worked perfectly without altering it at all.

Thanks for the help.

 

[petehilljnr]

It won't work for your other files though. You'll need to figure out a similar routine