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

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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.


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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 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