Creating a CSV file (1 Viewer)

[madcats]

I am trying to create a CSV file using the ImportExportText / Export Delimited. Everything is working except the fields that are text in the table that resemble numbers are exported as numbers (000 exported as 0 or 067 exported as 67). These are GL SubAccount fields and when I import the CSV file into our accounting software it crashes as unidentified SubAccounts.

In a earlier version of access I used the TransferText / Export Delimited and it created the CSV file okay. It looks like the TransferText is no longer available on my current version.

 

[jdraw]

What is your current version? What was the version of the previous Access?

 

[madcats]

Old Access 2000 file format using Access 2003
New Access 2016

 

[isladogs]

Just tested using the export wizard in A2010 & saving as both txt and csv files
Both correctly kept the 'NumberText' field as text

I ticked 'Export Data with formatting and layout'

BTW the table with the DUMMY text was created for another thread. Its not meant to be a reflection about you!

 

[jdraw]

I just tried this : using o365 --transfertext is here.
- create a select query and make sure you format your digits.
-save the query name
-use the queryname in the transfertext
eg:

DoCmd.TransferText acExportDelim, , "Query55", "C:\users\Jack\Documents\query55CSV.csv", True

This is my output from my data
===only the "blue fields" ere formated to text to includ the leading 0
"SightingDate","GPSLONGT","GPSLATT","AnimalId"
27-Mar-2009 0:00:00,"0077.90","0047.30",1
28-Mar-2009 0:00:00,"0077.34","0046.80",1
02-Dec-2009 0:00:00,"0078.44","0045.32",1
13-Mar-2009 0:00:00,"0076.33","0048.90",2
29-Mar-2009 0:00:00,"0077.45","0048.80",2
21-Apr-2010 0:00:00,"0078.53","0047.54",2
24-Aug-2009 0:00:00,"0076.20","0049.40",3
21-Apr-2010 0:00:00,"0078.23","0047.52",4
23-Apr-2010 0:00:00,"0078.67","0047.66",4
14-Sep-2010 0:00:00,"0077.70","0049.30",5
17-Sep-2010 0:00:00,"0077.33","0048.90",5

OOOps: I see Colin has responded while I was concocting an example from some existing data.

Also, just noticed I hadn't posted the SQL for Query55

SELECT AnimalLocs.SightingDate
, Format([GPSLong],"0000.00") AS GPSLONGT
, Format([GpsLat],"0000.00") AS GPSLATT
, AnimalLocs.AnimalId
FROM AnimalLocs;

 

[madcats]

Yes, I see that now. I was using excel to open the CSV file and it made it appear to be a 0 instead of 000. The error must be something else.

Thanks for taking the time to show me that.

 

[kevlray]

I have found that Excel likes to 'fix' your data. So I open csv files in Notepad (or Notepad++) to see how the data really looks.