Solved

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

Posted on 2004-04-07
12
788 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 10779945
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
ID: 10780243
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
ID: 10781777
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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
LVL 13

Expert Comment

by:danblake
ID: 10781867
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
ID: 10781883
just tried it, didnt work for me.
0
 
LVL 4

Expert Comment

by:hanneman
ID: 10781888
ok that worked, commas are there, but the file is still a total mess.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10781899
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
ID: 10781908
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
ID: 10783963
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
ID: 10784213
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
ID: 10794774
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
ID: 13843249
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.

717 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