Solved

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

Posted on 2004-04-07
12
784 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    
0
Comment
Question by:geoenvi
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
Comment Utility
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
 

Author Comment

by:geoenvi
Comment Utility
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
 
LVL 4

Expert Comment

by:hanneman
Comment Utility
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
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
 
LVL 4

Expert Comment

by:hanneman
Comment Utility
just tried it, didnt work for me.
0
 
LVL 4

Expert Comment

by:hanneman
Comment Utility
ok that worked, commas are there, but the file is still a total mess.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
 
LVL 4

Expert Comment

by:hanneman
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Comment

by:geoenvi
Comment Utility
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
 

Author Comment

by:geoenvi
Comment Utility
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
 

Expert Comment

by:InformationSystems
Comment Utility
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now