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.
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 functionsBulk Insert Mydata.dbo.cust_adj From 'C:\MyData\FlatFiles\UnprocessedAdjReport\' + @dt + '.txt' With |
|
|
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' ) GoAnd I received this error:Msg 102, Level 15, State 1, Line 5Incorrect syntax near '+'.Msg 319, Level 15, State 1, Line 6Incorrect 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. |
|
|
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.txtSET @sql = 'BULK INSERT Mydata.dbo.cust_adj FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';EXEC(@sql); |
|
|
|
|
|
|
|