SQL Stored Procedure - Import Text File To Cols
Description
The SQL function is a utility that is used by several SQL import methods. This function may be added and used as needed.
Purpose
This routine imports a delimited file into a table containing up to 30 columns.
Input
- @FileName - The path and name of the file to import
- @Delimiter - Optional - The delimiter, which is a tab character by default
Output/Results
- Table(ID, Col01, Col02, … Col30)
Code
-- ============================================= -- Author: Cyrious Sofware -- Create date: May-2016 -- Description: This stored procedure imports a file FROM the disk. -- The contents of the file are returned either -- a) in a table with a single columns -- b) or a delimited table with up to 30 columns -- Returns: -- a) Table (value : VARCHAR(2000)); -- b) Table (rowid : int, value01 : VARCHAR(2000), value02 : VARCHAR(2000), ... value30 : VARCHAR(2000)); -- ============================================= CREATE PROCEDURE csp_ImportTextFileToCols @FileName VARCHAR(512), -- The path and name of the file to import @Delimiter CHAR(1) = NULL -- Set to the delimiter, which is a tab character by default AS BEGIN SET NOCOUNT ON; -- Clean up if this was run before this session. IF OBJECT_ID('tempdb.dbo.#Temp_ImportLines', 'U') IS NOT NULL DROP TABLE #Temp_ImportLines; IF OBJECT_ID('tempdb.dbo.#csp_ImportTextFileToCols_Results', 'U') IS NOT NULL DROP TABLE #csp_ImportTextFileToCols_Results; SET @Delimiter = COALESCE(@Delimiter, CHAR(09)); -- Set Default Delimiter if not set CREATE TABLE #Temp_ImportLines ( VALUE VARCHAR(MAX) ); -- Create initial table for lines CREATE TABLE #csp_ImportTextFileToCols_Results ( -- Create table with columns for final results RowID SMALLINT IDENTITY, Col01 VARCHAR(2000), Col02 VARCHAR(2000), Col03 VARCHAR(2000), Col04 VARCHAR(2000), Col05 VARCHAR(2000), Col06 VARCHAR(2000), Col07 VARCHAR(2000), Col08 VARCHAR(2000), Col09 VARCHAR(2000), Col10 VARCHAR(2000), Col11 VARCHAR(2000), Col12 VARCHAR(2000), Col13 VARCHAR(2000), Col14 VARCHAR(2000), Col15 VARCHAR(2000), Col16 VARCHAR(2000), Col17 VARCHAR(2000), Col18 VARCHAR(2000), Col19 VARCHAR(2000), Col20 VARCHAR(2000), Col21 VARCHAR(2000), Col22 VARCHAR(2000), Col23 VARCHAR(2000), Col24 VARCHAR(2000), Col25 VARCHAR(2000), Col26 VARCHAR(2000), Col27 VARCHAR(2000), Col28 VARCHAR(2000), Col29 VARCHAR(2000), Col30 VARCHAR(2000) ); -- Import the Data into a Table using SQL Bulk Import -- DECLARE @Cmd VARCHAR(1024) = 'BULK INSERT #Temp_ImportLines FROM '''+@FileName+''' WITH (FIELDTERMINATOR = ''|~|'', ROWTERMINATOR = ''\n'' );'; EXEC(@Cmd); -- Parse the data -- -- Unfortunately, the Bulk Import can't handle import CSV data with embedded quotes -- or handling files with variable column lengths, as is common with QuickBooks IIF files (for example) -- -- Unless we are using SQL 2016, parsing has to be done manually (which is slow and ugly, but works) -- -- Iterate through the rows and parse the input into columns DECLARE @ROW VARCHAR(MAX); DECLARE @SplitRow TABLE(id tinyint PRIMARY KEY, VALUE VARCHAR(255)); -- Create a cursor to iterate over each line in the import file DECLARE ImportLines Cursor FOR SELECT VALUE FROM #Temp_ImportLines; OPEN ImportLines; FETCH NEXT FROM ImportLines INTO @ROW; -- Process each line WHILE @@Fetch_Status = 0 BEGIN DELETE FROM @SplitRow; -- remove any existing rows INSERT INTO @SplitRow SELECT * FROM dbo.csf_String_Split(@ROW, @Delimiter, 1); INSERT INTO #csp_ImportTextFileToCols_Results VALUES( (SELECT VALUE FROM @SplitRow WHERE ID = 01), (SELECT VALUE FROM @SplitRow WHERE ID = 02), (SELECT VALUE FROM @SplitRow WHERE ID = 03), (SELECT VALUE FROM @SplitRow WHERE ID = 04), (SELECT VALUE FROM @SplitRow WHERE ID = 05), (SELECT VALUE FROM @SplitRow WHERE ID = 06), (SELECT VALUE FROM @SplitRow WHERE ID = 07), (SELECT VALUE FROM @SplitRow WHERE ID = 08), (SELECT VALUE FROM @SplitRow WHERE ID = 09), (SELECT VALUE FROM @SplitRow WHERE ID = 10), (SELECT VALUE FROM @SplitRow WHERE ID = 11), (SELECT VALUE FROM @SplitRow WHERE ID = 12), (SELECT VALUE FROM @SplitRow WHERE ID = 13), (SELECT VALUE FROM @SplitRow WHERE ID = 14), (SELECT VALUE FROM @SplitRow WHERE ID = 15), (SELECT VALUE FROM @SplitRow WHERE ID = 16), (SELECT VALUE FROM @SplitRow WHERE ID = 17), (SELECT VALUE FROM @SplitRow WHERE ID = 18), (SELECT VALUE FROM @SplitRow WHERE ID = 19), (SELECT VALUE FROM @SplitRow WHERE ID = 20), (SELECT VALUE FROM @SplitRow WHERE ID = 21), (SELECT VALUE FROM @SplitRow WHERE ID = 22), (SELECT VALUE FROM @SplitRow WHERE ID = 23), (SELECT VALUE FROM @SplitRow WHERE ID = 24), (SELECT VALUE FROM @SplitRow WHERE ID = 25), (SELECT VALUE FROM @SplitRow WHERE ID = 26), (SELECT VALUE FROM @SplitRow WHERE ID = 27), (SELECT VALUE FROM @SplitRow WHERE ID = 28), (SELECT VALUE FROM @SplitRow WHERE ID = 29), (SELECT VALUE FROM @SplitRow WHERE ID = 30) ); FETCH NEXT FROM ImportLines INTO @ROW; END; CLOSE ImportLines; DEALLOCATE ImportLines; DROP TABLE dbo.#Temp_ImportLines; SELECT * FROM #csp_ImportTextFileToCols_Results; END;
Source
Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+
You could leave a comment if you were logged in.