Openrowset

[sleezy]

I have a script to try and clean the csv file and it my problem
is I have to hardcode the file name as it changes dynamically.
I tried to cancatenate the variable that conatins the name but it gives me an error. Does it not accept variables

Incorrect syntax near '+'.

How can I fix this

SET NOCOUNT ON
/** Declare Working Variables For The Bulk Insert Statement **/
DECLARE
@VisaFile VARCHAR(200)
,@InsertVisaSQL VARCHAR(200)


--Set the Variables
SET @Filepath = 'D:\xxxx\Files\'
SET @VisaFile = 'AAA_00808_IXS_VISA_'+ RTRIM(CONVERT(CHAR(10),GETDATE()-2,112)) +'.csv'


---Truncate the Balances Table and Stage the data
TRUNCATE TABLE StageVISA

---Clean the file
SELECT * INTO ##TEST FROM
OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:\xxxx\Files;','SELECT * FROM ') + @VisaFile
WHERE F13 IS NOT NULL
AND [Report Title: Test Bank as Authoriser: Visa Transactions] NOT IN ('Pan')

--Truncate Stage Table
TRUNCATE TABLE StageVISA INSERT StageVISA SELECT * FROM ##TEST

--Drop the temp table
DROP TABLE ##TEST

[skhanal]

You can put everything in a variable and run it with EXEC (@sqlvar)

[sleezy]

i'VE TRIED TO BUILD IT INTO A VARIABLE. mY ONLY PROBLEM IS THAT MY WHERE CLAUSE IS INCOMPLETE WHEN I DO THE SELECT ON THE VARIABLE.


SET NOCOUNT ON
DECLARE @Cmd VARCHAR(2000)
,@File VARCHAR(200
)
SELECT @File ='AAA_00808_IXS_VISA_'+ RTRIM(CONVERT(CHAR(10),GETDATE()-1,112)) +'.csv'
SELECT @Cmd ='SELECT * INTO ##TEST FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};'
SELECT @Cmd = @Cmd + ' DefaultDir=D:\XXXX\Files;'',''SELECT * FROM '''
SELECT @Cmd = @Cmd + @File + ''')'+ ' WHERE F13 IS NOT NULL'
SELECT @Cmd =@Cmd + ' AND [Report Title: TEST Bank as Authoriser: Visa Transactions] NOT IN (''Pan'')'
SELECT @Cmd
---EXEC (@Cmd)

[nosepicker]

Looks like too many single quotes:

SET NOCOUNT ON
DECLARE @Cmd VARCHAR(2000)
,@File VARCHAR(200
)
SELECT @File ='AAA_00808_IXS_VISA_'+ RTRIM(CONVERT(CHAR(10),GETDATE()-1,112)) +'.csv'
SELECT @Cmd ='SELECT * INTO ##TEST FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};'
SELECT @Cmd = @Cmd + ' DefaultDir=D:\XXXX\Files;'',''SELECT * FROM '
SELECT @Cmd = @Cmd + @File + ''')'+ ' WHERE F13 IS NOT NULL'
SELECT @Cmd =@Cmd + ' AND [Report Title: TEST Bank as Authoriser: Visa Transactions] NOT IN (''Pan'')'
SELECT @Cmd
---EXEC (@Cmd)