Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Bulk Insert of .txt when file name changes

Author  Topic 

EGoetz
Starting Member

7 Posts

Posted - 2013-04-04 : 17:02:28
Hello,

I have a folder that is static with a daily txt file that goes into the folder. The file name is the date. If the file name has the same name every day, everything works. Is there a way I can have my script pull any txt file in the folder? (Note, file comes in, get's processed and then I have an automatic transfer that moves the file after it has been inserted to a processed folder). So there will only be one file at a time in the folder. I hope that makes sense.

Here is the script for the bulk insert:
Bulk Insert Mydata.dbo.cust_adj
From 'C:\MyData\FlatFiles\UnprocessedAdjReport\importformat.txt'
With
(
FieldTerminator= '|',
Rowterminator= '\n'
)
Go

(I've got this saved as a stored procedure btw)

So "importformat" is just the name I used while setting up my scripts, going forward it will be in bb-yyyy-mmdd-hhmmnnnn.txt, as soon as the file is inserted, I move the file from the unprocessed folder to the processed folder. There will only be the one file each day.


If anyone has any advice or assistance with this, I would greatly appreciate it.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-05 : 00:45:17
DECLARE @dt AS VARCHAR(30)
SELECT @dt = REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-')

you create file name foramt by using datetime functions

Bulk Insert Mydata.dbo.cust_adj
From 'C:\MyData\FlatFiles\UnprocessedAdjReport\' + @dt + '.txt'
With
Go to Top of Page

EGoetz
Starting Member

7 Posts

Posted - 2013-04-05 : 11:18:41
Ok, I tried this:

DECLARE @dt AS VARCHAR(30)
SELECT @dt = REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-')

Bulk Insert Mydata.dbo.cust_adj
From 'C:\MyData\FlatFiles\UnprocessedAdjReport' + @dt + '.txt'
With
(
FieldTerminator= '|',
Rowterminator= '\n'
)
Go

And I received this error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '+'.
Msg 319, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

So I've got the syntax wrong. I'm on SQL 2008 R2 Express, by the way.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-08 : 05:08:27
DECLARE @FileName AS VARCHAR(30), @sql NVARCHAR(4000)='';
SET @FileName = 'C:\MyData\FlatFiles\UnprocessedAdjReport' + REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-') +'.txt'; -- File name Format is YYYY-MM-DD HH-MI-SS.txt
SET @sql = 'BULK INSERT Mydata.dbo.cust_adj FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';
EXEC(@sql);
Go to Top of Page
   

- Advertisement -