Automating an import routine for text files into SQL SERVER tables with Stored Procedures
-
I created a stored procedure which will import an text file from the network into MSSQL and insert the data into a new table.
The text files are pipe, “|”, delimited and the first row contains the column names.
See sample script below :-
------------------------------------------------------------------------------
CREATE PROC usp_testImportScript
AS
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CSVTest]') AND type in (N'U'))
DROP TABLE dbo.CSVTest
-- Create a new template table
CREATE TABLE CSVTest
(TCODE Varchar (5),
PSPNR Varchar (255),
ZZLOC Varchar (255),
ZZPLN_QTY Varchar (255))
-- Load data into table
BULK
INSERT CSVTest
FROM 'C:\Users\Ayodeji\Desktop\Importing Proj\Book1.csv'
WITH
(
FIRSTROW = 2, -- Removes the Header Row
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
--------------------------------------------------------------
The problem is-
I have 28 Text files with different columns layout for each one that needs to be imported into their own table in SQL server.
Is there a way to create a file name variable so MSSQL will pick up the right text file from the network drive and use the correct column layout?
All the text files are stored on the network drive in one folder and they all have the same files name structure as follows:-
Projects_Russia_8_01_Country_Prog_V2_0_LFD_Project_Definition_SD
Projects_Russia_8_01_Country_Prog_V2_0_LFD_WBS_Elements_TD
Projects _Country_8_01_Country_Programme_V2_0_LFD_CP_Goals_SD
“Projects – “ Fixed Text
“Country” name of the country being migrated
“Template No. and name”
“The Excel Tab name”
“Source Data or Target” Source Data or Target Data “SD”, “TD”
Please note that for the automatic load of these files
“Country”“Template No. and name” “LFD_CP_Goals “SD” -These parts of the file names will change depending on which of the 28 files is been imported.
I’m trying to avoid using SSIS if possible.
thanks for any advise
-
there are 3 options i see, dynamic SQL based on the file name or 28 stored procedures, one for each file. for the dynamic SQL it may get complicated depending on the columns for the table but can be done. the other option is the 28 stored procedures for the different files.
the third and personally how i would do it, create the 28 stored procedures then have a 29th master stored procedure with a file name parameter with a case statement. similar to this
CREATE PROCEDURE usp_FilePicker (@FileName VARCHAR(255))AS
--check if @FileName is null
IF @FileName IS NULL
BEGIN
SELECT 'Your Error Text here' AS ERROR
RETURN
END
--Declare our dynamic sql holder
DECLARE @Cmd VARCHAR(MAX)
-- set up exec command
SET @Cmd = 'EXEC '
--Get the stored procedure to run add when statements for each
SET @Cmd = @Cmd + CASE @FileName
WHEN 'FileA' THEN 'usp_1'
WHEN 'FileB' THEN 'usp_2'
END
--Execute the Stored Procedure
EXEC( @Cmd )
Takes a little longer to set up but if a file format changes you only need to change the files stored procedure and not the entire stored procedure with dynamic sql (option 1). also if there is ever a 29th file you just add the SP for that file and another when statement.
Your bulk insert from the stored procedure would also need a pipe (|) and not a comma (,) as your field delimiter if your delimiter in your post is correct.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Your a great help Grasshopper that exactly what I needed since I don't mind creating 29+ stored procedures.
Is there a way to add an wildcard or variable to the name files so that the stored will pick any file in the folder for example with "F1" or "F2" meaning format layout 1 or 2 and use the correct stored procedure like below?
--Get the stored procedure to run add when statements for each
SET @Cmd = @Cmd + CASE @FileName
WHEN 'FileA_F1' THEN 'usp_1'
WHEN 'FileB_F2' THEN 'usp_2'
END
Thanks for your help
-
ok ill get past the multiple logins which seem to be the same person.
to get the various options you want you just keep adding case statements, so if you wanted to import all the F1 files you could have as follows (code changed to move the first EXEC to the case statement)
CREATE PROCEDURE usp_FilePicker (@FileName VARCHAR(255))AS
--check if @FileName is null
IF @FileName IS NULL
BEGIN
SELECT 'Your Error Text here' AS ERROR
RETURN
END
--Declare our dynamic sql holder
DECLARE @Cmd VARCHAR(MAX)
--Get the stored procedure to run and set the command to run, Keep adding when's for any desired options
SET @Cmd = CASE @FileName
WHEN 'FileA' THEN 'EXEC usp_1'
WHEN 'FileB' THEN 'EXEC usp_2'
WHEN 'F1' THEN 'EXEC usp_1; EXEC usp_2; EXEC usp_3'
ELSE 'SELECT ''Nothing Matched'''
END
--Execute the Stored Procedure('s)
EXEC( @Cmd )
GO
the options are really endless as long as you can keep adding to the case statement for your options.
you also avoid any SQL Injection problems because you never add the input to the dynamic SQL.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Thanks for getting back to me, the problem I'm having now is that the filename structure below will keep changing through the depending
Projects_Russia_8_01_Country_Prog_V2_0_LFD_Project_Definition_SD
“Country”“Template No. and “LFD_CP_Goals “SD” -These parts of the file names will change depending on which of the 28 files is been imported and I don't want to change the name every week, can I use a wildcard or variable on the filename?
SET @Cmd = CASE @FileName
WHEN 'FileA' THEN 'EXEC usp_1'
WHEN 'FileB' THEN 'EXEC usp_2'
WHEN 'F1' THEN 'EXEC usp_1; EXEC usp_2; EXEC usp_3'
ELSE 'SELECT ''Nothing Matched'''
END
Thanks again
-
azdeji (3/12/2012)
Thanks for getting back to me, the problem I'm having now is that the filename structure below will keep changing through the dependingProjects_Russia_8_01_Country_Prog_V2_0_LFD_Project_Definition_SD
“Country”“Template No. and “LFD_CP_Goals “SD” -These parts of the file names will change depending on which of the 28 files is been imported and I don't want to change the name every week, can I use a wildcard or variable on the filename?
SET @Cmd = CASE @FileName
WHEN 'FileA' THEN 'EXEC usp_1'
WHEN 'FileB' THEN 'EXEC usp_2'
WHEN 'F1' THEN 'EXEC usp_1; EXEC usp_2; EXEC usp_3'
ELSE 'SELECT ''Nothing Matched'''
END
Thanks again
When you said 28 files i made a guess that the names would not change. what i am now wondering is how many different column layouts there are, with 28 files i was making a guess that there were 28 different layouts. sample data from the files would be helpful here.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
I have attached 14 of the Kenya texts files out of 28 which need to imported into SQl server but there is another 51 countries with 28 tables with the same layout. That why have a variable files names would coming in useful since each country has 28 tables with the layout and would use the same table layout.
-
ok i have an idea of how to attack this. it may have the potential for sql injection as we are going to pass a file name as a parameter. so the questions are, 1) will users be calling this, and 2) are you ok with that. still going to have the 29 SP's but the main SP is going to get 2 paramaters and pass one on to the secondary SP (one of the 28 file type SP's)
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Hi C.Hector,
Yes other users will need to EXEC the stored proc, and I don't mind using sql injection with 29 stored proc.
Your help is greatly appreciated!
-
and I don't mind using sql injection with 29 stored proc.
with sql injection an attacker can execute arbitrary code including possibly dropping tables if the permissions are not set correctly. think carefully when using the dynamic SQL. with that in mind here are the stored procedures.
/* This is the sample import procedure change the table names and structure to your requirements*/USE tempdb -- Some where every one has, change to your DB name
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Master]') AND type in (N'U'))
DROP PROCEDURE [dbo].[usp_Master]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SampleFormat1]') AND type in (N'U'))
DROP PROCEDURE [dbo].[usp_SampleFormat1]
GO
CREATE PROCEDURE usp_SampleFormat1 (@FileName VARCHAR(255))
AS
DECLARE @CMD VARCHAR (MAX)
/* Only drop the table if it is a temporary holding table. Otherwise skip the drop and create */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'U'))
DROP TABLE [dbo].[usp_Test]
CREATE TABLE Test --some name that will make sense in your database
-- Set the data types correctly for your import data type
(
TCODE VARCHAR (4),
PSPID VARCHAR (5),
ZZGOAL_NO INT,
ZZCSP_GOAL VARCHAR (255),
ZZINACT VARCHAR (10),
ZZIND1 VARCHAR (10),
ZZIND2 VARCHAR (10),
ZZIND3 VARCHAR (10),
ZZIND4 VARCHAR (10),
ZZIND5 VARCHAR (10),
ZZGOAL_TEXT VARCHAR (255),
ZZCOMM VARCHAR (255))
--The Set command adding the contents of @FileName is what opens up the possibility of SQL Injection
SET @CMD = 'BULK INSERT Test FROM ''' + @FileName + '''WITH(FIRSTROW = 2, FIELDTERMINATOR = ''|'',ROWTERMINATOR = '''')'
EXEC (@cmd)
GO
CREATE PROCEDURE usp_Master (@Format VARCHAR(16),@FileName VARCHAR(255))
AS
IF @Format IS NULL OR @FileName IS NULL -- Trap any Null values
BEGIN
SELECT 'Your Error Text Here' AS ERROR
RETURN
END
-- Add IF statements just like this one for each of your 28 file types
IF @Format = 'NameThatMeansSomethingForFileType' -- Set this to something that makes sense
BEGIN
EXEC usp_SampleFormat1 @FileName -- Add IF statements just like this one for each of your 28 file types
RETURN -- stop running at this point since the format name wont match any thing else in the file
END
IF @Format = 'AnotherThingThatMeansSomethingForFileType' -- Set this to something that makes sense
BEGIN
EXEC usp_NameOfSecondFormatUSP @FileName
RETURN
END
ELSE
BEGIN
SELECT 'Nothing Matched' AS ERROR -- tell the user nothing matched
END
GO
the first procedure is the sample insert procedure where you have the table formatting and names, this is the procedure you create 28 (or however many different formats you have) times.
the second is the procedure you actually run, pass it a format name and the file name you want to upload.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Thank for a great scrip !!!!!! but I have come back a exhausted man since my understanding of variables in SQL non-existent – so my question is here do I input my file location and file names? Apologises for being totally rubbish!!
So I put the loaction of my files in “SET @CMD” like below?
SET @CMD = 'BULK INSERT Test FROM ' C:\myimports\''+ @FileName + '''WITH(FIRSTROW = 2, FIELDTERMINATOR = ''|'',ROWTERMINATOR = '''')'
EXEC (@cmd)
GO
-----------------------------And for the file names like below??
CREATE PROCEDURE usp_Master (@Format VARCHAR(16),@FileName VARCHAR(255))AS
IF @Format IS NULL OR @FileName IS NULL -- Trap any Null values
BEGIN
SELECT 'Your Error Text Here' AS ERROR
RETURN
END
-- Add IF statements just like this one for each of your 28 file types
IF @Format = 'File_8_01' -- Set this to something that makes sense
BEGIN
EXEC usp_File_8_01 @FileName -- Add IF statements just like this one for each of your 28 file types
RETURN -- stop running at this point since the format name wont match any thing else in the file
END
IF @Format = 'File_8_02' -- Set this to something that makes sense
BEGIN
EXEC usp_Fle_8_02 @FileName
RETURN
END
IF @Format = 'File_8_03' -- Set this to something that makes sense
BEGIN
EXEC usp_Fle_8_03 @FileName
RETURN
END
ELSE
BEGIN
SELECT 'Nothing Matched' AS ERROR -- tell the user nothing matched
END
GO
Thanks again for the help.
-
ok so you only want to pass the file name and have the folder path static? As i wrote it you pass the entire file path to the stored procedure as such:
usp_Master 'File_8_01', 'c:\my\file\path\myfile.txt'
for a static file path the set would look like this:
SET @CMD = 'BULK INSERT Test FROM ''C:\myimports\'+ @FileName + ''' WITH(FIRSTROW = 2, FIELDTERMINATOR = ''|'',ROWTERMINATOR = '''')'
EXEC (@cmd)
GO
the '' you see are double '. the first one escaping the second so it does not end the ' giving errors.
For the master stored procedure you got it exactly right. just keep copying and pasting and changing your ifs.
EDITED: Forgot the code tag
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
I’m back again! This forum is fast becoming my most visited website.
I’m receiving an error message when I execute the master stored proc – see script below and error message after
I created two samples test Stored proc – ‘usp_SampleFormat1 and 2’ see below
CREATE PROCEDURE usp_SampleFormat2 (@FileName VARCHAR(255))AS
DECLARE @CMD VARCHAR (MAX)
/* Only drop the table if it is a temporary holding table. Otherwise skip the drop and create */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test2]') AND type in (N'U'))
DROP TABLE [dbo].[Test2]
CREATE TABLE Test2 --some name that will make sense in your database
-- Set the data types correctly for your import data type
(TCODE Varchar (5),
PSPNR Varchar (255),
ZZLOC Varchar (255),
ZZPLN_QTY Varchar (255))
--The Set command adding the contents of @FileName is what opens up the possibility of SQL Injection
SET @CMD = 'BULK INSERT Test FROM ''C:\myimports' + @FileName + '''WITH(FIRSTROW = 2, FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'
EXEC (@cmd)
GO
---I modify your master Stored proc below -
Create PROCEDURE [dbo].[usp_The1toRuleThemAll] (@Format VARCHAR(16),@FileName VARCHAR(255))AS
IF @Format IS NULL OR @FileName IS NULL -- Trap any Null values
BEGIN
SELECT 'Your Error Text Here' AS ERROR
RETURN
END
IF @Format = 'Book1' -- Set this to something that makes sense
BEGIN
EXEC usp_SampleFormat1 @FileName -- Add IF statements just like this one for each of your 28 file types
RETURN -- stop running
END
IF @Format = 'Book2' -- Set this to something that makes sense
BEGIN
EXEC usp_SampleFormat2 @FileName
RETURN
END
ELSE
BEGIN
SELECT 'Nothing Matched' AS ERROR -- tell the user nothing matched
END
When I execute the master stored proc I receive this error message instead
- Msg 201, Level 16, State 4, Procedure usp_The1toRuleThemAll, Line 0
Procedure or function 'usp_The1toRuleThemAll' expects parameter '@Format', which was not supplied.
Once again thanks
-
you did not pass the parameters to the stored procedure. see my above post on how to execute the master SP.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
I put this project on the back burner since it was give me nightmares but I can’t put it off any longer. Thanks for your last advice, the execution of the stored proc now works but it not returning a match see screen print and I also receive the error message below –
Msg 217, Level 16, State 1, Procedure usp_torulethemalltest, Line 26
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Thanks
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply