Importing a text-delimited file

[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.

 

[Ken Snell]

Post examples of the records that "work" and those that "don't work".

--
Ken Snell
<MS ACCESS MVP>

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??

 

[Mike Painter]

Post examples of the records that "work" and those that "don't work".

Also tell us if this is fixed or delimited text and what the delimiters are.

 

[Guest]

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

 

[Ken Snell]

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>

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

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.

 

[Mike Painter]

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.

 

[Ken Snell]

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.