question

red68 avatar image
red68 asked

Turn script to stored proc and pass in filename

How can I convert this TSQL script to a stored proc and pass in the filename instead of hard-code it? The name of file in script is: TestFile. Thanks! declare @headers varchar(max); declare @delimiter varchar(3); /* char(1) is not enough if text qualifier is present */ declare @createTable nvarchar(max); declare @rowTerminator varchar(6); declare @bulkInsert nvarchar(max); declare @lastColumn int; declare @updateScript nvarchar(max); -- read the header row, figure out delimiter and column names, and generate bulk insert script select @headers = replace(substring(t.BulkColumn, 1, charindex(char(10), t.BulkColumn) - 1), char(13), ''), @delimiter = case when charindex('","', @headers) > 0 then '","' when charindex('"|"', @headers) > 0 then '"|"' when charindex(',', @headers) > 0 then ',' else '|' end, -- outer replace is needed to remove 2 double quotes (in the beginning and the end of the headers) @createTable = replace('create table dbo.StagingTable(' + replace(@headers, @delimiter, ' varchar(50) null,') + ' varchar(50) null)', '"', ''), @rowTerminator = case when charindex(char(13) + char(10), t.BulkColumn) > 0 then '0x0d0a' else '0x0a' end, @bulkInsert = 'bulk insert dbo.StagingTable from ''\\server\Stagingfiles\FS\ TestFile.txt'' with ( fieldterminator = ''' + @delimiter + ''', firstrow = 2, rowterminator = ''' + @rowterminator + ''' );' from openrowset(bulk '\\server\Stagingfiles\FS\ TestFile.txt', single_clob) t; -- drop the staging table if already exists if exists( select 1 from sys.objects where [object_id] = object_id(N'dbo.StagingTable') and [type] = N'U' ) drop table dbo.StagingTable; -- create staging table exec sp_executesql @statement = @createTable; -- bulk insert records from delimited file exec sp_executesql @statement = @bulkInsert; -- Final touch up, which is needed only if the file included the double quotes as the -- text qualifier: if text is qualified then bulk insert worked but first column ended -- up prefixed and the lastcolumn - suffixed with the double quotes. Remove both. if charindex('"', @delimiter) > 0 begin select @lastColumn = max(column_id) from sys.columns where [object_id] = object_id(N'dbo.StagingTable'); select @updateScript = 'update dbo.StagingTable set ' + stuff(( select ', ' + quotename([name]) + ' = replace(' + quotename([name]) + ', ''"'', '''')' from sys.columns where [object_id] = object_id(N'dbo.StagingTable') and column_id in (1, @lastColumn) for xml path('')), 1, 2, ''); exec sp_executesql @statement = @updateScript; end; go
sql-server-2012
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@red68 I just posted the answer, please let me know if it works. Thank you.
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
The script in question looks like the script in the answer to the [related question][1] which was posted a couple of weeks ago. In this case, here is the same script restated as the stored procedure. All the changes which were needed to convert the script to the procedure are due to the fact that the openrowset does not accept the path to the file as a parameter, so the openrowset itself needs to be converted to dynamic script, that it all. The script to create procedure includes some comments which explain what has been done: if exists ( select 1 from sys.objects where [object_id] = object_id(N'dbo.usp_ImportRecordsFromFile') and [type] = N'P' ) drop proc dbo.usp_ImportRecordsFromFile; go set ansi_nulls on; set quoted_identifier on; go /* This procedure will read the records from the file provided as a procedure parameter, drop and create table named StagingTable, and insert the records from the file into it. The procedure will handle comma and pipe-delimited files and will also handle the files which have every value wrapped into double quotes (text qualified). It will accommodate the files from Windows (CRLF, a.k.a. \r\n or 0x0d0a used as line break) and Unix (LF, a.k.a. \n or 0x0a used as line break). It will not, however, accommodate the files from Mac (line breaks are carriage return \r). If handling of such files is needed then the case statement inside of the script setting the value of @headersSelect will have to be modified to accommodate handling of the files using just carriage return for line breaks. Sample usage (please note that the path to the file is from the server perspective): exec dbo.usp_ImportRecordsFromFile 'c:\temp\ 4358-unix.txt'; Notes: The file must exist, must be accessible by the SQL Server instance service account, and is expected to be comma or pipe-delimited with \n or \r\n used for line breaks. The errors are not handled, please add error handling as needed / required. */ create proc dbo.usp_ImportRecordsFromFile ( @fileName varchar(255) ) as begin set nocount on; -- local variables declare @headers varchar(max); declare @rowTerminator varchar(6); declare @delimiter varchar(3); declare @createTable nvarchar(max); declare @bulkInsert nvarchar(max); declare @lastColumn int; declare @updateScript nvarchar(max); -- The static select from openrowset will not work if the file name is provided via parameter -- because openrowset itself does not accept parameters, so the query needs to become dynamic. -- The values of the @headers and @rowTerminator are set by the dynamic script. declare @headersSelect nvarchar(max) = ' select @headersOut = replace(substring(t.BulkColumn, 1, charindex(char(10), t.BulkColumn) - 1), char(13), ''''), @rowTerminatorOut = case when charindex(char(13) + char(10), t.BulkColumn) > 0 then ''0x0d0a'' else ''0x0a'' end from openrowset(bulk ''' + @fileName + ''', single_clob) t;' -- read the first line of the file and set the values of @headers and @rowTerminator exec sp_executesql @statement = @headersSelect, @params = N'@headersOut varchar(max) out, @rowTerminatorOut varchar(6) out', @headersOut = @headers out, @rowTerminatorOut = @rowTerminator out; -- Header and row terminator values are already available; figure -- out delimiter and column names, and generate bulk insert script select @delimiter = case when charindex('","', @headers) > 0 then '","' when charindex('"|"', @headers) > 0 then '"|"' when charindex(',', @headers) > 0 then ',' else '|' end, -- outer replace is needed to remove 2 double quotes (in the beginning and the end of the headers) @createTable = replace('create table dbo.StagingTable(' + replace(@headers, @delimiter, ' varchar(50) null,') + ' varchar(50) null)', '"', ''), @bulkInsert = 'bulk insert dbo.StagingTable from ''' + @fileName + ''' with ( fieldterminator = ''' + @delimiter + ''', firstrow = 2, rowterminator = ''' + @rowterminator + ''' );' -- drop the staging table if already exists if exists( select 1 from sys.objects where [object_id] = object_id(N'dbo.StagingTable') and [type] = N'U' ) drop table dbo.StagingTable; -- create staging table exec sp_executesql @statement = @createTable; -- bulk insert records from delimited file exec sp_executesql @statement = @bulkInsert; -- Final touch up, which is needed only if the file included the double quotes as the -- text qualifier: if text is qualified then bulk insert worked but first column ended -- up prefixed and the lastcolumn - suffixed with the double quotes. Remove both. if charindex('"', @delimiter) > 0 begin select @lastColumn = max(column_id) from sys.columns where [object_id] = object_id(N'dbo.StagingTable'); select @updateScript = 'update dbo.StagingTable set ' + stuff(( select ', ' + quotename([name]) + ' = replace(' + quotename([name]) + ', ''"'', '''')' from sys.columns where [object_id] = object_id(N'dbo.StagingTable') and column_id in (1, @lastColumn) for xml path('')), 1, 2, ''); exec sp_executesql @statement = @updateScript; end; set nocount off; end; go Please change the name of the procedure to whatever it actually needs to be. Hope this helps. Oleg [1]: https://ask.sqlservercentral.com/questions/145000/load-txt-csv-files-with-minimal-effort-and-fast.html
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

red68 avatar image red68 commented ·
Perfect. I was close in converting over to dynamic sql but had some issues with quotes. Thanks so much. You are the man. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.