none
sqlcmd output to file formatting

    Question

  • I am using MS SQL server 2008. I hope I have this under the right category.

    I am trying to migrate a database that contains customer contact/system info from a legacy crm application (it uses access) to Dynamics CRM 2011. I found that if I want to assign various records/help tickets to our employee's I need to export the data for each user individually to a CSV file and import it. I am thankful that it is a simple process. But with 50 employee's it will take time to do each employee individually for each table, for this reason I wish to script the export process.

    It is important to note that our customer contact/system info more or less contains all of the symbols found on a standard American keyboard. I would like to stick with CSV files using comma's as my delimiter.

    I found that I can run the query in grid view within SQL Management Studio, then right-click in the grid "Copy with Headers", and then save to a CSV file. When I do this, the formatting is perfect. But when I try to run the query via sqlcmd, it loses the clean formatting that it has when the query is ran using the management studio.

    sqlcmd -S crm -E -Q "Select <Field1>,<Field2>,......<Field162> from <Table> where <Field> = '<Employee Name>';" -s "," -h-1 -W -o <Output Directory>

     

     


    Saturday, October 08, 2011 2:09 PM

All replies

  • perhaps use -s , for the column separator. Note that column names will not be exported. I strongly urge you to use SSIS for this.
    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Sunday, October 09, 2011 8:21 PM
  •  

    I took another look at the query and discussed it with a co-worker. It looked like my issue was carriage returns in text fields. I looked back through the various sqlcmd arguments and found -k helped with the issue.

    This has left me with another dilemma. I thought the -k (without using 1 or 2) would allow the query to ignore the carriage returns but it turns out that it completely removes them.

    Within our data base we have a single text field where we put our customers phone numbers, anywhere from 4 -100 phone lines. The list of phone lines never standardized layout. Each customer record has a different layout within our system for thier phone numbers. As such, I would like to be able to do this without removing the carriage returns.

    I am also looking into using SSIS as well.
    Monday, October 10, 2011 3:24 PM