SQL Direct CSV output not working properly

[samwoo]

Hello,

When I run the following code (for example):

select  h_sys_accounts.h_user_id as USERID,
        trim(h_attrib7) as COMPANY,
        h_account_status as STATUS
from    h_sys_accounts

When I export it to CSV, if the h_attrib7 field is null it will then populate the empty h_attrib7 field with the h_account_status instead of leaving it blank. If it is populated, then the next field as expected will contain the h_account_status.

This happens with every export I do, especially the h_sys_accounts... if i export this then where there is a blank field, it puts in the value from the next field leaving all the information out of sync and in the wrong fields.... and this is not consistent so I cannot filter information in excel.

Can this be fixed? I have a small workaround for the above script (run it once for null h_attrib7 and one for populated h_attrib7) but need to see the overall picture from h_sys_accounts on it's own.

Thanks,

Samuel.

 

[samwoo]

Sorry I forgot about using Service Manager Reports... i still prefer SQL though but will use Advanced Analytics this time round. Other created with SQL Direct (the more complicated ones) do come out with the issue i mentioned in the first post.

[Steve Giller]

There were issues with TRIM() and NULL values in SQL - I don't know if these were ever fixed.

Have you tried a CASE statement to return a blank string if NULL and a TRIMmed string if not?

[James Ainsworth]

I was wondering if this post has any relation to the issues that are mentioned here?  

[samwoo]

On 09/08/2016 at 8:39 PM, James Ainsworth said:

I was wondering if this post has any relation to the issues that are mentioned here?  

Hi James,

In my case, I am able to download the CSV file... when I see the data from the SQL query in Hornbill, everything is in the right columns, but when outputting to CSV the data doesnt sit in the right columns. For each record the data could be in different places in the CSV file.

For example:

Quote alegil Company 1 0 alehal 0   aleken Company 1 0 alelan Company 1 0 alemon Company 1 0 ALEPRE Company 1 0 aleree Company 1 0 alesex Company 1 0 aleshi Company 2 0 alethw Company 1 0 aliapp 0   ALIAUT Company 1 0

As you can see for alehal and aliapp, where it should be a 0 in the third column, its moved to the second column because they dont have a company name

What i guess is happening is that if the column is empty, the comma that's supposed to separate it is not added.

Quote

alegil, Company 1, 0
alehal, 0
aleken, Company 1, 0

aleshi, Company 2, 0
alethw, Company 1, 0
aliapp, 0

This seem to be happening with all the SQL Direct reports where a field could have an empty column sucn as the h_sys_accounts table when exporting to CSV.

Thanks,

Samuel

[TrevorKillick]

Hi Samuel

This is because the API for SQL Query does not return columns if there value is null so the CSV Generator doesn't know what to do. 

If you use the following query the CSV will be exported correctly

 

select  h_sys_accounts.h_user_id as USERID,
        coalesce(trim(h_attrib7),0) as COMPANY,
        h_account_status as STATUS
from    h_sys_accounts

I will see if there is anything we can do to always return columns if there value is null.

Kind Regards 

Trevor Killick