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

Access Data to CSV file

xxoulmate
P: 77
how can i extract data from access table into csv file format.,
i want it to save through query.,

i have example of sql query saving in xls format., but i want the query result to save in csv format.
eg. xls format
- "Select Fields into TempData in 'sample.xls' 'Excel 5.0;' from From Table"
May 19 '09 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,679
@xxoulmate
I can show you a Custom Routine that I created which will write specific Query Field Values to a CSV (Comma Seperated Value) File but I'll wait and see if anyone has a better idea. I'm probably mistaken, but I do not think that there is an easy way to accomplish this through the Access Interface, but there is programmatically.
May 19 '09 #2

xxoulmate
P: 77
how bout., instead of saving it in csv file save it in text file through query
May 19 '09 #3

P: 36
This method will help you...

Expand|Select|Wrap|Line Numbers
  1. The TransferText method has the following syntax and options:
  2.  
  3. DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
  4.  
  5.  

This will work...

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "Your select statement"
  3. DoCmd.TransferText ExportDelim, "strSQL", "path.csv"
  4.  
May 19 '09 #4

xxoulmate
P: 77
how can i add this to vb 6 program
ex.
i will use the access database
location = "c:\db1.mdb"
query = "select * from table1"


how can i use docmd in vb
May 19 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
There is an option to save an object (Table or QueryDef will work - NOT SQL string) as a CSV file within Access (VBA). If you need to know how to access this via VB, then I suggest the VB forum is where you need help (although I suspect it may be as simple as having an Access application variable in your code and using AccVar.DoCmd ...). Would you like me to move this over for you?
May 25 '09 #6

ADezii
Expert 5K+
P: 8,679
@xxoulmate
It took me a little while, but I did come up with what may be a workable solution, the 'Base Code' of which I'll post for you. Basically, if the Filename contains 'Exec ' it is converted and Renamed to 'Exec_', the Import occurs without any problems, and the User is notified of this process. If the Filename does not contain 'Exec ', it is Imported normally. It is up to you as to whether or not it is a viable Option:
Expand|Select|Wrap|Line Numbers
  1. Public Function fImportCSVFile(strFileName As String)
  2. Dim strNewName As String
  3.  
  4. If InStr(strFileName, "Exec ") > 0 Then     'does "Exec " appear in the File Name
  5.   strNewName = Replace(strFileName, "Exec ", "Exec_")
  6.     Name strFileName As strNewName      'Rename the File replqacing Exec  with Exec_
  7.       DoCmd.TransferText acImportDelim, , "CSV Table", strNewName, False
  8.       MsgBox strFileName & " has been Renamed to " & strNewName & " in order to avoid " & _
  9.                            "Import Errors", vbExclamation, "Change in File name"
  10. Else
  11.   DoCmd.TransferText acImportDelim, , "CSV Table", strFileName, False
  12. End If
  13. End Function
To successfully Import a File with 'Exec ' contained within its Name:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fImportCSVFile("C:\Dezii\Exec Test.csv")
May 26 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
Wasn't the "Exec in filename" issue from another thread?
May 26 '09 #8

ADezii
Expert 5K+
P: 8,679
@NeoPa
I do believe you're corect, NeoPa. Just add it to my ever growing 'OOPs List'. Actually, I think I may have Double-Posted, sorry.
May 26 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
I think the giggle was worth it ADezii ;)

Besides, you more than make up for any senior moment lapses.
May 26 '09 #10

P: 1
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acExportDelim, "KlasExportspecificatie", "Resultaten", "C:\Users\HAL\Desktop\" & klKlassen.Value & " " & sKeuze & ".csv"
  2.  
  3. "KlasExportspecificatie"      is the exportspecification I created with the export wizard, containing the settings for CSV files
  4. "Resultaten"                  is the table from which the data is exporting
  5. "C:\Users\HAL\Desktop\" & klKlassen.Value & ".csv"
  6.                               is the file path to where the file is exported
  7. klKlassen.Value               is the value from a list-box which contains a part of the filename
The trick is to make a exportspecification in the Export Wizard.
Right-click the exported table. Choose EXPORT, TXT-file.
All checkboxes not checked.
Use on the next page the ADVANCED-button to modify and save the exportspecification.

That does the trick, HAL
Jan 18 '10 #11

Post your reply

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