UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Export To Csv Changes Number Format, Access 2016    
 
   
AlBilly
post Sep 13 2017, 10:02 AM
Post#1



Posts: 105
Joined: 23-September 16



Hi to all,

I am encountering a very strange situation, I need to export a query to a .csv file but it keep adding .00 to numbers even after converting to string, the flat file keep display the number as number instead of converting to string, my query display the number properly, like a sting
I am using a VBA code line to do so
DoCmd.TransferText acExportDelim, "Export_SupplierCommMethod", "qry_SupplierCommMethod", strExport & "SuppInfoCommMethod.csv"

How can I fix this ?, no matter what I do it does not change anything, even importing the number directly in my table as text, still output as number

THanks
This post has been edited by AlBilly: Sep 13 2017, 10:16 AM
Go to the top of the page
 
theDBguy
post Sep 13 2017, 10:16 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Let's back up a little bit. A CSV file is a Text file. Are you opening the file using Notepad or any other Text Editor and seeing the additional .00 in it? Or, are you opening the file using Excel? If using Excel, then Excel is probably adding the .00 for you (right or wrong). If using Notepad, then we'll have to figure out why the extra .00 are being added.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 13 2017, 10:17 AM
Post#3



Posts: 105
Joined: 23-September 16



open the file in notepad/noptepad++
Go to the top of the page
 
theDBguy
post Sep 13 2017, 10:22 AM
Post#4


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


Okay, thanks. Did you say the data type in the table is Text? Just making sure...

PS. Wait a minute, I see you're using an Export Spec called "Export_SupplierCommMethod." Have you check the spec to see if it's adding the extra .00?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 13 2017, 10:29 AM
Post#5



Posts: 105
Joined: 23-September 16



there is no place in the spec for adding any zeros, i use it for the code page encoder only and the data is being display as text in the sample export format so the problem is not there !!! very confusing
Go to the top of the page
 
theDBguy
post Sep 13 2017, 11:18 AM
Post#6


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


Is there a space in the spec to identify if the column was supposed to be a Text or a Number? If so, does it say Text?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 13 2017, 11:43 AM
Post#7



Posts: 105
Joined: 23-September 16



the spec file is just for specifying delimiters, language and code page and numbers only, nothing much
I just found out that there were a bunch of updates to be installed on my pc, that might be the cause, will reboot at lunch and see after, wouldn't be surprise it is because of this
Go to the top of the page
 
theDBguy
post Sep 13 2017, 11:57 AM
Post#8


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


I see. Okay, good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 13 2017, 12:26 PM
Post#9



Posts: 105
Joined: 23-September 16



as expected, problems occurs while waiting for the updates to be installed, now working just fine

Go to the top of the page
 
theDBguy
post Sep 13 2017, 12:33 PM
Post#10


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


Yay! Congratulations.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 15 2017, 08:13 AM
Post#11



Posts: 105
Joined: 23-September 16



not quite solved.
the problem just appears again and I just do not see why ? the numbers exported to .csv just keep loosing it' format meaning , I am exporting currency rate with 4 digit anf the flat file truncate them to 2 and number that do not have digits have 2
I cannot see it why pullhair.gif , does anybody can help me on this problem,

see attachment
Go to the top of the page
 
theDBguy
post Sep 15 2017, 08:22 AM
Post#12


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


I don't see any attachment.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 15 2017, 08:33 AM
Post#13



Posts: 105
Joined: 23-September 16



let me download winzip
I am currently winrar
Go to the top of the page
 
AlBilly
post Sep 15 2017, 08:35 AM
Post#14



Posts: 105
Joined: 23-September 16



try this
Attached File(s)
Attached File  Doc1.zip ( 125.72K )Number of downloads: 2
 
Go to the top of the page
 
theDBguy
post Sep 15 2017, 10:43 AM
Post#15


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


Hmm, says it's invalid...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 15 2017, 10:56 AM
Post#16



Posts: 105
Joined: 23-September 16



try this one
Attached File(s)
Attached File  Doc1.zip ( 125.69K )Number of downloads: 1
 
Go to the top of the page
 
theDBguy
post Sep 15 2017, 11:06 AM
Post#17


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


This one worked. You have an Expr1 in there. What does the expression look like?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 15 2017, 12:03 PM
Post#18



Posts: 105
Joined: 23-September 16



Expr1: Val([VOUCHER_NO_REF]) to make sure it is a number not as string, cInt or INT still produce the same error

my main concern is really with the truncation of the exchange rate from 4 to 2 digit
This post has been edited by AlBilly: Sep 15 2017, 12:11 PM
Go to the top of the page
 
theDBguy
post Sep 15 2017, 12:08 PM
Post#19


Access Wiki and Forums Moderator
Posts: 71,012
Joined: 19-June 07
From: SunnySandyEggo


What happens if we change it to this?

Expr1: CStr(Val([VOUCHER_NO_REF]))

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 15 2017, 12:14 PM
Post#20



Posts: 105
Joined: 23-September 16



the voucher field is now becoming a null field since we will not track old transactions, so it is just the 1.2345 to 1.23,
it seems like there is an implicit number formatting to default 2 digit somewhere is the exportation to .csv which I really don't get
This post has been edited by AlBilly: Sep 15 2017, 12:16 PM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    18th November 2017 - 07:24 AM