none
Newb: Bulk Insert from static folder with variable file name, help please.

    Question

  • Hello, I'm on SQL 2008 R2 Express. I set up my scripts using a specific file called file format, now that I have my bulk insert and stored procedure and everything working, I'm trying to change this so that I can grab any file in the folder. I tried the following but I get an error.... could someone help point me in the right direction?

    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 changed the script to:

    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

    But I get 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.

    What did I get mixed up in my syntax?

    Thank you!!!

    Friday, April 05, 2013 3:22 PM

All replies

  • Try this instead:

    DECLARE @dt AS VARCHAR(30)
    SELECT @dt = REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-')
    DECLARE @sql varchar(max)
    SET @sql = 
    'Bulk Insert Mydata.dbo.cust_adj 
    From ''C:\MyData\FlatFiles\UnprocessedAdjReport' + @dt + '.txt'''  
    + ' With 
    ( 
    FieldTerminator= ''|'', 
    Rowterminator= ''\n'' 
    ) '
    select @sql
    exec (@sql)


    Chuck Pedretti | Magenic – North Region | magenic.com

    Friday, April 05, 2013 3:51 PM
  • Well, I've tried a variety of different things and I can't seem to get it to work. Kept giving me a variety of syntax errors. Now I'm getting errors that the file doesn't exist. I am now seeing what would happen if the file name was this: bb-2013-0405. Every file would start with bb-

    How would I try doing that?

    SQL 2008 R2 Express. Bulk insert as a stored procedure. 

    Monday, April 08, 2013 2:22 PM
  • Try this instead:

    DECLARE @dt AS VARCHAR(30)
    SELECT @dt = REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-')
    DECLARE @sql varchar(max)
    SET @sql = 
    'Bulk Insert Mydata.dbo.cust_adj 
    From ''C:\MyData\FlatFiles\UnprocessedAdjReport' + @dt + '.txt'''  
    + ' With 
    ( 
    FieldTerminator= ''|'', 
    Rowterminator= ''\n'' 
    ) '
    select @sql
    exec (@sql)


    Chuck Pedretti | Magenic – North Region |

    In that solution there will be blank space (interval) in the name of the file right after the date part.

    Maybe you should trim blank spaces.

    Monday, April 08, 2013 2:57 PM
  • Why must you do this as a stored procedure? What other languages do you master?

    Of course, it's doable in T-SQL that there are alternatives that are less painful.

    In any case, I'm not exactly following where you are, so maybe you could post your current code?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 08, 2013 10:03 PM