Exporting to CSV delim (quote and concat issues)

[Killer42]

In Access, when you do an Export (through the menus) you can define an export specification. This lets you set all kinds of details about the data format (for example delimited or fixed width fields), field delimiter, text delimiter (your unwanted quotes) and so on.

Once you do that, you can save the specification with a name. Then in your code, according to NeoPa, you can say to use that spec.

This may solve your quote problem.

[czarbjones]

That almost sounds too good to be true. I've setup the export schema for comma delimited, no quotes and saved it, but how do I then reference it?

Any ideas?

[NeoPa]

'THIS IS EXAMPLE CODE ONLY - DESIGNED FOR MY DATABASE!

'ExportFile exports tbl{strName} to {conImpExpFolder}\{strName}.New
Public Sub ExportFile(strName As String)
    Dim strSpec As String, strTable As String
    Dim strOut As String, strFile As String

    strSpec = strName & " Spec"
    strTable = "tbl" & strName
    strOut = conImpExpFolder & strName & "New.Txt"
    strFile = conImpExpFolder & strName & ".New"
    Call DoCmd.TransferText(TransferType:=acExportFixed, _
                            SpecificationName:=strSpec, _
                            TableName:=strTable, _
                            FileName:=strOut, _
                            HasFieldNames:=False)
    On Error Resume Next
    Call Kill(strFile)
    On Error GoTo 0
    Name strOut As strFile
End Sub

TransferText Method


The TransferText method carries out the TransferText action in Visual Basic. For more information on how the action and its arguments work, see the action topic.

Syntax

DoCmd.TransferT ext [transfertype][, specificationna me], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]

The TransferText method has the following arguments.

Argument Description
transfertype One of the following intrinsic constants:
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim (default)
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
If you leave this argument blank, the default constant
(acImportDelim) is assumed.
Notes You can link to data in a text file or HTML file, but this data is read-only in Microsoft Access.
Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).

specificationna me A string expression that's the name of an import or export specification you've created and saved in the current database.
For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.
tablename A string expression that's the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.
filename A string expression that's the full name, including the path, of the text file you want to import from, export to, or link to.
hasfieldnames Use True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed.
This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.
HTMLtablename A string expression that's the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the transfertype argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked.
The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.
codepage A Long value indicating the character set of the code page.

[czarbjones]

I still had issues until I decided to approach it a different way. Instead of trying to get the formating correct on the export, I figured I'd export it, then open the file and just search and replace on the quote strings then save it using the date and time filename.

Obviously if there's too much data the readall() could grind the computer to a halt by using up the virtual memory, but my requirements are very low (few hundred records each time), so seems to be a reasonable work around. I'll post what I did below in case it helps anyone else.

P.S. Thanks to everyone who chipped in to help me on this thread. :)

Dim MyDate
Dim MyFile
Dim Mystring

MyDate = Now()
Mystring = Format(MyDate, "yyyymmdd_hhnnss")
MyFile = "C:\Customer\DSF_" & Mystring & ".csv"

DoCmd.TransferText acExportDelim, , _
    "QueryDataExport", "c:\customer\data.csv", True

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\customer\data.csv", ForReading)

strContents = objFile.ReadAll()
objFile.Close

strOldText = Chr(34)
strNewText = ""

strContents = Replace(strContents, strOldText, strNewText)

Set objFile = objFSO.OpenTextFile(MyFile, ForWriting, True)
objFile.Write strContents
objFile.Close

[Killer42]

I guess if it works, it works. But personally I think that using the export specification would be the more elegant solution. It effectively does the job in a single step, rather than two or more.

[czarbjones]

Killer I agree, but sometimes real world deadlines don't allow for the most elegant of programming lol. I was just having no luck and out of time. One of those ugly work arounds that, well, works. :)