Cleaning Excel Files in Script

[brunopaulo]

Hello community, i have something like this

Name     |     Competence      |      Evaluation       

Bruno             Competence1               4         

Bruno             Competence2             jfkfhkahhf

Bruno             Competence3               3

Bruno             Competence4

Bruno             Competence5          30/11/2017                

Andrew          Competence1          28/11/2017        

Andrew          Competence2             hdgdgd

Andrew          Competence3              

Andrew          Competence4                    4

Andrew           Competence5                   1

And so on. In script im doing something like this

Table:

Load *

Resident TableTemp

where len(Evaluation)<2;

Im excluding the fields that doesn't mather to me but i want include blank fields. Anyone has other idea how to do this. At the moment isnt including blank fields.

Expected output

Name     |     Competence      |      Evaluation       

Bruno             Competence1                4         

Bruno             Competence3                3

Bruno             Competence4

Andrew           Competence3

Andrew           Competence4               4

Andrew           Competence5               1

Thanks in advance

Best Regards

Bruno Paulo

[stabben23]

Hi Bruno, whatif you trim the Field Evaluation in Your where condition? it colud be that the Field is include more than 2 blanksteps.

where len(trim(Evaluation))<2;

[YoussefBelloum]

Hi,

it depends on what you really have on that blank values, but you can try this:

where len(Evaluation)<2 and not isnull(Evaluation);

[balar025]

Hello,

Try this.

Load

*

Where IsNum(Evaluation) or Len(Evaluation)=0;

Load * Inline [

Name,  Competence,  Evaluation

Bruno,Competence1,  4

Bruno,Competence2,jfkfhkahhf

Bruno,Competence3,  3

Bruno,Competence4,

Bruno,Competence5,  30/11/2017 

Andrew,  Competence1, 28/11/2017

Andrew,  Competence2,hdgdgd

Andrew, Competence3,

Andrew,  Competence4,   4

Andrew,   Competence5,  1

];

[brunopaulo]

well this solution looks perfect. But i discovered a new problem. Im doing cross table and is there where it excludes the values.

I have

Name           |          Competence 1               |      Competence2               |               Competence3

Bruno                              4                                             jshdjhd

Andrew                              5                                                                                          14/11/2017

So when i Cross table this the problem appears.

Somethings that worth a mention. I'm reading several excel files and competence can be variable depending on excel.

If you could help i apreciate. Thank you

[YoussefBelloum]

Try this:

where Len(Trim(Field)) > 0 and Len(Trim(Field)) < 2 and not isnull(Evaluation);

[brunopaulo]

It gives me error because the fields arent created.

CrossTEMP2:

CrossTable (Competence, Evaluation,3)

LOAD *

RESIDENT CrossTEMP;l

So if i try do something like dat Competence and Evaluation aren't created. And fields that go to Competence column are variable, depend on excel.

[YoussefBelloum]

CrossTEMP2:

CrossTable (Competence, Evaluation,3)

LOAD *

RESIDENT CrossTEMP;

CrossTEMP3:

LOAD *

resident CrossTEMP2

where Len(Trim(Evaluation)) > 0 and Len(Trim(Evaluation)) < 2 and not isnull(Evaluation);

drop table CrossTEMP2;

[brunopaulo]

Didnt solve, ill make more tests. Thank you for the help!

[brunopaulo]

I tested only with 1 excel file and when i do cross table eliminates all null values and when i do the first load (before cross table command) the null values are there.