Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-22-2000, 05:59 PM   #1
Louise
Member
 
Join Date: Oct 1999
Location: Sydney, Australia
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Louise
Import .csv file problem

Hi,
I'm using the docmd.transfertext action to import a .csv file. The file is a softcopy of an invoice. I have the spec set up and I thought everything worked fine.

Now I have found that when I import the file using the code it is somehow changing my values in the (monetary) amount field.

Basically when I sum the Amount in excel, or when I copy the data and Paste Append into the table in Access, I get the right sum value. When I import the file and sum the Amount it is less than what it should be.

It is being imported into a pre-existing table and the Amount field is set to currency, and in the source file it is only to 2 decimal places anyway so I didn't think it could be about rounding.

Can anyone think why this might happen???

(ps sorry for the long-winded explanation!)

Louise is offline   Reply With Quote
Old 08-23-2000, 12:38 AM   #2
Atomic Shrimp
Humanoid lifeform
 
Join Date: Jun 2000
Location: Portsmouth, England
Posts: 1,954
Thanks: 0
Thanked 8 Times in 7 Posts
Atomic Shrimp has a spectacular aura about Atomic Shrimp has a spectacular aura about
In your import spec, you haven't set up any of the monetary value fields as Integer have you?

Otherwise, can you spot specific examples of values that have imported incorrectly and describe the differences?

Mike
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
- it's not radioactive, and contains only a few crustaceans.
Atomic Shrimp is offline   Reply With Quote
Old 08-23-2000, 12:57 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,298
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Are you sure all the rows are being appended? If you have warnings turned off you won't see the error message that some rows couldn't be appended.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-24-2000, 02:25 PM   #4
Louise
Member
 
Join Date: Oct 1999
Location: Sydney, Australia
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Louise
Hi everyone,
Thankyou all for your input. This problem had me really stumped, however I have managed to solve it.

Everything seemed to check out, the same number of rows was appending, there was no rounding, there were no null values that were being omitted, and when I did a straight copy and paste, the amounts summed correctly!

But what was hapenning was that the original source file was in excel format. When I did the copy and paste it was from this format. However to maintain certain data type requirements we saved this file as a .csv so as to do a transfer text. What I eventually discovered was that one text field had a comma in the middle of it and when it was imported, it moved half the record over by one column and that changed my amount value for that record. That was the exact amount I was missing.

Glaringly obvious....yes
staring me in the face.....yes

Guilty as charged, but as you all know sometimes it's the most simple thing that we don't see right away!
I hope my explanation can help someone else who encounters this.

Thanks again!

Louise is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 03:39 AM.


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

Featured Forum post


Sponsored Links


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