Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-06-2018, 01:43 PM   #1
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 29
Thanks: 6
Thanked 0 Times in 0 Posts
madcats is on a distinguished road
Creating a CSV file

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.

madcats is offline   Reply With Quote
Old 06-06-2018, 01:48 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,535
Thanks: 54
Thanked 1,871 Times in 1,821 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Creating a CSV file

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-06-2018, 01:54 PM   #3
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 29
Thanks: 6
Thanked 0 Times in 0 Posts
madcats is on a distinguished road
Re: Creating a CSV file

Old Access 2000 file format using Access 2003
New Access 2016

madcats is offline   Reply With Quote
Old 06-06-2018, 02:09 PM   #4
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,208
Thanks: 83
Thanked 1,532 Times in 1,427 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Creating a CSV file

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!
Attached Images
File Type: png Capture.PNG (18.6 KB, 75 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
madcats (06-07-2018)
Old 06-06-2018, 02:26 PM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,535
Thanks: 54
Thanked 1,871 Times in 1,821 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Creating a CSV file

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;
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 06-07-2018 at 01:43 PM.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
madcats (06-07-2018)
Old 06-07-2018, 08:03 AM   #6
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 29
Thanks: 6
Thanked 0 Times in 0 Posts
madcats is on a distinguished road
Re: Creating a CSV file

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.
madcats is offline   Reply With Quote
Old 06-12-2018, 06:28 AM   #7
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 586
Thanks: 9
Thanked 50 Times in 48 Posts
kevlray will become famous soon enough
Re: Creating a CSV file

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.


kevlray is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Creating a .DAT file Nittylions General 2 05-22-2009 05:14 PM
Creating own Help file ? pookie62 Modules & VBA 7 02-20-2007 11:31 AM
Creating a dll file shadowraven Other Software 3 12-09-2005 07:29 AM
Creating An MDE File paulS30berks General 1 09-06-2005 01:13 AM
Creating a File.exe lucour Modules & VBA 0 12-18-2002 03:37 PM




All times are GMT -8. The time now is 10:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World