Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


set isql output file format

Posted on 1999-10-15
Medium Priority
Last Modified: 2008-03-03
How can I set the output file format of a command-line isql call to output to csv, with the trailing spaces removed from each field?

Basically I want my output file to look like the output you would get if you set QueryOptions --> FormatOptions --> ResultOutputFormat --> CommaSeparated(CSV) within ISQL/w and saved its results. I just want to be able to do it from the command line.

Using MS SQL6.5 and have a Query input file. All required fields are of type VARCHAR(x)
Question by:Calum
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 2130332
I don't know if you can set the format of the output file but what you can do is to change your SQL to give you this output like:

select PIN+','+Name_First+','+Name_Last+','+convert(VarChar(3),Company_id) from employees


Accepted Solution

simonsabin earned 200 total points
ID: 2133943
iF you use the /s option you can specify comma column separatot but you will get padding, The only way not to get padding is to use BCP, you would then have to use a view as well i.e you BCP out a view on comma delimited format.

Author Comment

ID: 2134963
I have tried kubeerja's suggestion but you still get padding at the end of the resulting string. When your query returns half a million or so rows, this adds quite a substantial amount to the size of the resulting output file.

Likewise, have tried the /s option and, as simonsabin states, you still get padding with that too.

If there really is no way to get csv output from iSQL without padding, then so be it, but doesn't that seem rather odd that you can get it on-screen in iSQL/w, yet not be able to output it directly to file - good one Microsoft! Does anyone know if this situation is rectified in SQL7?

Thanks for your help guys.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 2137347

you can use rtrim to trim the spaces :

select rtrim(PIN)+','+rTrim(Name_First)+','+rTrim(Name_Last)+','+rtrim(convert(VarChar(3),Company_id)) from employees


Author Comment

ID: 2137861
Thanks, but you still get loads of spaces after the end of each line which you can't get rid of no matter how many RTRIMs you stick in your select statement.

SELECT RTRIM( rtrim( PIN )+','+rTrim( Name_First )+','+rTrim( Name_Last )+','+rtrim( convert( VarChar(3),Company_id ))) from employees
will leave you with spaces at the end!!

Expert Comment

ID: 6843324
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.
Thank you,
Moderator @ Experts Exchange

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question