468,119 Members | 1,962 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,119 developers. It's quick & easy.

Exporting data from Access to CSV in particular format

119 100+
Hi there,

My question does not concern the exporting of data to CSV so much as the format of the exported data. The data to be exported consists of a time series of prices for a number of assets. I can easily bring up a query of the data I want to export in the following way:

Expand|Select|Wrap|Line Numbers
  1. SELECT Date, Asset, AssetPrices
  2. FROM tblPrices;
  3.  
returning:

Expand|Select|Wrap|Line Numbers
  1. [Date],[Asset],[AssetPrice]
  2. date_1,asset_1,price_1    
  3. date_2,asset_1,price_2
  4. date_3,asset_1,price_3
  5. etc....
  6. date_1,asset_2,price_1    
  7. date_2,asset_2,price_2
  8. date_3,asset_2,price_3
  9. etc....
  10. date_1,asset_3,price_1    
  11. date_2,asset_3,price_2
  12. date_3,asset_3,price_3
  13. etc....
  14.  
However, in the CSV format I would like the prices to be aligned in the following way:

Expand|Select|Wrap|Line Numbers
  1. "Date","Asset_1","Asset_2","Asset_3"
  2. date_1,price_1, price_1, price_1
  3. date_2,price_2, price_2, price_2
  4. date_3,price_3, price_3, price_3
  5. etc....
  6.  
So, taking a 1-dimensional data structure within Access and exporting it as a 2-dimensional data structure. Does anyone know how to do this? Two possibilities might be to (1) create a data structure in Access to then export to CSV or (2) export each asset's data series at a time, appending successive columns of data within the CSV file.

Any help greatly appreciated.
May 14 '08 #1
2 1529
nico5038
3,079 Expert 2GB
For this you need a cross table query.
Place the fields like:
Date, Asset, AssetPrices
in the query editor and change the query type to cross-table.
Now change the GroupBy under the AssetPrices into "Max"
Finally set the fields in the "combo line" for the cross table like:
Rowheader, ColumnHeader and Value

Nic;o)
May 14 '08 #2
billelev
119 100+
Perfect! Thanks, Nic.

I've been looking for this kind of formatting in Access (obviously not very hard) for about a year!
May 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by mik18 | last post: by
11 posts views Thread by PC Datasheet | last post: by
1 post views Thread by Mustufa Baig | last post: by
reply views Thread by =?Utf-8?B?ZGVuIDIwMDU=?= | last post: by
13 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.