By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Exporting data from Access to CSV in particular format

100+
P: 119
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
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
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

100+
P: 119
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.