Creating a CSV file

madcats

Registered User.
Local time
Today, 16:37
Joined
Jun 24, 2005
Messages
36
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.
 
What is your current version? What was the version of the previous Access?
 
Old Access 2000 file format using Access 2003
New Access 2016
 
Just tested using the export wizard in A2010 & saving as both txt and csv files
Both correctly kept the 'NumberText' field as text

attachment.php


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!
 

Attachments

  • Capture.PNG
    Capture.PNG
    18.6 KB · Views: 254
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

Code:
SELECT AnimalLocs.SightingDate
, Format([GPSLong],"0000.00") AS GPSLONGT
, Format([GpsLat],"0000.00") AS GPSLATT
, AnimalLocs.AnimalId
FROM AnimalLocs;
 
Last edited:
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom