Solved

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

Posted on 2004-04-07
12
786 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 47
MS SQL with ODBC 5 34
Sql server function help 15 28
VBScript Write Column Headers 3 32
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

18 Experts available now in Live!

Get 1:1 Help Now