Insert custom header row in BCP output

Question

Long story short we have a database here that manages some employee data such as email, first name, last name, etc. Our company bought into this SAP Based expense report system :| that needs an export of our employee based data in a very strange format. Without getting into too many details the export of this data needs a total of 137 columns, with many of these columns having an empty value.

Simple I put together a query that basically pulled this information from our database, and I set some constants to what was needed. It's not relevant what this query is in this question, its simply a SELECT statement that pulls some data.

I then needed to export this out on a daily basis with a specific file name and with a pipe delimited format, something to this effect:

--employee export
DECLARE @FileName varchar(500)
SET @FileName = (SELECT '\\someFileServer\Public\someFolder\employee_p06010603ace_305_202105_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, getdate(), 112), 126), '-', ''), 'T', ''), ':', '') + '.txt')
DECLARE @sql varchar(8000)
DECLARE @header varchar(8000)
SET @sql= 'bcp "exec [MyDBServer].[MyDbName].dbo.ConcurEmployeeExport" queryout ' + @FileName + ' -c -T -t "|"'
Exec master..xp_cmdshell @sql

Perfect, I get rows like this:

305|Jon| |Doe|10217487|10217487@t| 
305|Steve| |Smith|10217522|10217522@t|

With about 130 more columns, didn't want to display all of this since it is irrelevant. I thought I was done until the implementation coordinator said yes looks good except the first row needs to start with this strange row with some values.

Meaning the resultant query that I have should have one additional row with some values in it..simple I thought I would union what they wanted..and so I did.

Except the UNION ALL I had to put in some additional empty values to match the number of columns in my query. The issue is when I run the export I end up with a bunch of empty columns that are pipe delimited, I end up with this:

100|0|SSO|UPDATE|EN|N|N| | | | | | | | | | | | | | | | |

Imagine that going on for about 120 more columns. I submitted this to them and they said well everything looks good except your first row. We just need the first 7 values, that is they only want this:

 100|0|SSO|UPDATE|EN|N|N|

But I cannot do that with my UNION ALL, as my union all requires the same number of columns from both queries. So I thought I could somehow after generating this file just replace the |'s in the first line after the last N from above.

Is this possible without having to write another app / interface to do this? I want to keep what I have but only modify that first row such that the result I get is fixed, currently here is a sample of three rows:

100|0|SSO|UPDATE|EN|N|N| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 

305|Jon| |Doe|11111|11111@t| |[email protected]|en_US|USA| |TK_Symbolic|USD| |Y|USA|IS|0000202105|0120|CC|371000000| | | |030257| | | | | | | | | | | | |030257| | | |USA0000202105|N|N| | |N|N|N|N|N|N|N| | | | | |10217495| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |EOL

305|Steve| |Smith|22222|22222@t| |[email protected]|en_US|USA| |TK_Symbolic|USD| |Y|USA|IS|0000202105|0120|CC|371000000| | | |030177| | | | | | | | | | | | |030177| | | |USA0000202105|N|N| | |N|N|N|N|N|N|N| | | | | |10217495| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |EOL

I need to get it to this:

100|0|SSO|UPDATE|EN|N|N|

305|Jon| |Doe|11111|11111@t| |[email protected]|en_US|USA| |TK_Symbolic|USD| |Y|USA|IS|0000202105|0120|CC|371000000| | | |030257| | | | | | | | | | | | |030257| | | |USA0000202105|N|N| | |N|N|N|N|N|N|N| | | | | |10217495| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |EOL

305|Steve| |Smith|22222|22222@t| |[email protected]|en_US|USA| |TK_Symbolic|USD| |Y|USA|IS|0000202105|0120|CC|371000000| | | |030177| | | | | | | | | | | | |030177| | | |USA0000202105|N|N| | |N|N|N|N|N|N|N| | | | | |10217495| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |EOL

Notice the first row how I removed all those |'s. Ideally I would like to do this in maybe the query that exports the file. Something to the effect of removing all of this | | | on the first row after the export?

The first row is static, generated using:

SELECT
    '100' AS [Transaction Type],    --1
    '0' AS [Error Threshold],   --2
    'SSO' AS [Password Generation], --3
    'UPDATE' AS [Existing Record Handling], --4
    'EN' AS [Language Code],    --5
    'N' AS [Validate Expense Group],    --6
    'N' AS [Validate Payment Group],    --7

Unfortunately, my brain keeps telling me this is not a good idea / is not possible. I know I can easily do this in .net but I'd hate to have yet another program sitting out there doing these sorts of things.

Answer 1

You can create a CMD script to create a temp header row file, run BCP, and then append the BCP output to the temp header file. This would be called via xp_cmdshell, just like the existing call to BCP in your current setup.

Here is the CMD script, which I named AddHeaderToExportFile.cmd. It takes two parameters:

1. The filename.
2. The header row. If it changes, you only need to update the Stored Procedure.

Just create a new text file in Windows Explorer, then replace the name (including the .txt extension) with AddHeaderToExportFile.cmd. Then edit AddHeaderToExportFile.cmd and paste in the following code and save it.

AddHeaderToExportFile.cmd:

@ECHO OFF

SET TempHeaderRowFile="%TEMP%\TempHeader.txt"
SET TempOutputFile="%TEMP%\TempOutput.txt"


BCP "EXEC [MyDBServer].[MyDbName].dbo.ConcurEmployeeExport" queryout %TempOutputFile% -c -C 1252 -T -t "|"

ECHO %~2 >  %TempHeaderRowFile%

REM Concatenate Header + BCP_Output -> @FileName
REM /V = Verifies that new files are written correctly.
REM /Y = Suppresses prompting to confirm you want to overwrite an existing destination file
REM /B = treat files as Binary (else you get an extraneous CHAR(26) at the end)
COPY /V /Y /B %TempHeaderRowFile% + %TempOutputFile% %1

REM Delete the temporary Header and BCP output files
IF EXIST %TempHeaderRowFile% DEL /Q %TempHeaderRowFile%
IF EXIST %TempOutputFile% DEL /Q %TempOutputFile%

Adapting your original script to call the new CMD script, your new SQL should be something like:

Stored Procedure:

--employee export
DECLARE @FileName NVARCHAR(500)
SET @FileName = N'\\someFileServer\Public\someFolder\employee_p06010603ace_305_202105_' +
      REPLACE(REPLACE(REPLACE(
           CONVERT(NVARCHAR(19), CONVERT(DATETIME, GETDATE(), 112), 126),
                              N'-', N''), N'T', N''), N':', N'') +
      N'.txt';

DECLARE @Command NVARCHAR(4000),
        @Header   NVARCHAR(500);

SET @Header = N'100|0|SSO|UPDATE|EN|N|N|';

SET @Command = N'C:\TEMP\BCP\AddHeaderToExportFile.cmd "' +
               @FileName +
               N'", "' +
               REPLACE(@Header, N'|', N'^|') +
               N'"';

EXEC xp_cmdshell @Command; --, NO_OUTPUT;

---

OR, you can create a text file to hold the header row value, and then skip the CMD script altogether and use multiple calls to xp_cmdshell to accomplish the same thing:

--employee export
DECLARE @FileName NVARCHAR(500),
        @HeaderFile NVARCHAR(500);
SET @FileName = N'\\someFileServer\Public\someFolder\employee_p06010603ace_305_202105_' +
      REPLACE(REPLACE(REPLACE(
           CONVERT(NVARCHAR(19), CONVERT(DATETIME, GETDATE(), 112), 126),
                              N'-', N''), N'T', N''), N':', N'') +
      N'.txt';
SET @HeaderFile = N'\\someFileServer\public\someFolder\header.txt'; -- static header row

DECLARE @Command NVARCHAR(4000);

SET @Command = N'BCP "EXEC [MyDBServer].[MyDbName].dbo.ConcurEmployeeExport" queryout ' + @FileName + 'tmp -c -C 1252 -T -t "|"';
EXEC xp_cmdshell @Command; --, NO_OUTPUT;

SET @Command = N'COPY /V /Y /B ' + @HeaderFile + N' + ' + @FileName + N'tmp ' + @FileName;
EXEC xp_cmdshell @Command; --, NO_OUTPUT;

SET @Command = N'IF EXIST ' + @FileName + N'tmp DEL /Q ' + @FileName + N'tmp';
EXEC xp_cmdshell @Command; --, NO_OUTPUT;

Answer 2

One option you might consider is separating the two. Create one file with your export and all fields. The other file just has your header. The last step would be to combine the two with something like this:

REM create the header file
ECHO 100|0|SSO|UPDATE|EN|N|N| >"MyExport.txt.header"
REM append the bcp export to the header file
TYPE "MyExport.txt">>"MyExport.txt.header"
REM rename the header file back to the export file name
MOVE /y "MyExport.txt.header" "MyExport.txt"

You mentioned the export was done daily. Is there an SQL job schedule to do this? This could be a step added to the job using the Operating system (CmdExec) type. It doesn't have to be an "external" process if I understand you correctly. All of the code can be entered into the job step.

If you're not familiar with it, the other thing you can look into is SQLCMD. Bottom line is I believe you are correct: What you want probably can't be done using bcp directly in one step.