Text Qualifier errors when exporting to a txt file.

  • Thread starter Thread starter Dana
  • Start date Start date
D

Dana

When I export from a table in Access 2002 to a comma
delimited text file, during export I can view that all
text qualifiers are present. When I open the txt file in
notepad when a field is left blank I don't get a set of
empty text qualifiers

Desired result
"Field 1","Field 2","Field 3"
"Field 1","","Field 3"

Result received
"Field 1","Field 2","Field 3"
"Field 1",,,"Field 3"

How can I get the empty quotes without putting a space or
character in the field - they must be left blank.
 
You can make your own export utility using the the
TextStream object which would give you more control. For
example, the following would create a text file on the
root C:\ drive with the first value in the "FirstName"
column of a table called "tblCustomer". The value would
be enclosed in quotes, so if there was a Null value or
empty space, it would look like ""
You can create a loop that goes through all of the fields
and rows of the table in a similiar manner, putting
commas and carriage returns in the file where appropriate.

This procedure references the "Microsoft Scripting
Runtime" library which must be set in the "References"
collection from the Tools menu in VBA IDE in Access.

...........BEGIN CODE.....
Sub CreateTextOutputFile()
Dim fso As New FileSystemObject
Dim ts As TextStream
Dim rs As New ADODB.Recordset
Dim strValue As String


rs.Open "tblCustomer", CurrentProject.Connection,
adOpenKeyset, adLockReadOnly
strValue = Nz(rs("FirstName"), "")

Set ts = fso.CreateTextFile("C:\Test.txt", True)

ts.WriteLine ("""" & strValue & """") & ","

End Sub

...............END CODE.......
 
Hi Dana,

One way is to create a query in which you supply the text qualifiers
yourself, using calculated fields, e.g.
fField1: Chr(34) & [Field 1] & Chr(34)

Then export this query without text qualifiers.
 
Dear Dana,

Simular kind of solution as John has given is
- Change query
expr1: "" & [colum name]

If field is empty the tex qualifier will see "" as empty input and generate "" between the seperators.

BR,
Dirk
 
Back
Top