Import text as a number

[#1]

Hi all,

I'm trying to import data from a large text file. It has fixed width columns so I've gone through the import wizard and set up the widths and relevant column names etc. Trouble is, it seems to be importing the Balance field as text.

So I took a look at the import again and saw the facility to specify data type. So I re-did it and selected Real for the Balance field. This seemed to make that field disappear from the wizard, which looked a bit odd.

But then i could see in the script QV has placed an "R" beside the relevant column width defnintion, so I now have something like this:

LOAD @1:4,
 @5:16,
 @17:70,
 @71:82,
 @83:104,
 @105:116,
 @117:138,
 @139:151,
 @152:181,
 @182:189,
 @190:200,
 @201:205 as Currency,
 @206:224R as Balance,
 @225:229 as BaseCurrency,
 @230:248 as BaseBalance,
 @249:253 as SIHCurrency,
 @254:272 as SIHBalance,
 @273:n
FROM
\\ldndata05\userdata$\macpheto\Documents\Project\Ledger\SAPFiles\TBs\Zplatod_09022010v2.txt
(fix, codepage is 1252);

You can see the "R" in the Balance field. However, this just errors when it runs, with the message:

Field not found - <@206:224R>

Any ideas? I'm really stumped and can't find anything in the help. And I can't do much with the Balance field if it is imported as text...

TIA,

Tony

[d_pranskus · Accepted solution]

Hi

Seems I managed to figure it out

Use following expression, when you load the data:

NUM(EVALUATE(@4:26), '##############') AS Amount

EVALUATE() to convert to number, NUM() to remove leading zeros

Cheers

Darius

[#3]

Hi Michael, Yes I tried that one. No change - there weren't any non-numeric characters in the field.

Thanks for the suggestion.

Tony

[#4]

Darius that's brilliant, works perfectly!

Thanks v much,

Tony

[d_pranskus]

Hi

Did you try NUM(EVALUATE(@4:26), '##############') AS Amount as I suggested? It worked for me.

Cheers

DArius

[#6]

Yes Darius, it worked for me too. Thanks again.

Any idea why my original method - specifying the column as a Real number - didn't work? The fixed-width import wizard in QV seems a little buggy to me, do you have any experience with that?