• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 798
  • Last Modified:

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

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    
0
geoenvi
Asked:
geoenvi
  • 4
  • 3
  • 2
  • +3
1 Solution
 
arbertCommented:
You accepted this answer on the question and it didn't work?

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20944254.html


Also, close some of your old questions.
0
 
geoenviAuthor 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...
0
 
hannemanCommented:
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. "
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
danblakeCommented:
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)
0
 
hannemanCommented:
just tried it, didnt work for me.
0
 
hannemanCommented:
ok that worked, commas are there, but the file is still a total mess.
0
 
danblakeCommented:
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.
0
 
hannemanCommented:
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.
0
 
Anthony PerkinsCommented:
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
http://www.experts-exchange.com/Networking/Broadband/VPN/Q_20881809.html

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

3 01/07/2004 500 Exchange Setup  Open Exchange Server
http://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)
http://www.experts-exchange.com/help.jsp#hi71

2:
Please follow up.
0
 
geoenviAuthor 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?
0
 
geoenviAuthor 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..
0
 
InformationSystemsCommented:
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.
0
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

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now