importing fixed lenght text file - problem

  • Thread starter Thread starter Bob Shaffar
  • Start date Start date
B

Bob Shaffar

I'm trying to import a text file that has dollar amount
data. the format is ddddcc (dollar, dollar, dollar,
dollar, cents cents)

for example 000095 is 95 cents. When I import it as
currency or fixed lenth, 2 decimal places, it comes out
95 dollars.

Any way to get access to understand the first for places
are for dollars??

Bob
 
Hi Bob,

I'd probably import it as is to a Currency field, so 000095 would be
$95.00, and then use an update query to divide each value by 100.

Alternatively, if Perl was installed on the workstation, I'd process the
text file to insert the decimal before importing (change 99 to the
actual number of characters before where the decimal needs to go):

perl -ne"s/^(.{99})(.+)$/$1\.$2/" Inputfile.txt > Newfile.txt

I'm trying to import a text file that has dollar amount
data. the format is ddddcc (dollar, dollar, dollar,
dollar, cents cents)

for example 000095 is 95 cents. When I import it as
currency or fixed lenth, 2 decimal places, it comes out
95 dollars.

Any way to get access to understand the first for places
are for dollars??

Bob
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
That's what I wound up doing. Added a field in the query
with the /100 then hid the original field.

It just seems like a common import task to import a
field like that. Thought I was overlooking a setting or
field format switch. Ho Well

THANKS for your input, very much appreciated.

Bob
-----Original Message-----
Hi Bob,

I'd probably import it as is to a Currency field, so 000095 would be
$95.00, and then use an update query to divide each value by 100.

Alternatively, if Perl was installed on the workstation, I'd process the
text file to insert the decimal before importing (change 99 to the
actual number of characters before where the decimal needs to go):

perl -ne"s/^(.{99})(.+)$/$1\.$2/" Inputfile.txt > Newfile.txt
I'm trying to import a text file that has dollar amount
data. the format is ddddcc (dollar, dollar, dollar,
dollar, cents cents)

for example 000095 is 95 cents. When I import it as
currency or fixed lenth, 2 decimal places, it comes out
95 dollars.

Any way to get access to understand the first for places
are for dollars??

Bob
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top