Insert only certain .csv file

Question

I am working on SQL Server 2008 R2.

I need to BULK INSERT only a certain .CSV file.

Here is my script :

BULK INSERT HPCM_HRSS.dbo.HRSS_TEST
        FROM '\\FOLDER\HRSS TEST\BISM043_Oracle FRG Workforce 201410.csv'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n',
                FIRSTROW = 2
    )
GO

There is only 1 file with this naming and I archive this file after the import. The file name changes from 'BISM043_Oracle FRG Workforce 201410.csv' to 'BISM043_Oracle FRG Workforce 201411.csv'

How do I change the script in order to import any file with the naming 'BISM043_Oracle FRG Workforce' in that specific folder?

Any advise would be greatly appreciated.

Not 100% a duplicate, I guess, but the answer is the same. See http://stackoverflow.com/a/5547437/864696 — Ross Presser, Dec 11 '14 at 06:44
@RossPresser, If I use dynamic SQL as illustrated on your link, where do I use '*' ? My file name that I want to import will change each month from 'BISM043_Oracle FRG Workforce 201411.csv' to 'BISM043_Oracle FRG Workforce 201412.csv' to 'BISM043_Oracle FRG Workforce 201501.csv' etc... — PKirby, Dec 11 '14 at 06:50
If you know the file pattern is based on the current day, you can build up the filename using `GETDATE()` to get the current day. If not, i.e. the filename may be from any day, then you'd need to use `xp_cmdshell`, `xp_dirtree`, or a CLR procedure to actually inspect the files in the directory. — Ross Presser, Dec 11 '14 at 06:55
If this was my task, I would *not* do it entirely in T-SQL. I would use a DOS batch or Powershell command to locate the file, then invoke SQLCMD with parameters that pass the file in to the `BULK IMPORT` statement. Look up "SQLCMD Mode" to see how replaceable parameters can make this easy. — Ross Presser, Dec 11 '14 at 06:57

score 1 · Answer 1 · answered Dec 11 '14 at 07:05

To amplify on the SQLCMD mode I was talking about. Here is a CMD batchfile that locates the file, then invokes SQLCMD:

echo on
setlocal enabledelayedexpansion
rem ---
rem this finds the latest .bak file in the backup directory
rem ---
set SQLBACKUPPATH=\\SERVER\sqlbackups\SQLSERVERNAME\DBNAME
for /F %%f in ('dir /b /o:-d !SQLBACKUPPATH!\*.bak') do set BAKFILE=!SQLBACKUPPATH!\%%f  

rem ---
rem this passes the found bak file as the parameter BAKFILE to the SQL file in SQLCMD mode
(sqlcmd -v BAKFILE ="!BAKFILE!" -i UpdateDevDBFromProdBak.sql -I -S SQLSERVER -E  -d master )

And here is the UpdateDevDBFromProdBak.SQL file invoked in the last line:

ALTER DATABASE [MyDB]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE [MyDB] FROM DISK = '$(BAKFILE)'WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5;
GO

ALTER DATABASE [MyDB] SET  RECOVERY SIMPLE
GO

ALTER DATABASE [MyDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

USE [MyDB]

EXEC sp_change_users_login 'auto_fix','mydbuser'
GO

I realize this is not a good "answer" in the stackoverflow mold, since it is about how I solved a different problem. But it shows the technique: use DOS batch to find the file, then pass the file in SQLCMD mode. If my answer gets voted down because of this, I will delete it and rewrite to directly answer the original question. — Ross Presser, Dec 11 '14 at 07:06

PKirby · Accepted Answer · 2014-12-11T08:57:59.703

Thank you Ross Presser, your answer did lead me in the right direction.

Here is what I did in order to get the correct result:

DECLARE @SQL        nvarchar(max),
        @FileName   nvarchar(200)

        CREATE TABLE #Temp_FileName
        (
            [FileName] nvarchar(200)
        )

INSERT INTO #Temp_FileName
EXECUTE XP_CMDSHELL 'dir \\FAS-RBGFS01\costec\HRSS\ /b'

DELETE FROM #Temp_FileName
WHERE [FileName] NOT LIKE 'BISM043_Oracle FRG Workforce%'


SELECT TOP(1) @FileName = [FileName] FROM #Temp_FileName ORDER BY [FileName] DESC

SET @SQL = 'BULK INSERT HPCM_HRSS.dbo.HRSS_TEST
        FROM ''\\FAS-RBGFS01\costec\HRSS\' + @FileName + '''
            WITH
    (
                FIELDTERMINATOR = '','',
                ROWTERMINATOR = ''\n'',
                FIRSTROW = 2
    )'

EXEC (@SQL)

DROP TABLE #Temp_FileName

Insert only certain .csv file

2 Answers2