Access Data to CSV file

[xxoulmate]

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"

[ADezii]

@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.

[xxoulmate]

how bout., instead of saving it in csv file save it in text file through query

[harshakusam]

This method will help you...

The TransferText method has the following syntax and options:
 
DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
 
 

This will work...

Dim strSQL As String
strSQL = "Your select statement"
DoCmd.TransferText ExportDelim, "strSQL", "path.csv"
 

[xxoulmate]

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

[NeoPa]

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?

[ADezii]

@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:

Public Function fImportCSVFile(strFileName As String)
Dim strNewName As String
 
If InStr(strFileName, "Exec ") > 0 Then     'does "Exec " appear in the File Name
  strNewName = Replace(strFileName, "Exec ", "Exec_")
    Name strFileName As strNewName      'Rename the File replqacing Exec  with Exec_
      DoCmd.TransferText acImportDelim, , "CSV Table", strNewName, False
      MsgBox strFileName & " has been Renamed to " & strNewName & " in order to avoid " & _
                           "Import Errors", vbExclamation, "Change in File name"
Else
  DoCmd.TransferText acImportDelim, , "CSV Table", strFileName, False
End If
End Function

To successfully Import a File with 'Exec ' contained within its Name:

Debug.Print fImportCSVFile("C:\Dezii\Exec Test.csv")

[NeoPa]

Wasn't the "Exec in filename" issue from another thread?

[ADezii]

@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.

[NeoPa]

I think the giggle was worth it ADezii ;)

Besides, you more than make up for any senior moment lapses.

[HAL4u]

DoCmd.TransferText acExportDelim, "KlasExportspecificatie", "Resultaten", "C:\Users\HAL\Desktop\" & klKlassen.Value & " " & sKeuze & ".csv"
 
"KlasExportspecificatie"      is the exportspecification I created with the export wizard, containing the settings for CSV files
"Resultaten"                  is the table from which the data is exporting
"C:\Users\HAL\Desktop\" & klKlassen.Value & ".csv"
                              is the file path to where the file is exported
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