LITTLEBLACKDOG.COM Forum Index LITTLEBLACKDOG.COM

 
LWD LWD   FAQ FAQ   Memberlist Memberlist   Usergroups Usergroups   Active Topics Active Topics   Register Register  
  Profile Profile   Log in to check your private messages Log in to check your private messages   Log in Log in  
  Who is Online Who is Online   Image Gallery Image Gallery   Chat Chat   Search Search  
  LWDGear       LBDGear  

Excel - Importing Data Bug?
 View next topic
View previous topic
Post new topic     Reply to topic   LITTLEBLACKDOG.COM Forum Index » General Software
Author Message
Extreme
Big Dog
Big Dog


Joined: 17 Jun 2001
Age: 28
Posts: 4396
Location: Palm Bay, Florida USA
Post Posted: Mon Dec 03, 2007 6:35 pm   Post subject: Excel - Importing Data Bug? Reply with quote Back to top  
Background:
I have a report that is generated by our software that outputs some data, the field in question is a numeric field that holds a Credit Card # (I.E. 15-16 digits only)

The Issue:
When I open the file in Excel, it changes the last # to a zero. I.E. 1111222233334444 will display as 1.11122E+15 in the cell, but when you view the cell data, it shows 1111222233334440 Question

I have verified that the data is correct by viewing it in a text editor.

The Resolution:
Unknown...Does anyone have an idea? If not, how do I report this to Microsoft for a possible fix? For those who have Excel 2007, are you able to recreate this issue?

_________________
I ♥ my IT guy, do you?
View user's profile Send private message Send e-mail Visit poster's website AIM Address ICQ Number
ThunderDawg
Alpha Dog
Alpha Dog


Joined: 14 Apr 2002
Posts: 16744
Location: Paradise, by the Dashboard light
Post Posted: Mon Dec 03, 2007 7:43 pm   Post subject: Reply with quote Back to top  
Cell A1 = Credit Card Number.

B1 = LEFT(A1,16)

will return exactly 16 digits.


[Edit]

OKayyy that did not work. See next post.

_________________
Where in the World is ThunderPoopia.
View user's profile Send private message
ThunderDawg
Alpha Dog
Alpha Dog


Joined: 14 Apr 2002
Posts: 16744
Location: Paradise, by the Dashboard light
Post Posted: Mon Dec 03, 2007 7:53 pm   Post subject: Reply with quote Back to top  
Excel actually changes the original cell contents from
1111222233334444 to
1111222233334440
replacing the last digit with zero.

If you can import the credit card numbers as a text field (using single quote ' not double quote "), the problem goes away.

OR

Concatenate any character to every CC# and then truncate it off using left (a1,16) as previously mentioned.

Interestingly, if you leave the dashes in the CC#, 1111-2222-3333-4444, the problem also goes away

_________________
Where in the World is ThunderPoopia.
View user's profile Send private message
JustAnEngineer
Leg Humper
Leg Humper


Joined: 27 Jan 2002
Posts: 4628
Location: Heart of Dixie
Post Posted: Mon Dec 03, 2007 8:10 pm   Post subject: Reply with quote Back to top  
This is caused by the IEEE 754 specification of storing only 15 significant digits of precision.

http://support.microsoft.com/kb/78113

A simple workaround might be to put a ' character before the credit card number, causing it to be imported as text rather than as a number.

_________________
1: C2Q 9300, GA-X48-DS4, 8 GiB PC2-6400, Radeon HD3870X2, 4x 640GB Caviar SE16 (RAID 1+0) +750GB, Pioneer 106S, X-Fi XG, Antec P182, S75CF, 3007WFP, CVT Avant Prime, Logitech G7
2: Athlon64 X2 4600+, DFI RS482 Infinity, 2 GiB PC3200LL, Radeon X800XL, 320GB Barracuda 7200.10, Samsung SH-S182M, ASUS TM-210, M12-500, 2001FP, Logitech MX3000
View user's profile Send private message
gregw
Tail-Wagger
Tail-Wagger


Joined: 25 May 2003
Posts: 2598
Location: About 2000 miles south of where I want to be.
Post Posted: Tue Dec 04, 2007 5:26 am   Post subject: Reply with quote Back to top  
Can you change the cell format to Text?

_________________
Some people are like slinkys... not really good for anything but they still bring a smile to your face when you push them down a flight of stairs.
View user's profile Send private message
Extreme
Big Dog
Big Dog


Joined: 17 Jun 2001
Age: 28
Posts: 4396
Location: Palm Bay, Florida USA
Post Posted: Tue Dec 04, 2007 8:08 pm   Post subject: Reply with quote Back to top  
Thanks Guys! I ended up just outputting the file to a .txt file (Keeping the CSV Format), and then using Excel to import it, and specifying the CC # Field as text and doing some formatting.

Its all done via an excel macro, so the user didn't even notice a change.

_________________
I ♥ my IT guy, do you?
View user's profile Send private message Send e-mail Visit poster's website AIM Address ICQ Number
Display posts from previous:   
Post new topic     Reply to topic   LITTLEBLACKDOG.COM Forum Index » General Software
 
Jump to:  

View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group
phpBB SEO
All times are GMT - 8 Hours

Help us keep advertisements off this site. Donate today!