Importing a text-delimited file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use an import specification in Access to pull in information in a notepad file. One particular field in the file is a currency. I have my import specification set up for currency for that field also. For most of the records, this fields' information comes in fine, however a few of the fields don't - they are left blank. When I look at the error table that it creates, it says that it is a Type Conversion Error on the currency field. If I go back and look at the notepad file, the currency number looks just fine. Anyone know why I'm getting this error?

Thanks in advance for your help.
 
Post examples of the records that "work" and those that "don't work".

--
Ken Snell
<MS ACCESS MVP>

Amy said:
I am trying to use an import specification in Access to pull in
Click to expand...
information in a notepad file. One particular field in the file is a
currency. I have my import specification set up for currency for that field
also. For most of the records, this fields' information comes in fine,
however a few of the fields don't - they are left blank. When I look at the
error table that it creates, it says that it is a Type Conversion Error on
the currency field. If I go back and look at the notepad file, the currency
number looks just fine. Anyone know why I'm getting this error??
 
Ken Snell said:
Post examples of the records that "work" and those that "don't work".
Click to expand...
Also tell us if this is fixed or delimited text and what the delimiters are.
 
Here is the data from the notepad file
PRICED ACTIVE 05/04 10.33 10.18
PRICED ACTIVE 11/04 7.00 7.19

Here is the data in the Access DataBase
PRICED ACTIVE 05/04
PRICED ACTIVE 11/04 7.00 7.1

Here is an insert from my import/export specification
Field Name Data Type Start Width Indexed Ski
Price Status Text 120 7 No
Filled Text 127 11 No
Futures Month Text 138 9 No
Price Currency 147 8 No
Futures Price Currency 155 9 No

Here is a copy of the error I get in the error table
Error Field Ro
Type Conversion Failure F6 12

Again, when the information is imported sometimes it will just leaves fields blank. I have noticed that a lot of the numbers in the price column that are not importing are 4 digits - 99.99. In fact, that is how we noticed it, we've never imported 10.00 before! However I cannot find any consistancy with the column on the far right (futures price). Hope this helps

Thanks
 
I assume that this is an excerpt from a full line of data, as the character
start positions don't match your spec's numbers.

Data look ok, as does the spec. So, what are the properties that you have
set for the Price and Futures Price fields in the table? I'm guessing that
you have a setting that is preventing any number greater than 9.99 from
being stored.

--
Ken Snell
<MS ACCESS MVP>

Amy said:
Here is the data from the notepad file:
PRICED ACTIVE 05/04 10.33 10.18
PRICED ACTIVE 11/04 7.00 7.19

Here is the data in the Access DataBase:
PRICED ACTIVE 05/04
PRICED ACTIVE 11/04 7.00 7.19

Here is an insert from my import/export specification:
Field Name Data Type Start Width Indexed Skip
Price Status Text 120 7 No 0
Filled Text 127 11 No 0
Futures Month Text 138 9 No 0
Price Currency 147 8 No 0
Futures Price Currency 155 9 No 0

Here is a copy of the error I get in the error table:
Error Field Row
Type Conversion Failure F6 124


Again, when the information is imported sometimes it will just leaves
Click to expand...
fields blank. I have noticed that a lot of the numbers in the price column
that are not importing are 4 digits - 99.99. In fact, that is how we
noticed it, we've never imported 10.00 before! However I cannot find any
consistancy with the column on the far right (futures price). Hope this
helps.
 
If you let the wizard determine the field positions and the first few were
9.99 then it would have assumed the field started there. Go back and change
the numbers to start a column or two earlier.
 
Only thought I have is that the character position is not consistent in all
records. Other than that, you may need to post the entire info about the
spec and a full copy of some example data records.
 
Back
Top