Pivot a 70 column table into a one column TXT file 1

[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

 

[JoanneM]

Hello

happened to have done something a little similar so I've ammended the code
there are 3 simple stages

1
copy the code below into a module




Option Compare Database
Option Explicit
Sub mkTXTList(objname As String, fname As String)
On Error GoTo mkcsverr
Dim mydb As Database
Dim myrs As Recordset
Dim fldcount As Integer
Dim fld As Field
Dim x As Integer
Dim printstr As String
Dim fs As Object
Dim a As Object

Set mydb = CurrentDb
Set myrs = mydb.OpenRecordset(objname, dbOpenDynaset)

'Dim fname As String
'fname = Forms!frmexporttocsv!txtfilename & ".txt"
fname = fname & ".txt"

Set fs = CreateObject("Scripting.FileSystemObject&quot
Set a = fs.CreateTextFile("c:\" & fname, True)

a.Close

DoCmd.Hourglass True
'test line below for manual naming
' Open "c:\mycsv.txt" For Output As #1
Open "c:\" & fname For Output As #1

Do Until myrs.EOF
For x = 0 To myrs.Fields.Count - 1
printstr = ""
printstr = printstr & myrs.Fields(x).Value & vbNewLine

Print #1, printstr

Next x
myrs.MoveNext

Loop
Close #1
DoCmd.Hourglass False
'MsgBox "Text file " & fname & "created to " & PathName

mkcsverrexit:
MsgBox "done"
Err = 0
Exit Sub


mkcsverr:
MsgBox Error$(Err)
GoTo mkcsverrexit
End Sub




2
copy this line into the immediate window of your module
To activate this window Ctrl+G

call mkTXTList("yourTablenameHere","ListFldVals&quot

3 replace yourTablenameHere with the name of the 70 field table

press enter

the code will run
a msgbox Done will appear at the end
your file is stored on C:\ and called ListFldVals.txt

Note:

I did this with a 77 field table I had laying around and it worked but I am almost sure you will exceed notepads maximum limit. Nothing venture nothing gained - give it a go.

I'm off for my Friday night tipple but I'll take the mute off so my email will ping if you need any further help

regards

Jo

 

[carfj1]

Thanks this is what I need, just two questions:

1
On the command

DIM mydb as Database

results in an error:
Compile Error
Variable not define

How can I define this variable?

2
How can I put on a command button the line:
call mkTXTList("Datos_exportados",Archivo.txt&quot

Thank in advance your help is apreciated.

 

[JoanneM]

sorry to have kept you waiting

in your module goto Tools on the menu
select references
in this window you need to seled MICROSOFT DAO 3.6

Next you need to select REFERENCES AGAIN & MAKE SURE THAT THE DAO REFERENCRE IS ABOVE THE ADO REFERENCE.

THE LITTLE UP & DOWN ARROWS AT THE SIDE MOVE ANY REFERENC HIGHLIGHTED ACCORDINGLY.
THIS WILL TAKE CARE OF THE DATABASE PROB

2


IN DESIGN VIEW OF YOUR FORM

CLICK ON THE cmdButton
right mouse click
select properties from the pop up menu
select the Events tab
in the ON click property copy your line

3
JOB Done

Morning and happy printing
NB I would STRONGLY suggest that you run a test to check all the data can be stored in a txt file

To do this

create a new database

import the table, form and module
put a dummy record in a copy of your table

do the references again (modules)
and add a dummy record right at the end and use this as a check.
when you have run the code - in your txt file in notepad make sure its there using CTRL+END


hope this brings success

regards

jo

 

[carfj1]

Thanks but still didnt work
At the running process I am getting the message:

"No coinciden los tipos"

OK

Which translates to:

"Data dont match"

Ok

Is the autonumber affecting?

Then:

Done
Ok
Thanks

 

[JoanneM]

Hi again

now you have me puzzled
are you in the test db I suggested or in you live db before we go any further?


To fault find go into your module to the line
Close #1
and press F9
this will put a break point on it
run the code and see if error msg comes up
mail me it will be faster email2jo@btinternet.com

 

[carfj1]

I did it all with a new database, it actually had only two objects, the table and the module. When running it I recived the mentioned error.

scince I need close control on the format of each row of the output table. I decided to go as stated on my first post.

Now after 12 hours of work I sent the db to my customer.
She cannot run one of the macros because it cannot run a query. An Invalid alias name error occurs.

Can you gide me on this?
I it a good idea to convert macros into modules?
How can I call a macro transformed to a module form a command button?

As I mention, I dont have VB skills

Thankyou