Data360 Analyze

Hi,

When importing data from an excel file, Null values are not correctly shown? 

Is there a way to transform these blanks to null values?

When I ran the Excel File node against a file where the 'blank' cells in the excel file were actually empty then the cells were imported as Null values. However, if the were not empty but instead contained whitespace characters then the cells appeared 'blank'. If in the Data Viewer you use the 'Statistics...' option in the field's context menu it should report any blank and/or fields with leading/trailing whitespace.

However, you can use the Calculate Fields node to change the whitespace values to Null per the screenshot

In this case the node uses a Python in-line if statement of the form:

<expression1> if <condition> else <expression2>   The condition uses the strip() function to remove any leading/trailing whitespace characters from the specified field and, if the result is an empty string, the named field is set to Null, otherwise the named field is set to the original value. In this case the named field to be output has the same name as the original input field so it has the effect of replacing the input field in the output data.    Note the in-line if statement must have an else expression or the node will generate an error. If the input field name has the same name as a reserved Python keyword or contains space characters then the field in the expression must be referenced using the alternative format field['my field name'] for example:

Thank you for the response, the proposed solution works.

However, the excel that is uploaded does not contain any whitespace characters.
Running the Profiler node gives me the following output:

This indicates Null values instead of blanks. How come that I see blanks instead of Null values.

Hi Felix, I cannot explain the difference between the results you see and those I get. It would be good if you could post a cut-down and sanitized version of the file with a few records that exhibit the condition on your system. Can you also confirm which version of the Analyze software you have installed.

Many thanks,

Adrian

Hi, I am working on v3.5.1.5606.

Find attached some dummy data, and an extract of the canvas that show the above screenshots.

Attached files

Null issue - 22 Nov 2019.lna
Dummy data.xlsx

Hi Felix,

The cells in the 'Valuation Type' column are not actually empty but they are blank (i.e. empty strings). 

If you look at the output of the Excel File node they are reported as blanks in the statistics info. Depending on your screen resolution you may need to scroll down in the dialog or zoom out to see all the stats.

The default setting for the Data Profiler node is to consider empty strings as Null values:

If you also look at the Excel Node output and examine the values in the 'Special Stock' field you will see that the fourth record is being reported as being Null.

Save the Excel File in Excel with a new file name and (after unhiding the 'Valuation Type' property) select all ten cells. Then  use Right-click -> Clear contents and save the file. When this file is then imported into Analyze the values are reported as Null:

Returning to the 'Special Stock' field, if you add a filter node and then specify a condition in the Advanced tab of the Filter Criteria to match an empty string the records with the empty string values are output and the record with the Null value is not output.