We help IT Professionals succeed at work.

EXPORT STORED PROCEDURE (WITH PARAMETERS) RESULTS TO CSV FORMAT ** JUMBLED FORMAT***

geoenvi
geoenvi asked
on
Medium Priority
835 Views
Last Modified: 2012-05-04
I am using the following command to export the results of the stored procedure

set @sqlstring = 'osql -E -Q' + '"exec odb.dbo.MystoredProc' + '''04/01/01'''+','+'''04/01/04''' + '" -o "C:\Results1.CSV"'

execute @sqlstring

The file that is returned does not look like a CSV file, it shows up in a weird format.  like this:

What am I doing wrong?  I tried inserting a specific comma charater into the SP but since a number of fields are dates, int etc, I cannot concatenate without a major effort (It is a very long SP)


 Fname                lname                
      address                                                                
                                    
      address2                                                              
                                    City                          
      State                                              Zip        
      cardnum     expdate    
      sAddress                                                              
                                    
      sAddress2                                                              
                                    sCity                          
      sState                                             sZip       Item    
      Price   sTotal Tax  TOTAL
      Comments                                                              
                                                      
      Name                                                                  
           SHIPVIA                   cardtype    
      DESCRIP                                                                
                                                                            
                                                                            
                                                 QTYORD      
      Country                        phone      
      sname                                                                  
           sCountry                       sphone      
 -------------------- --------------------
      -----------------------------------------------------------------------
      -----------------------------
      -----------------------------------------------------------------------
      ----------------------------- ------------------------------
      -------------------------------------------------- ----------
      ----------- -----------
      -----------------------------------------------------------------------
      -----------------------------
      -----------------------------------------------------------------------
      ----------------------------- ------------------------------
      -------------------------------------------------- ---------- -------
      ------- ------ ---- -----
      -----------------------------------------------------------------------
      -----------------------------------------------
      -----------------------------------------------------------------------
      ---- ------------------------- -----------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      -----------------------------------------------------------------------
      ------------------------------------------ -----------
      ------------------------------ -----------
      -----------------------------------------------------------------------
      ---- ------------------------------ -----------
Judy Pain                                                              
           USA                                   NULL
 Judy                 Pain                
      bbbb                                                                  
                                    
                                                                            
                                    Phonex                        
      AZ                                                            
             NULL        NULL
      bbbb                                                                  
                                    
      NULL                                                                  
                                    Phonex                        
      AZ                                                            NULL    
      NULL    0      0    0    
Comment
Watch Question

Top Expert 2004
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
The accepted answer created a text file.  I am looking to see why the CSV format is not there...

I closed all my previous questions with satisfactory answers...

Commented:
I dont know if this works, but I found this bit of info:
"An easy way to have your output appear in tab delimited format using osql is to add the argument [ -s " " ] to your osql script using a text editor. The space between the double quotes is a tab. "

Commented:
so if you want the file as a comma seperated version format you require the argument to be -s "," (And, no I don't want the points)

Commented:
just tried it, didnt work for me.

Commented:
ok that worked, commas are there, but the file is still a total mess.

Commented:
I have just tried this as a .CSV file (with ',') sepearting all objects from the following:
osql -E -Q"Select top 10 * from sysobjects" -s ","  >output1.txt

Seems to work for me..
output1.txt is formated well.

Commented:
looks to me like the text file has the value of the field, then spaces up to the maximum length of the field.
my datetimes come out fine e.g ,[datetime], but the text fields are what really blow the layout.
CERTIFIED EXPERT
Top Expert 2012

Commented:
As arbert has reminded you already, please maintain your very old open questions and you will find experts more responsive:

1 02/11/2004 500 NETGEAR R0318 and WIN XP  Open VPN
https://www.experts-exchange.com/Networking/Broadband/VPN/Q_20881809.html

2 06/17/2003 500 Text Rotation Mirror Images  Open Crystal Reports
https://www.experts-exchange.com/Databases/Crystal_Reports/Q_20651257.html

3 01/07/2004 500 Exchange Setup  Open Exchange Server
https://www.experts-exchange.com/Networking/Email_Groupware/Exchange_Server/Q_20843722.html

1 and 3:
Request a refund because no one answered your question (Delete)
https://www.experts-exchange.com/help.jsp#hi71

2:
Please follow up.

Author

Commented:
Dan and Hanneman

Your suggestion certainly worked but like H said, the problem of text fields is what is blowing the layout. Any ideas on how to get around this? Also, when you export it, the field names are shown as the first row.  Is this avoidable?

Author

Commented:
Arbert,
Your solution proposed in the referred question exactly did what I wanted.  Instead of using OSQL or ISQL, I should have used bcp.  ISQL/OSQL provided me with a text file but the format was jumbled.  Since I did not credit you for your answer in the previous question, I am crediting you with all the points here.

Thanks a lot..
You could use osql as proposed above using the directives -s "," -w 500.
-w indicated the maximum linesize of the output.  The default value is 80 so your lines of output over 80 characters are getting wrapped to a new line.  Also in the stored proc if you trim the text values of any padded spaces your file format should look better.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.