June 5, 2009 at 6:47 am
I have a stored procedure that someone else created. The procedure exports data to a csv file. Previously the export table worked with no headings. Another program would take the table and import the data to a database. An update has created the need to now have column headings as the first row of info and I don't know how to do it. Can someone help? Below is the line in the stored procedure I think needs changing.
Set @Cmd = 'BCP ' + @TableName + ' out ' + @FileName + ' -c -t, -T'
Thanks
siboyd07
June 5, 2009 at 9:42 am
Search BOL for BCP and look under command options. Edit: sorry only answered the bit about -c -t, -T (talk about not reading the question) not sure if you can get column headings.
June 5, 2009 at 10:33 am
I didn't find anything on how to create the column headings but I didnt even know about BCP so that was a help.
I still need the column headings to go with the export. The script is exporting from a view. Of course the views have a column heading but only the data goes to the csv file.
Thanks
June 5, 2009 at 10:47 pm
The easiest way to do this is to create a file in advance with the headers in it. BCP the data out to a separate file. Use the DOS copy command to combine the two files into one.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2009 at 2:11 am
Another option would be to add the column names using the UNION ALL function within the SELECT statement of the BCP command.
Something like
SELECT 'Col1','Col2','Col3' UNION ALL
SELECT CAST(Col1 AS VARCHAR(10)), Col2, Col3 FROM
Note: The CAST function is required for any numeric column (e.g. INT).
I'm not sure whether Jeff's way is easier or mine, but Jeff's avoids the CAST function, leaving the data in it's original format.
June 6, 2009 at 2:13 am
June 6, 2009 at 2:30 am
June 6, 2009 at 8:14 am
I came up with a way to accomplish this:
CREATE PROC dbo.BCPOUT AS
declare @header varchar(max),
@details varchar(max),
@CRLF char(2),
@RowTerminator char(1)
set @CRLF = char(13) + char(10)
set @RowTerminator = '|'
-- generate a comma-separated list of the column names of the table
set @header = stuff((
select ',' + name
from sys.columns
where object_id = object_id('v_Type') -- enter your table or view name here
order by column_id
FOR XML PATH('')
),1,1,'')
-- create a comma-delimited list of the columns in each row.
-- NOTE: this is limited to 2GB
-- NOTE: you will need to specify each column in the select list as ',' + whatever conversion is necessary to make it a character
-- NOTE: the last column needs a "row terminator" added to it... in my case, I used "|", but you can use whatever you desire.
set @details = stuff((
select ',' + convert(varchar(10), ID) +
',' + ltrim(rtrim(ID_Value)) + @RowTerminator
from dbo.v_Type
FOR XML PATH('')
),1,1,'') -- removes the first comma in the output
-- replace the "row terminators" (and comma for the first column of the next row) between rows with CRLF
-- replace the final "row terminator" with an empty string
set @details = replace(replace(@details, @RowTerminator + ',', @CRLF), @RowTerminator, '')
-- final result
select @header UNION ALL select @details
For the BCP command, change it to:
bcp "execute "YourDatabaseName".dbo.BCPOut" queryout "Your OutputFile" -c -T -S"Your Server"
For the view v_Type in the database on my system, it generated the following output:
ID,ID_Value
1,Commercial
2,Residential
This may have performance issues with large files, but you need to test it against what you have in your system.
I think that your better approach may be what Jeff suggested. If the columns don't change, you can have a file sitting out on the drive with the column names. Or, you can create the comma-separated column name header line from the first query in the proc I gave.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2009 at 9:54 am
Hi,
referring my previous post I would have done it as follows (example from AdventureWorks, thought it would clarify a little more what I was thinking about than just throwing those SELECT snippets in the post...)
DECLARE @cmd VARCHAR(8000),
@query VARCHAR(7800),
@FileName VARCHAR(100)
SET @FileName='C:\Temp\test_bcp.txt'
SET @query =
'"SELECT ''ShiftID'', ''Name'', ''StartTime'', ''EndTime'', ''ModifiedDate'' UNION ALL
SELECT CAST(ShiftID AS VARCHAR(5)), Name, CONVERT(VARCHAR(19),StartTime,120), CONVERT(VARCHAR(19),EndTime,120), CONVERT(VARCHAR(8),ModifiedDate,112)
FROM AdventureWorks.HumanResources.Shift"'
SET @Cmd = 'BCP ' + @query + ' queryout ' + @FileName + ' -c -T'
SELECT @Cmd
I copied the result into a command window (xp_cmdshell is disabled) and got the following in the file C:\Temp\test_bcp.txt
ShiftIDNameStartTimeEndTimeModifiedDate
1Day1900-01-01 07:00:001900-01-01 15:00:0019980601
2Evening1900-01-01 15:00:001900-01-01 23:00:0019980601
3Night1900-01-01 23:00:001900-01-01 07:00:0019980601
June 6, 2009 at 9:44 pm
siboyd07, I guess the main thing to mention here is that in order to include the column headings, you are going to have to change the way you are running BCP. You will need to either pass in a query that has the column names hard-coded or a way to get the column names, and have that unioned to a query to gets the columns from the table column by column, with appropriate conversion to character data. The BCP command will need to use the "queryout" parameter instead of the "out" parameter; additionally you will need to specify the specific query or stored procedure to run instead of just the view that you want to export.
Since you didn't know very much about BCP, it looks like you've got a bit of research and work ahead of you. There have been several different methods to accomplish what you're looking for here, I hope this all helps you out. Come back with more questions, and please let us know how your conversion is going, and how you decided to do it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 8, 2009 at 8:40 am
It's Monday morning and I'm backed to my problem. I don't know if I need to adjust the script, create another one, or use two to get the task accomplished. Here's my entire script. Do I need to change it and if so, where.
I really appreciate everyone's help.
CREATE proc [dbo].[Test_spExport_CSV_File]
@TableName as varchar(60),
@FileName as varchar(60),
@OutErr as varchar(255) = '' OUTPUT AS
Declare @Cmd as varchar(255)
--Create Table #TempOutTable ( outtext Varchar(255) )
Delete from DOSCMD_TempOutput--clear it
--Delete any previous versions
Set @Cmd = 'Del ' + @FileName
INSERT INTO DOSCMD_TempOutput
EXEC master.dbo.xp_cmdshell @Cmd
Delete from DOSCMD_TempOutput--clear it because we dont really care about Delete errors
--Use BCP to create the CSV file,
-- using "-c"=characters in file, "-t"=filed seperator, "-T"=Trusted connection
Set @Cmd = 'BCP ' + @TableName + ' out ' + @FileName + ' -c -t-F1, -T'
INSERT INTO DOSCMD_TempOutput
EXEC master.dbo.xp_cmdshell @Cmd--send the output of the DOS command to the @output Table
--check the results for errors
If EXISTS(Select * from DOSCMD_TempOutput Where Outtext LIKE 'Error%')
Set @OutErr = (Select * from DOSCMD_TempOutput Where Outtext LIKE 'Error%')
Else
Set @OutErr = ''
--NOTE:
-- to view or extract the data:
--Exec master.dbo.xp_cmdshell 'TYPE C:\Flight_Log.csv'
GO
June 8, 2009 at 3:20 pm
You've been given with 3 options.
Which one to choose - it's your call.
Actually, it's funny you nee to add headers to the data file.
It means that for years the data were prepared for computer processing, and now computers are gonna be replaced by humans.
Back to the stone age?
_____________
Code for TallyGenerator
June 8, 2009 at 3:53 pm
SiBoyd, personally I would go with Jeff Moden's solution of having a separate file that already has the column names in it, and then merge the exported file with that one.
Another option that no one has mentioned yet though is to discard BCP entirely and use either SSIS or DTS (depending on version of SQL). That is probably an inferior method to some of the other suggestions already made, but depending on the skills you already have (or want to learn) it may end up being easier to implement.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
June 10, 2009 at 1:34 pm
I managed to come up with a solution. I don't know if it's the best but it will work. Basically we have an import utility that imported the lines from a csv table without any headers because those were identified in order by the import. They decided to change the import, so now the import utility thinks the first line was a header line so it doesn't pick up the information on the first line. I created a table and added one bogus line of information to the table. Then I unioned that table with a view to create another view which included the one bogus line on the first line and the actual information starting on line 2. The import works, not importing the first row, which is OK. I'm sure there are better ways of doing the same thing that would work better, but for now..... that's as much skill as I have at this point.
Thanks everyone for the suggestions. These ideas gives me opportunites to try some things when I'm not under as much pressure to get things working again.
June 12, 2009 at 8:06 pm
siboyd07 (6/10/2009)
I managed to come up with a solution. I don't know if it's the best but it will work. Basically we have an import utility that imported the lines from a csv table without any headers because those were identified in order by the import. They decided to change the import, so now the import utility thinks the first line was a header line so it doesn't pick up the information on the first line. I created a table and added one bogus line of information to the table. Then I unioned that table with a view to create another view which included the one bogus line on the first line and the actual information starting on line 2. The import works, not importing the first row, which is OK. I'm sure there are better ways of doing the same thing that would work better, but for now..... that's as much skill as I have at this point.Thanks everyone for the suggestions. These ideas gives me opportunites to try some things when I'm not under as much pressure to get things working again.
Very cool... Thanks for posting your good solution.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply