Jump to content

SQL Direct CSV output not working properly


Recommended Posts


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.




Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:

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.


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.



Link to comment
Share on other sites

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

  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...