Limitation with transfertext to csv file?

[Guest]

I have code in an Access procedure that, with a transfertext, exports the
results of a query to a delimited csv file which is then opened up in Excel.

The query is running fine, all the proper records are returned in the
datasheet view of the query when it is run in Access.

The problem occurs when the csv file is created through the transfertext --
values of ten digits or more don't come across in the csv file, they're just
blanks.

I have not found any limitations shown in the specs, but I can't figure out
any other reason for the data to not come across.

Any ideas? Thanks in advance.

 

[John Nurick]

This is not normal. I've just now used TransferText with no problems to
export queries to CSV with ten and eleven digit numbers in Double
fields, and up to 17 digits in a Decimal field.

Please can you describe in detail how to reproduce the effect you're
getting.

 

[Guest]

Yes, my statement is:

DoCmd.TransferText acExportDelim, "MyExportSpec", "SourceTable",
"C:\ExportedCSFfile.csv", False, ""

My export spec is for a delimited file. with a field delimiter of comma, and
a text qualifier of ". Everything comes through except for those ten-digit
numbers.

Thanks!

 

[John Nurick]

The only unusual things about that TransferText call are (1) the empty
string you're passing as the HTML table name and (2) the export
specification.

The HTML table name argument shouldn't be there (it's only used when
importing from tables in HTML files) but doesn't seem to do any harm. So
the next place to look for problems is in the export specification.
Exporting standard CSV files doesn't normally need a spec (CSV is the
default setting), so try the export without it.

Also: what type is the field that contains the ten-digit numbers? What
happens if you export the table to Excel: do these numbers come through
OK?

 

[Guest]

That was very helpful -- see comments below:

The only unusual things about that TransferText call are (1) the empty
string you're passing as the HTML table name and (2) the export
specification.

The HTML table name argument shouldn't be there (it's only used when
importing from tables in HTML files) but doesn't seem to do any harm. So
the next place to look for problems is in the export specification.
Exporting standard CSV files doesn't normally need a spec (CSV is the
default setting), so try the export without it.

That worked!

Also: what type is the field that contains the ten-digit numbers? What
happens if you export the table to Excel: do these numbers come through
OK?

Field type is a double -- and interestingly enough, when the table is
exported to Excel, everything comes through fine.

THANKS!!!

 

[John Nurick]

I can only guessthat for some reason the export spec doesn't match the
table. Did you perhaps at one time change the field in question from
Long to Double in order to hold large numbers? If you did that and left
the old spec I can imagine that causing subtle problems.