I am working on a program which loads a bunch of files in a SQL database with an average amount of rows totaling over 150,000 + records into a table and then a process runs to match SSN numbers based on how many times an SSN shows up in the table. Once the process is finished running, those 150,000 + records are then exported to a text file which we then send to a company for processing. I am using the BCP (Bulk Copy) utility with a format file to load the records into the table and to export the processed records to the text file at the end of the process.
The problem I am facing is the file export produces the records we want but the character count is off as each record within the file is only 99 characters long when it should be 102 characters log. With the additional characters missing the company we send the file to can not process it on their end.
My question is, how can I add the additional white space without adding too many additional steps to my current process as it is a bit resource intensive. My first thought is to amend the current format file I am using to include three additional white spaces at the end of each row, but in reading comments over the internet I am not sure this is possible. My next thought was to loop through each of the rows in the text file to add the additional white space, but that seems a bit inefficient as it would be easier to add the white spaces while the file export occurs instead of looping through all the records as I am sure that is quite resource intensive. What would be the best approach?
Format File
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="14" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" MAX_LENGTH='6' TERMINATOR="\n" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Agency" xsi:type="SQLCHAR" />
<COLUMN SOURCE="2" NAME="Fund" xsi:type="SQLCHAR" />
<COLUMN SOURCE="3" NAME="Account" xsi:type="SQLCHAR" />
<COLUMN SOURCE="4" NAME="LName" xsi:type="SQLCHAR" />
<COLUMN SOURCE="5" NAME="FName" xsi:type="SQLCHAR" />
<COLUMN SOURCE="6" NAME="SSN" xsi:type="SQLCHAR" />
<COLUMN SOURCE="7" NAME="Prior" xsi:type="SQLCHAR" />
<COLUMN SOURCE="8" NAME="BalDue" xsi:type="SQLCHAR" />
<COLUMN SOURCE="9" NAME="DRSBal" xsi:type="SQLCHAR" />
<COLUMN SOURCE="10" NAME="Fill1" xsi:type="SQLCHAR" />
<COLUMN SOURCE="11" NAME="FileDate" xsi:type="SQLNCHAR" />
</ROW>
</BCPFORM>
Stored Procedure which processes the file export
DECLARE @fileName varchar(75) = 'ITAS_FILENAME';
DECLARE @processTime varchar(14) = REPLACE(Convert(varchar, getdate(), 108), ':', '');
DECLARE @processDate varchar(8) = CONVERT(varchar, GETDATE(), 112);
DECLARE @bulkCopyCmd varchar(512);
DECLARE @filePath varchar(100) = 'E:\DRSFILE\OUT\';
SET @fileName = @fileName + @processDate + @processTime + '.txt';
SET NOCOUNT ON;
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
RECONFIGURE
SET @bulkCopyCmd = 'bcp "Execute dbo.sp_process_file_export "processResult"" queryout "'
+ @filePath
+ @fileName + '"'
+ ' -S [Server IP] -T -d "DRSFile" -f "E:\DRSFILE\Utilites\drsFile_SQL_FormatFile.xml"'
--Insert File Details
INSERT INTO FileExportDetails (userName, fileName, runDate)
VALUES (@userName, @fileName, @processDate + @processTime)
--Run Bulk Copy Command
EXEC master..xp_cmdshell @bulkCopyCmd
IF @@ROWCOUNT = 0
--Error, something went wrong
SET @processResults = 0
ELSE
--Success
SET @processResults = 1
Just add another field to your format file and query.
To your query, just add a new last column
select
col_a
, col_b
, ...
, Filedate
, cast( '' as char( 3 ) ) Fill2 -- add this new column
from your_table
Then also add to your format file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="14" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="16" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="11" xsi:type="CharFixed" LENGTH="6" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="12" xsi:type="CharTerm" MAX_LENGTH='3' TERMINATOR="\n" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Agency" xsi:type="SQLCHAR" />
<COLUMN SOURCE="2" NAME="Fund" xsi:type="SQLCHAR" />
<COLUMN SOURCE="3" NAME="Account" xsi:type="SQLCHAR" />
<COLUMN SOURCE="4" NAME="LName" xsi:type="SQLCHAR" />
<COLUMN SOURCE="5" NAME="FName" xsi:type="SQLCHAR" />
<COLUMN SOURCE="6" NAME="SSN" xsi:type="SQLCHAR" />
<COLUMN SOURCE="7" NAME="Prior" xsi:type="SQLCHAR" />
<COLUMN SOURCE="8" NAME="BalDue" xsi:type="SQLCHAR" />
<COLUMN SOURCE="9" NAME="DRSBal" xsi:type="SQLCHAR" />
<COLUMN SOURCE="10" NAME="Fill1" xsi:type="SQLCHAR" />
<COLUMN SOURCE="11" NAME="FileDate" xsi:type="SQLNCHAR" />
<COLUMN SOURCE="12" NAME="Fill2" xsi:type="SQLCHAR" />
</ROW>
</BCPFORM>