bulk insert files with filenames that change everyday

Question

I need to bulk insert a text file, which is always stored in the same folder. The file name is 'employee_date', where the date part is not always the actual date. It is a dynamic date that the user changes every day (with the format YYYYMMDDHHMM).

What I need is a query that bulk inserts the data in that text file (that is already formatted). My problem is bulk insert does not work with variables or with *.txt or employes*.txt.

I need a query that bulk insert only the file which name is like EMPLOYEE_YYYYMMDDHHMM.txt that can be executed every day and always insert the file from that folder, doesn't matter what the date is in the file name.

You can either use dynamic SQL for the whole command, or for example use bcp instead — James Z, Oct 31 '16 at 14:51
You could use [SSIS](https://msdn.microsoft.com/en-gb/library/ms169917.aspx?f=255&MSPPError=-2147217396) to automate the import process. SSIS is a flexible tool. But that flexibility comes at a cost. It is more complicated, at first, that the approach @JamesZ suggested. — David Rushton, Nov 01 '16 at 13:38
Does this answer your question? [How to cast variables in T-SQL for bulk insert?](https://stackoverflow.com/questions/5019041/how-to-cast-variables-in-t-sql-for-bulk-insert) — wibeasley, Aug 07 '20 at 22:42

score 0 · Answer 1 · answered Oct 31 '16 at 15:17

Here is something you can modify to fit your requirements. I had a similar task where we got files SFTP to us from a Linux system and I needed to upload this data into a SQL Server. Here's the basic layout... understanding your file locations, permissions on the folder, SQL permissions, etc all need to be taken into consideration. Including if you can run command shell code in your environment.

CREATE procedure [dbo].[file_upload]
as

DECLARE @dt VARCHAR(10)                                                         --date variable but stored as VARCHAR for formatting of file name
DECLARE @fileLocation VARCHAR(128) = 'E:\SomeFolder\'                           --production location which is 
DECLARE @sql NVARCHAR(4000)                                                     --dynamic sql variable
DECLARE @fileName VARCHAR(128)                                                  --full file name variable

--This stores the file names into a temp table to be used in a cursor.
--The bottom part is handleing some date formatting i needed. You can change to what your files look like
IF OBJECT_ID('tempdb..#FileNames') IS NOT NULL DROP TABLE #FileNames
CREATE TABLE #FileNames (
    id int IDENTITY(1,1)
    ,subdirectory nvarchar(512)
    ,depth int
    ,isfile bit
    ,fileDate date null
    ,fileTime time null)
INSERT #FileNames (subdirectory,depth,isfile)
EXEC xp_dirtree @fileLocation, 1, 1

UPDATE #FileNames SET 
                        fileDate = CAST(SUBSTRING(subdirectory,LEN(subdirectory) - 19,10) AS DATE)
                        ,fileTime = CAST(REPLACE(SUBSTRING(subdirectory,LEN(subdirectory) - 8,5),'-',':') AS TIME)


--here's the cursor to loop through all the files
DECLARE c CURSOR FOR
select subdirectory from #FileNames

OPEN c
FETCH NEXT FROM c INTO @fileName

--For each file, bulk insert or what ever you want...

WHILE @@FETCH_STATUS = 0
    BEGIN
       --set the dynamic with the appropriate delimiters, if you want to keep headers, etc.
       SET @sql = 'BULK INSERT Server.dbo.someTable FROM '''+ @fileLocation + @fileName +''' WITH (FIELDTERMINATOR = ''|'',FIRSTROW=2,KEEPNULLS,ROWTERMINATOR = ''0x0a'')' 
       EXEC(@sql)

       --do some other stuff like logging, updating, etc...
    END

CLOSE c
DEALLOCATE c

score 0 · Answer 2 · answered Dec 21 '16 at 04:53

This should do it for you. Just adjust as needed.

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=48)
BEGIN

PRINT @intFlag


declare @fullpath1 varchar(1000)
select @fullpath1 = '''your_path_here\employee_' + convert(varchar, getdate()- @intFlag , 112) + '.txt'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[your_table_name] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 2, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)


SET @intFlag = @intFlag + 1

END
GO

bulk insert files with filenames that change everyday

2 Answers2