Delimited Export Specification - independent of table

[Tokyo Alex]

Dear all,

Is it possible in Access 2007 to create a delimited-data export
specification that is independent of the source table structure?

For example, if I want to export to a text file with a delimiter | and a
text qualifier of # (instead of the default , and "), I can create a spec to
allow automation of the task with TransferText.

However, if the table I use to create the spec has 7 fields and I try to
apply the spec to a different table with 8 fields I get a "The Microsoft
Office Access database engine could not find the object 'Filename#csv'. Make
sure the object exists and that you spell its name and the path name
correctly," error. (Aside: Least helpful Access error message *ever*)

Is there anyway to bypass this so that I can use code to cycle through an
array of export tables without having to specify a different spec for each
table?

Thanks,
Alex.

 

[Roger Carlson]

Well, here's a little routine that will export a table or query to a a text
file, with whatever delimiter, text qualifier, and with or without field
names:

'-----------------------------
Sub ExportTextFileDelimited(FileName As String, _
DataSet As String, _
Delimiter As String, _
TextQualifier As String, _
WithFieldNames As Boolean)
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String
Dim I As Integer

Open FileName For Output As #1
Set cnn = CurrentProject.Connection

rst.Open DataSet, cnn, adOpenForwardOnly, adLockReadOnly
If WithFieldNames Then
For I = 0 To rst.Fields.Count - 1
MyString = MyString & rst(I).Name & Delimiter
Next I
MyString = Left(MyString, Len(MyString) - 1)
Print #1, MyString
End If
rst.MoveFirst
Do While Not rst.EOF
MyString = ""
For I = 0 To rst.Fields.Count - 1
'check for text datatype (202)
If rst(I).Type = 202 Then
MyString = MyString & TextQualifier & _
rst(I) & TextQualifier & Delimiter
Else
MyString = MyString & rst(I) & "|"
End If
Next I
MyString = Left(MyString, Len(MyString) - 1)
Print #1, MyString
rst.MoveNext
Loop

ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub
ExportTextFile_Err:
MsgBox Err.Description
Resume ExportTextFile_Exit
End Sub
'-----------------------------

Call it like this:

Call ExportTextFileDelimited2("C:\Table1.txt", "Table1", "|", "#", true)

Of course, you could put it in a loop and replace the table (or query) and
filename each time.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Whups. Couple of corrections in the main loop:

Do While Not rst.EOF
MyString = ""
For I = 0 To rst.Fields.Count - 1
'check for text datatype (202)
If rst(I).Type = 202 Then
MyString = MyString & TextQualifier & _
rst(I) & TextQualifier & Delimiter
Else
MyString = MyString & rst(I) & Delimiter '<----
End If
Next I
MyString = Left(MyString, Len(MyString) - 2) '<---
Print #1, MyString
rst.MoveNext
Loop

 

[Tokyo Alex]

Thanks very much for the response.

I was hoping to be able to use TransferText, but it looks like I can't, and
your code will be very helpful.

Thanks again,
Alex.