Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Dynamic file name for importing

    Posted - 04/16/2012 : 06:03:19
    --------------------------------------------------------------------------------

    I have a stored procedures that will import 28 CSV files with different columns layout for each one, into their own table in SQL Server 2008 and a master stored procedure which will execute the other 28 store procedures.
    All the CSV files are stored on the network drive in one folder.
    The naming of these files will vary from week to week and I don’t want to manually change the names in the stored procedures. Each table name has a unique Identifier F1 to F28 that will be a constant before the rest of the name for example –
    F1_country23
    F2_region12

    Can I use the unique Identifier to add an wildcard or variable to the stored proc so it 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?

    WHEN 'F1' THEN 'usp_F1_ImportScript'
    WHEN 'F2' THEN 'usp_F2_ImportScript'



    Example SQL scripts so far –

    Code:
    Create PROC [dbo].[usp_F1_ImportScript]
    
    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\Desktop\F1_Book1.csv' 
    WITH
    (
    FIRSTROW = 2, -- Removes the Header Row
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )
    ----------------------------the SP which will run the 28 SP

    Code:
    CREATE PROCEDURE [dbo].[usp_FilePicker] (@FileName VARCHAR(255))
    
    AS
    --check if @FileName is null
    IF @FileName IS NULL
    BEGIN
    SELECT 'No Files Match' 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 'F1_Book1.csv' THEN 'usp_F1_ImportScript'
                           WHEN 'F2_Book2.csv' THEN 'usp_F2_ImportScript'
    					   END
    I cannot use SSIS because other users from different countries (50+ so far) may need to add/delete columns in the raw text/cvs files (for their own test environment) meaning there may be changes needed to made to the SQL scripts and with documentation any user without experience with MSSQL would be able to make simple changes.

    I understand that dynamic SQL maybe the solution but I lack skill in this area and even my colleague who more proficient in this area can’t help.
    Any help would appreciated.

    Thanks,

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Try this for example. You may have to make a few code changes but this is the general idea. Here is a link to understanding dynamic sql: http://www.techrepublic.com/blog/dat...sql-server/306

    Code:
    CREATE PROCEDURE [dbo].[usp_FilePicker] (@FileName VARCHAR(255))
    
    AS
    --check if @FileName is null
    IF @FileName IS NULL
    BEGIN
    SELECT 'No Files Match' AS ERROR
    RETURN
    END
    --Declare our dynamic sql holder
    DECLARE @Cmd NVARCHAR(255) 
    
    --Get the stored procedure to run add when statements for each
    SET @Cmd =  'usp_' + SUBSTRING(@FileName,1,2) + '_ImportScript'
    EXECUTE @Cmd
    Last edited by corncrowe; 04-18-12 at 11:12.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •