Pivot a 70 column table into a one column TXT file

[carfj1]

I have to make a Transfer MDB file form One Database system to another:

Original table column names are numbersaprox 5000 records)
Original Table
1 2 3 ... 68 69 70
Mgw150 PrNm1 2.0000 ... 1.0000 Null Null
Mgw150 PrNm2 4.0000 ... 1.0000 Null Null
.
.
.


The final output is should be a txt file as:

Mgw150
PrNm1
2.0000
4.0000
.
.
.
1.0000
1.0000
Null
Null
Mgw150
PrNm1
2.0000
4.0000
.
.
.
1.0000
1.0000
Null
Null

In orther to make this I have done the following steps:
1.- Append first record from original table to a one record table (Datos_Externo).
2.- pivot the table using union querys:

SELECT [1].Datos_externo.[1] AS 1, [1].[1] AS 1b, [1].[1] AS Orden
FROM 1
WHERE ((([1].[1])=1));
Union


SELECT [2].[2], [2].[1],2 as orden
FROM 2
WHERE ((([2].[1])=1));
union

SELECT [3].[3], [3].[1],3 as orden
FROM 3
WHERE ((([3].[1])=1));
union

SELECT [4].[4], [4].[1],4 as orden
FROM 4
WHERE ((([4].[1])=1));
UNION

SELECT [5].[5], [5].[1],5 as orden
FROM 5
WHERE ((([5].[1])=1));
Union

SELECT [6].[6], [6].[1],6 as orden
FROM 6
WHERE ((([6].[1])=1));
UNION
...
until 70

3.- Append first column from this union Query in an exporting table.

4.- Delete and backup from original table the first row.
5.- Cicle steps 1~4 until no record is on Original Table
6.- Export exporting Table.

Now the questions:

This process works well for 10 columns will it work for 70?
Will it be to slow, if so how can it be speed it up?

Can someone help me with a code to automate the Union query in a loop. (I am starting to learn VB on access still an amprentiz on this I am))

I made up this process reading various posts on this forums, thank you all for it.

Carlos

 

[MichaelRed]

It may work for 70 columns. It WILL be slow. If it is a one time process it won't really be soooooo sloooooooooow that you can't just do it anyway.


It will become QUITE tedious to do the process in the 70 (or so queries).

A simple VBA loop would be my choice and, i believe be no slower than all of the queries.

Something like:

Public Function basPiviot2_1col()

    'Michael Red    1/24/2003
    'Tek-Tips thread181-457133 for carfj1

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim MyTxtFil As String
    Dim FilHdl As Integer
    Dim Idx As Integer

    Dim MyFldVal As Variant

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblLoc", dbOpenDynaset)

    MyTxtFil = "C:\MsAccess\TestPiviot.Txt"
    FilHdl = FreeFile
    Open MyTxtFil For Output As #FilHdl

    While Not rst.EOF
        Idx = 0
        While Idx <= rst.Fields.Count - 1
            Print #FilHdl, rst.Fields(Idx).Value
            Idx = Idx + 1
        Wend
        rst.MoveNext
    Wend

    Set rst = Nothing
    Set dbs = Nothing

    Close #FilHdl

End Function

of course, some parts of this are quite arbitrary. the table name and the file path/name are just selected to provide SOME pratical and verifiable results on my system.

there is NO error protection or trapping, which would be at least useful

many other fine points are perhaps beyond ny simple understanding

on the other hand is does generate a text file output of the table (actually RECORDSET) content. the source table does not include any null values, so you probably need to explicitly test for the NULL and actuall output the string "NULL" in that instance. there are other transforms from the table format of the data to DESIRED output which you may also need to consider.


Now, that this is all so neatly 'tied togerther', you really should consider what the )(*&@$#*#& you are being asked to do. ANY reasonable db would at least accept some format which is easier to generate, such as spreadsheet, fixed width text, comma seperated values ...

So, the perspective here is that SOMEONE doesn't understand the job or process. this,loosely translates to you should be asking A LOT of very POINTED questions. INSIST on discussing the transfer with someone directly responsible for the ohter side of the activity. THE guy who will actually read the file and put into the "next" format. Find out what HE knows and wht HE needs or can use.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places