I was loading data into QlikView from a large, comma-delimited text file last week. I found that some of the numeric quantities were expressed in

*scientific notation*or*E-notation*. Apparently, the person who had prepared the data used a reporting tool that converted some of the very large and very small numbers into scientific notation. The scientific notation numbers contain two numbers separated by an “E” character. The notation means to take the number on the left of the value and multiply by ten raised to the power of the number on the right of the value. For example, the E-notation value of**6.02E23**means that the numeric value is 6.02 times 10 to the 23rd power. It isn’t difficult to deal with in a loadscript but this example might save you some work someday. The loadscript looked something like this:PROMO_HIST:

LOAD MATERIAL,

WHSE,

If(index(upper(FQTY),'E')>0,

subfield(upper(FQTY),'E',1)*pow(10,subfield(upper(FQTY),'E',2)),

FQTY) as FQTY

FROM D:\Tim\Promo.csv (ansi,txt,delimiter is ',',embedded labels,msq);

That IF statement in the loadscript converted any e-notation values in the data into numbers and for regular numeric values loaded them as usual.

Later in the day, I needed to load the same data into an Oracle table using

*Sql Loader*. Interesting that the idea is the same but the syntax is different. This is what the entry in the SqlLdr control file looked like:

FQTY "DECODE(INSTR(UPPER(:FQTY),'E'),0,:FQTY, SUBSTR(:FQTY,1,INSTR(UPPER(:FQTY),'E')-1) * POWER(10,SUBSTR(:FQTY,INSTR(UPPER(:FQTY),'E'))+1))",

★ ★ ★

## 2 comments:

Just a thought, but if it's a one-off import, I'm pretty sure you can just open the CSV in Excel and change the formatting of the column(s). I'd guess that's where the scientific notation came from anyway - I think Excel does it by default on numbers too large for the width of the column.

The following seems to work fine. Is there any reason not to do it this way?

Item Number is in E-notation:

Num#([ITEM NUMBER])*1 as "ITEM NUMBER"

Post a Comment