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

[Miguel_Angel_Baeyens]

Hello Tony,

Have you tried

Num(@206:224) AS Balance

[#3]

Hi Miguel,

Yes, I tried that, with confusing results.... I changed my import script to:

...
@206:224 AS TextBalance,
Num(@206:224) AS Balance,
...

and it seems to convert some values, but not others. So now I'm seeing:

Currency	TextBalance	Balance
KWD	9328.91	9328.91
TND	24576.39	24576.39

which is great, but I'm also seeing:

Currency	TextBalance	Balance
EUR	000000001038327.27-
EUR	000000001038327.27
RUB	000000000066432.10-
RUB	000000162481698.09
RUB	000000222417753.69

where Balance is being calculated as null.

It looks like it's something to do with the TextBalance field, but I can't figure out any difference. Note - even though the first example is showing the TextBalance field as numbers in Excel all values in this field are actually preceded by zero's, as seen in the second example.

Weird... and infuriating!!!

Thanks for your help, any other suggestions?

Tony

[Anonymous]

Tony,
If there are non-mumeric characters in the string, it will not be converted into number. Is there a possibility that there are non-printable chartacters there? You can try keepchar() function:
num(keepchar((@206:224), '0123456789.'))

[flavio_fazzano]

Tony, have you verified that the Character "," (comma) is the rigth setting in ThousandSep=',' ?

[rwunderlich]

Tony,

QV won't recognize trailing minus signs. You have to do a bit of script like:

if(right(@206:224 ,1) = '-',
0-left(text,len(@206:224 )-1),
@206:224
) as Balance

-Rob

[#7]

All,

Thanks for the suggestions, I'll work on these and give you an update later.....

Tony

[#8]

Hi all,

This is still puzzling me I'm afraid... any other suggestions would be much appreciated.

In the source file I have:

THB 000001075919563.06 USD
THB 000001909199021.60-USD
KWD 00000000000000.000 USD
KWD 00000000009328.910-USD

and I have now changed the script to treat the minus sign as a separate column, which I also use to calculate the actual Balance from the TextBalance field:

@201:205 as Currency,
@206:223 as TextBalance,
@224:224 as Sign,
If ( @224:224 = '-',
 0 - Num(@206:223),
 0 + Num(@206:223))
 as Balance,

but it still isn't converting most of the values into numbers:

Currency	TextBalance	Sign	Balance
THB	000001075919563.06
THB	000001909199021.60	-
KWD	0		0
KWD	9328.91	-	-9,329

Note - in QV the TextBalance columns are ALL preceded by a bunch of zero's. But the really interesting thing is that those values that are being treated as numbers all have 3 decimal places, as you can see in the text file above. So it seems as though QV is automatically converting the entries with 3 decimal places into a number anyway, as you can see from the right-aligned numeric values in TextBalance. But those entries with 2 decimal places it is treating as text and it is not converting them when I use the Num function.

I'm going to try adding a zero on the end of the 2dp values to see if Num works then, but any more sensible suggestions would be greatly appreciated.....

Tony

[d_pranskus]

Hi

Would you attache some source file to test it?

Cheers

Darius

[Anonymous]

Have you tried num(keepchar(...))?