SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Bulk Insert of .txt when file name changes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

EGoetz
Starting Member

USA
7 Posts

Posted - 04/04/2013 :  17:02:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/05/2013 :  00:45:17  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 04/05/2013 :  11:18:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/08/2013 :  05:08:27  Show Profile  Reply with Quote
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);

Edited by - bandi on 04/08/2013 05:12:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000