Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 02-09-2015, 09:03 PM   #1
uniq_usr
Newly Registered User
 
Join Date: Jan 2015
Posts: 12
Thanks: 3
Thanked 1 Time in 1 Post
uniq_usr is on a distinguished road
Exporting query with parameters to csv template

Hello All,

I have question as to the approach I should take regarding a problem I have.

I have a template csv file which has comes with headers. I now need to export multiple datas from my different tables into the csv file. I thought I would open an instance of excel, open the query as recordset (in VBA) and go through each record and finally use appExcel.saveAs as a CSV file. The approach doesnt look like it will look. I have looked into the DoCmd.Transfer text method but I cant seem to create a export specification because my query requires two parameters (startDate and endDate). If anyone could shed a bit more light on exporting results from a query into existing csv file would be great.
Secondly it is a huge template, over 700 fields and I will only be exporting around 40 fields so there will be huge number of empty fields. If this was a excel file, I would write few queries and write result from one query into the file then skip required columns and then write results from another query but I don't know if its possible with Docmd.write txt.
I can understand VBA fairly well and would really appreciate if you point me to any learning material,code examples or any other insights you have.

I am using access 2013 (Office 365 Pro) to perform all of this.

uniq_usr is offline   Reply With Quote
Old 02-09-2015, 09:11 PM   #2
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Exporting query with parameters to csv template

Are you going to allow your Users to put in parameters via a Form and then export to your Template?
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 02-09-2015, 09:51 PM   #3
uniq_usr
Newly Registered User
 
Join Date: Jan 2015
Posts: 12
Thanks: 3
Thanked 1 Time in 1 Post
uniq_usr is on a distinguished road
Re: Exporting query with parameters to csv template

The template, as far as I can see, will not be changed via access. It will be a csv file with headers written into it and I want to write into the file then save a copy as filename_timestamp. This leaves the original template intact (This is what I have been doing with excel)
The user will be in a form where all they do is select the dates and click on the button. I will then pass on this dates to my query in vba and the returned result will be written into the csv. I also have a text box where I inform the user of the steps happening (this is incase a error happens)
All of my exports so far has happened in excel but a new system requires us to export a set of data in the exact format (.csv) and they have a provided us with a template file. For eg it has 10 fields with (employee1, employee2,..) but we only use 1 employee so I will need to ensure the query exports out empty field in a lot of steps.
Please let me know if I am not clear in any of the things I wrote and really appreciate your help in this problem

uniq_usr is offline   Reply With Quote
Old 02-10-2015, 08:04 AM   #4
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Exporting query with parameters to csv template

Oh, you have done this in Excel, well it's the same for your .CSV file only now you export to TEXT. You could probably set up and use a saved import for that. Are you having a problem converting your existing code?
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
The Following User Says Thank You to GinaWhipp For This Useful Post:
uniq_usr (02-10-2015)
Old 02-10-2015, 06:21 PM   #5
uniq_usr
Newly Registered User
 
Join Date: Jan 2015
Posts: 12
Thanks: 3
Thanked 1 Time in 1 Post
uniq_usr is on a distinguished road
Re: Exporting query with parameters to csv template

I find it easier to manipulate data to different cells as required using appExcel.application and .Activecell.offset. I also define cell names in the templates so I can easily focus where to start the writing from.
Thank you for your direction so far and I have been able to save it as csv, for any one stumbling upon this in the future, I have used :

Code:
appExcel.ActiveWorkbook.SaveAs FileName:=CsvFileName, FileFormat:=23
'// where CsvFileName is a variable that stores the full path and the intended file name ending with csv and the number 23 represents the excel fileformat for windows csv

I have got a slight problem, the client end of this report is demanding each field in the csv come wrapped in " " (double quotes), while saving it from excel just outputs the standard field contents. Would you know of any approach in doing it ?

Secondly, I dont know if this warrants a separate question ( I would be happy to do this), is there a limitations in the number of fields a query can have in acces, or a limitation in number of empty fields in access >

Thank you for your help so far
uniq_usr is offline   Reply With Quote
Old 02-10-2015, 07:05 PM   #6
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Exporting query with parameters to csv template

Hmm, I have ever done this is export to text. I did find...
http://www.experts-exchange.com/Data..._28078709.html

I believe a query has the same number of fields as a table which is 255 but there are other limitations that may be of help to you. Have a look at...
http://regina-whipp.com/blog/?page_id=42

No need to put related questions in a separate thread. Glad to have helped!
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 02-11-2015, 12:28 AM   #7
uniq_usr
Newly Registered User
 
Join Date: Jan 2015
Posts: 12
Thanks: 3
Thanked 1 Time in 1 Post
uniq_usr is on a distinguished road
Re: Exporting query with parameters to csv template

As i have been doing all the manipulating with an open instance of excel in the background. I found it a bit of extra stretch on memory to again go through the whole file and write a txt file. The code i wrote above works perfectly except for the " " part. If I manually add character infront and behind each field with the use
Code:
Chr(34) & rs.fields (i) & Chr(34)    '// Chr(34) is the code for ""
. Then excel saves it as "My value" but then converting it to csv, excel will convert it to ""My value"". I cant find a way for access to use a instance of excel and force it to not convert my txt when saving a csv. A solution I will try is save the file as a excel file, use the method mentioned in the following post and save it as csv. If any one knows any other easier way of approachin this, please let me know

uniq_usr is offline   Reply With Quote
Old 02-11-2015, 12:29 AM   #8
uniq_usr
Newly Registered User
 
Join Date: Jan 2015
Posts: 12
Thanks: 3
Thanked 1 Time in 1 Post
uniq_usr is on a distinguished road
Re: Exporting query with parameters to csv template

Method for saving excel file to csv with both , and "" as delimiter. https://gist.github.com/fabriceleal/7803969 Couldnt post link in my earlier comment because of low comment count
uniq_usr is offline   Reply With Quote
The Following User Says Thank You to uniq_usr For This Useful Post:
GinaWhipp (02-11-2015)
Old 02-11-2015, 09:33 AM   #9
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Exporting query with parameters to csv template

Cool code.. are you saying you now have it working?
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 02-11-2015, 08:12 PM   #10
uniq_usr
Newly Registered User
 
Join Date: Jan 2015
Posts: 12
Thanks: 3
Thanked 1 Time in 1 Post
uniq_usr is on a distinguished road
Re: Exporting query with parameters to csv template

Unfortunately No. The link I posted is for a excel macro. I need to do the same to my excel file from within access (using vba). Not getting anywhere with that.
uniq_usr is offline   Reply With Quote
Old 02-11-2015, 10:15 PM   #11
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Exporting query with parameters to csv template

Any way you could set up a Template file with the Excel Macro and then dump the data to the Template, run the Macro from Access (which can be done) and then, again from within Access, do a Save As a .CSV file. That actually sounds doable if you could use a Template.

__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Reply

Tags
csv , export csv , queries , vba access 2013

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting query fields to Word Template duecesup Reports 1 11-21-2008 02:45 PM
Exporting query's results to excel template bugsy Modules & VBA 5 04-20-2008 01:57 PM
Looking for Example of exporting Query results to Excel Template Ginny2222 Queries 2 01-27-2008 10:15 AM
HTML template help; exporting Junkee Brewster General 0 12-09-2005 09:00 PM
Exporting query results to an excel template jedcomyn Queries 1 06-18-2004 06:25 PM




All times are GMT -8. The time now is 07:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World