February 22, 2010 at 5:27 am
Hello,
I've got a little problem when getting results from a .csv file, only because of spaces before the result.
The output of the query are the true results but as the client need it in a .csv file i cannot get the result that he wants only because of blank spaces.
Client want results in a csv file and i'm sending the output of the following query (Q1) into a file.
(Q1)
select (select distinct physicalhostname +';' from DTIAuxSpaceTable) as 'Physical Host Name',
(select ltrim(rtrim(srvname)) from master..sysservers where srvid=0) +';' as 'Instance Name',
(select cast(convert(varchar,count(name)) as varchar(5))+';' from master.dbo.sysdatabases) as 'Number of Databases',
cast(convert(varchar,sum(a.size)/(select count(name) from master.dbo.sysdatabases)) as varchar(10))+';' as 'Total Allocated Space (MB)',
cast(convert(varchar,sum(a.used)/(select count(name) from master.dbo.sysdatabases)) as varchar(10))+';' as 'Total Used Space (MB)',
cast(convert(varchar,(sum(a.size)-sum(a.used))/(select count(name) from master.dbo.sysdatabases)) as varchar(10))+';' as 'Total Free Space (MB)'
from DTIAuxSpaceTable a, master.dbo.sysdatabases b
I send you too the filename "CSVWithoutSolution.xlsx" where in the second tab (Final Result) appears the output of the problem.
Can you help me.
Thanks and regards,
JMSM 😉
February 22, 2010 at 7:53 am
Although your problem appears to be leading blank characters it is possible that they may be other non-printing characters. May I suggest you read the following and check your data for additional characters as noted in this article:
Remove spaces and nonprinting characters from text
February 22, 2010 at 8:45 am
Hello again,
I apreciate a lot the link that you send me but my part of this execution is the one that sends information to a .csv file.
Then client will get the information that client needs without doing anything else in this datafile (like removing spaces)
Is there any sintax so that a can send information to a file without sending header?
I tried to reduce the header size but it didnt work yet.
Hope that you can help me.
JMSM 😉
February 22, 2010 at 8:58 am
JMSM
My posting was intended to have you search for, and remove the non-printing characters in your T-SQL statement, much as you use the LTRIM and RTRIM. Not for the user of Excel to do the work.
February 22, 2010 at 9:19 am
Hello again,
Sorry for my interpretation.
The part of the code that i've gotting problems its exacly where i tried to use the ltrim(rtrim()) but i'm not getting what else can i do in my transact SQL to remove the non-printing characters.
.
.
(select ltrim(rtrim(srvname)) from master..sysservers where srvid=0) +';' as 'Instance Name',
.
.
I'll look again the link that you send me to see id i can get a solution.
Thanks and regards,
JMSM 😉
February 22, 2010 at 12:14 pm
I'll look again the link
Since you did not post your table(s) definitions, and your field may be NVARCHAR or VARCHAR please note the character codes for those characters that are NON_PRINTING, and explicitly test for them,. If you determine their are such characters present in the field you could use the REPLACE function enclosed within your LTRIM / RTRIM functions.
Hope this makes things a little clearer for you. If not come on back reporting what you have found and perhaps someone can assist you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply