Exporting to CSV based on column/cell location.

[DPM]

I have a bit of a tricky problem. Hopefully someone can help me because this one has me a little stumped. I'm pretty new to Access and VBA for Access is not the same animal I know from other Office apps. After exporting to Excel and running a module to remove the unnecessary data (which I know how to do through Excel, so it was a bit of a cheat), I can re-import the data to Access. Now, here is where the rub lies: Every row contains 6 columns of data. I need to figure out a way to take values, based on column, and assign a column value, followed by the actual value that is stored in the column. For example:

Value1 Value2 Value3 Value4 Value 5 Value6
Null Null Null Value7 Value8 Value9
Null Null Null Value10 Value11 Value12
Value13 Value14 Value15 Value16 Value17 Value18
Null Null Null Value19 Value20 Value21

I need to be able to translate this into:
1, Value1
2, Value2
3, Value3
4, Value4
5, Value5
6, Value6
4, Value7
5, Value8
6, Value9
4, Value10
5, Value11
6, Value12
1, Value13
2, Value14
3, Value15
4, Value16
5, Value17
6, Value18
4, Value19
5, Value20
6, Value21

And so on.
1,2,3 groups are always followed by one or more 4,5,6 groups. I would have done this purely in Excel, but I am dealing with an enormous amount of data. Before reformatting, there are almost 62,000 rows of data, so Excel is not the best option because it maxes out (per sheet) at just 65,536 (256^2) rows of data. Like I mentioned, I'm pretty new to Access, so I am not familiar with all of its functionality yet. Can someone point me in the right direction? Thanks for your time, everyone! I really appreciate any help you can give me!

 

[SydneyGeek]

Hi, and welcome to the Board!

You don't need VBA for this; it can be done using a query.
Assuming that you have tblTest with fields Field1, Field2, ... , Field6 you can tackle it like this:

1. Build a query using tblTest. The first field in the grid is one you enter manually --
Header:1
The second field in the grid is Field1. In the Criteria row type Is Not Null.

2. Run the query to check: you should have a series of 1's adjacent to values from Field1.

3. Now to turn that into a UNION query, which lets you transpose all the fields at once. Go to the SQL view in the query; it will look something like

SELECT 1 AS Header, tblTest.Field1 AS [Value]
FROM tblTest
WHERE (((tblTest.Field1) Is Not Null))

Note that the semi-colon has been removed -- you need to do that or the query will choke when you try to daisy-chain them.
Copy the SQL into Notepad. Replace all 1's with 2's, copy, go back to Access and type UNION ALL in the first blank row of the SQL page, then paste.
Back to Notepad, replace 2's with 3's, copy, ... you get the sequence.
It should end up looking something like

SELECT 1 AS Header, tblTest.Field1 AS [Value]
FROM tblTest
WHERE (((tblTest.Field1) Is Not Null))
UNION ALL
SELECT 2 AS Header, tblTest.Field2 AS [Value]
FROM tblTest
WHERE (((tblTest.Field2) Is Not Null))
UNION ALL
SELECT 3 AS Header, tblTest.Field3 AS [Value]
FROM tblTest
WHERE (((tblTest.Field3) Is Not Null))
...
etc for as many fields as required

When you run that query (it could take a minute or so) you will have transposed all your records. From there you can export the data to CSV.

Denis