How to replace in files with [] in file name?

[hurcane]

I need to replace text in a series of database script files. The files have names like...

[dbo].[tablename1].sql
[dbo].[storedproc1].sql

The process file seems to work fine. But the replace in files action is converting these names to "..sql" when I use the %PROCFILES_FILENAME% macro.

Any tips to how I can process these files?

[kevina]

Per the help file (Replace in File Action section):
<snip>
Note: To insert literal bracket [ ] and percent sign % characters within this field, they must be doubled up (just as in any other field), since these are normally interpreted by Visual Build Pro as referencing script code and macros within a field.
</snip>

Simply double up any occurence of [ ] or % in the find or replace text, and check the "Treat all character as literals (no regular expressions)" check box.

[hurcane]

kevina,

Thanks, but my problem is not in the regular expressions. My problem is in using the macros set by the process files action.

The process files action automatically sets some macro variables. From the help, these macros are:
PROCFILES_FULLPATH
PROCFILES_FILENAME
PROCFILES_ROOT_DIR
PROCFILES_FILE_DIR
PROCFILES_COUNT

The replace in files action has an input file option and an output file option. I have entered %PROCFILES_FILENAME% in these two fields.

My search/replace text has no square brackets. The problem I am seeing is that the %PROCFILES_FILENAME% value is being parsed rather than treated as a literal value.

Here is the log from my test...
Building project step 'Build database'...
Building project step 'Change to views folder'...
Step default property 'Path' = 'c:\dev\database\views'
Building project step 'Process views'...
Loading a list of matching files...
Processing file 1 of 1524: [dbo].[GetBankAccount].sql...
Building project step 'Make ALTER into CREATE'...
Step default property 'FileIn' = '..sql'
Failed to open '..sql': The system cannot find the file specified.
Step 'Make ALTER into CREATE' failed
Build ended.

Note that the process files step seems to be working fine. It's the FileIn property, which I have set to %PROCFILES_FILENAME%, that is getting corrupted.

I would do a "replace" function on the macro to double up the square brackets, but I don't know what kind of action(s) I could use to do that.

[kevina]

The easiest way to do this replace is with script, I've attached a sample with a project level script function [DoubleSystemChars()] that does this which is demonstrated in a example Replace In File step.

[hurcane]

kevina,

This still didn't help.

Your sample project has literal string values for the input file and output file. That's where I'm using the macro. I'm not using the macro value in the regular expression text. I don't think that I'm clearly expressing where my problem is.

I have a set of files that contain SQL code. I want to replace "ALTER VIEW" with "CREATE VIEW" inside of these files. To do this my, find expression is:

^ALTER\s+VIEW

My replace expression is:

CREATE VIEW

This works fine if my file names don't contain the square brackets. But I don't have the option of changing my file names.

I have attached a sample project along with one of the text files that I am trying to process. It works perfectly if you rename the file and remove the brackets. It's broken if you don't.

[kevina]

Your particular situation is a bit difficult to deal with (and I apologize for not reading your previous posts more clearly).

Because the macro you are using (PROCFILES_FULLPATH) uses other macros, and contains the [ and ] symbols, you need to first expand the macro using Application.ExpandMacros() [in script so it doesn't interpret the [ ] symbols as framing additional script]. The xml below defines a modified version of the Replace in File step you provided, with a vbld_StepStarting script step that does this conversion (by placing the value into a temp macro for use by the step).

Note: simply copy the xml fragment below and paste into Visual Build Pro to review/use the step changes.

<step action='Replace in File' type='0'>
<FileIn>%_PROCFILES_FULLPATH%</FileIn>
<FileOut>%_PROCFILES_FULLPATH%</FileOut>
<Find>^ALTER\s+VIEW</Find>
<Replace>CREATE VIEW</Replace>
<description>Changes ALTER VIEW statements to CREATE VIEW statements</description>
<indent type='3'>2</indent>
<name>Make ALTER into CREATE</name>
<script><![CDATA[
Function vbld_StepStarting()
Application.Macros(vbldMacroTemporary).Add "_PROCFILES_FULLPATH", EscapeString(Application.ExpandMacros("%PROCFILES_FULLPATH%"))
End Function
]]></script>
</step>

[hurcane]

Thank you! This is working now.