Mixing
How can I use SSIS to conditionally rename each file in a directory with a unique name?
I have a directory with 5 files containing datetime information. I need to strip the datetime from each file and rename each file to a friendly name.
I'm having trouble coming up with the structure of this task, but I think I need to use a loop that in a script task to rename the file conditionally, but haven't found many examples on the internet that perform this function.
I also considered a script task that renames all of the files in the directory at once.
Any guidance would be great.
I've tried to use SSIS foreach loop with file tasks that move and rename files, but it's clunky. "Once and only once" rings in my head, but I'm having trouble working out an efficient method of doing this.
There is no code that works as of this post.
I would like to rename each file based on a partial name match so that the destination directory contains clean filenames that exclude unnecessary date information.
Let's say that the files are named as follows:
"EOY Test 01012018.xls"
"Test PLA 01022018.xls"
"Test 01032018 SHA.xls"
"EAD Test 01042018.xls"
"Test DOY 01052018.xls"
I want to rename each file conditionally by finding part of their name:
Examples:
If fileName like "EOY Test" then "EOY_Test.XLS"
If fileName like "Test PLA" then "PLA_Test.XLS"
etc...
UPDATE:
I had to modify the code to also remove periods and a few other characters from the filename. Also added a new variable and directoryinfo line to account for source and destination folders. Here's the modified code from @userfl89 's answer:
string fileDirectory_Source = Dts.Variables["User::PayrollSourceFilePath"].Value.ToString();
string fileDirectory_Dest = Dts.Variables["User::PayrollDestFilePath"].Value.ToString();
DirectoryInfo dirInfo_Source = new DirectoryInfo(fileDirectory_Source);
DirectoryInfo dirInfo_Dest = new DirectoryInfo(fileDirectory_Dest);
///
///int i = 1;
foreach (FileInfo fi in dirInfo_Source.EnumerateFiles())
{
///Replace(" ", "_") + i
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]|[.,/ -]", "").TrimEnd()+ fi.Extension;
fi.MoveTo(dirInfo_Dest + newFileName);
///i++;
}
UPDATE 2:
This is the error I'm getting:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
c# loops ssis
asked Jan 2 at 20:57
theeviininjatheeviininja
876
add a comment |
I have a directory with 5 files containing datetime information. I need to strip the datetime from each file and rename each file to a friendly name.
I'm having trouble coming up with the structure of this task, but I think I need to use a loop that in a script task to rename the file conditionally, but haven't found many examples on the internet that perform this function.
I also considered a script task that renames all of the files in the directory at once.
Any guidance would be great.
I've tried to use SSIS foreach loop with file tasks that move and rename files, but it's clunky. "Once and only once" rings in my head, but I'm having trouble working out an efficient method of doing this.
There is no code that works as of this post.
I would like to rename each file based on a partial name match so that the destination directory contains clean filenames that exclude unnecessary date information.
Let's say that the files are named as follows:
"EOY Test 01012018.xls"
"Test PLA 01022018.xls"
"Test 01032018 SHA.xls"
"EAD Test 01042018.xls"
"Test DOY 01052018.xls"
I want to rename each file conditionally by finding part of their name:
Examples:
If fileName like "EOY Test" then "EOY_Test.XLS"
If fileName like "Test PLA" then "PLA_Test.XLS"
etc...
UPDATE:
I had to modify the code to also remove periods and a few other characters from the filename. Also added a new variable and directoryinfo line to account for source and destination folders. Here's the modified code from @userfl89 's answer:
string fileDirectory_Source = Dts.Variables["User::PayrollSourceFilePath"].Value.ToString();
string fileDirectory_Dest = Dts.Variables["User::PayrollDestFilePath"].Value.ToString();
DirectoryInfo dirInfo_Source = new DirectoryInfo(fileDirectory_Source);
DirectoryInfo dirInfo_Dest = new DirectoryInfo(fileDirectory_Dest);
///
///int i = 1;
foreach (FileInfo fi in dirInfo_Source.EnumerateFiles())
{
///Replace(" ", "_") + i
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]|[.,/ -]", "").TrimEnd()+ fi.Extension;
fi.MoveTo(dirInfo_Dest + newFileName);
///i++;
}
UPDATE 2:
This is the error I'm getting:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
c# loops ssis
asked Jan 2 at 20:57
theeviininjatheeviininja
876
What happens if you have two files which contain EOY or some other Identifier? Would you just want to append a numeric to the end of the file name or use the date time inside the file name?
– Ryan Wilson
Jan 2 at 20:59
Please assume that the name will not change unless the dates change.I want to eliminate the date portion from the name completely and rename the files.
– theeviininja
Jan 3 at 14:57
add a comment |
I have a directory with 5 files containing datetime information. I need to strip the datetime from each file and rename each file to a friendly name.
I'm having trouble coming up with the structure of this task, but I think I need to use a loop that in a script task to rename the file conditionally, but haven't found many examples on the internet that perform this function.
I also considered a script task that renames all of the files in the directory at once.
Any guidance would be great.
I've tried to use SSIS foreach loop with file tasks that move and rename files, but it's clunky. "Once and only once" rings in my head, but I'm having trouble working out an efficient method of doing this.
There is no code that works as of this post.
I would like to rename each file based on a partial name match so that the destination directory contains clean filenames that exclude unnecessary date information.
Let's say that the files are named as follows:
"EOY Test 01012018.xls"
"Test PLA 01022018.xls"
"Test 01032018 SHA.xls"
"EAD Test 01042018.xls"
"Test DOY 01052018.xls"
I want to rename each file conditionally by finding part of their name:
Examples:
If fileName like "EOY Test" then "EOY_Test.XLS"
If fileName like "Test PLA" then "PLA_Test.XLS"
etc...
UPDATE:
I had to modify the code to also remove periods and a few other characters from the filename. Also added a new variable and directoryinfo line to account for source and destination folders. Here's the modified code from @userfl89 's answer:
string fileDirectory_Source = Dts.Variables["User::PayrollSourceFilePath"].Value.ToString();
string fileDirectory_Dest = Dts.Variables["User::PayrollDestFilePath"].Value.ToString();
DirectoryInfo dirInfo_Source = new DirectoryInfo(fileDirectory_Source);
DirectoryInfo dirInfo_Dest = new DirectoryInfo(fileDirectory_Dest);
///
///int i = 1;
foreach (FileInfo fi in dirInfo_Source.EnumerateFiles())
{
///Replace(" ", "_") + i
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]|[.,/ -]", "").TrimEnd()+ fi.Extension;
fi.MoveTo(dirInfo_Dest + newFileName);
///i++;
}
UPDATE 2:
This is the error I'm getting:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
c# loops ssis
asked Jan 2 at 20:57
theeviininjatheeviininja
876
I have a directory with 5 files containing datetime information. I need to strip the datetime from each file and rename each file to a friendly name.
I'm having trouble coming up with the structure of this task, but I think I need to use a loop that in a script task to rename the file conditionally, but haven't found many examples on the internet that perform this function.
I also considered a script task that renames all of the files in the directory at once.
Any guidance would be great.
I've tried to use SSIS foreach loop with file tasks that move and rename files, but it's clunky. "Once and only once" rings in my head, but I'm having trouble working out an efficient method of doing this.
There is no code that works as of this post.
I would like to rename each file based on a partial name match so that the destination directory contains clean filenames that exclude unnecessary date information.
Let's say that the files are named as follows:
"EOY Test 01012018.xls"
"Test PLA 01022018.xls"
"Test 01032018 SHA.xls"
"EAD Test 01042018.xls"
"Test DOY 01052018.xls"
I want to rename each file conditionally by finding part of their name:
Examples:
If fileName like "EOY Test" then "EOY_Test.XLS"
If fileName like "Test PLA" then "PLA_Test.XLS"
etc...
UPDATE:
I had to modify the code to also remove periods and a few other characters from the filename. Also added a new variable and directoryinfo line to account for source and destination folders. Here's the modified code from @userfl89 's answer:
string fileDirectory_Source = Dts.Variables["User::PayrollSourceFilePath"].Value.ToString();
string fileDirectory_Dest = Dts.Variables["User::PayrollDestFilePath"].Value.ToString();
DirectoryInfo dirInfo_Source = new DirectoryInfo(fileDirectory_Source);
DirectoryInfo dirInfo_Dest = new DirectoryInfo(fileDirectory_Dest);
///
///int i = 1;
foreach (FileInfo fi in dirInfo_Source.EnumerateFiles())
{
///Replace(" ", "_") + i
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]|[.,/ -]", "").TrimEnd()+ fi.Extension;
fi.MoveTo(dirInfo_Dest + newFileName);
///i++;
}
UPDATE 2:
This is the error I'm getting:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
c# loops ssis
c# loops ssis
asked Jan 2 at 20:57
theeviininjatheeviininja
876
asked Jan 2 at 20:57
theeviininjatheeviininja
876
edited Jan 10 at 20:40
theeviininja
asked Jan 2 at 20:57
theeviininjatheeviininja
876
asked Jan 2 at 20:57
theeviininjatheeviininja
876
asked Jan 2 at 20:57
theeviininjatheeviininja
876
876
What happens if you have two files which contain EOY or some other Identifier? Would you just want to append a numeric to the end of the file name or use the date time inside the file name?
– Ryan Wilson
Jan 2 at 20:59
Please assume that the name will not change unless the dates change.I want to eliminate the date portion from the name completely and rename the files.
– theeviininja
Jan 3 at 14:57
add a comment |
What happens if you have two files which contain EOY or some other Identifier? Would you just want to append a numeric to the end of the file name or use the date time inside the file name?
– Ryan Wilson
Jan 2 at 20:59
Please assume that the name will not change unless the dates change.I want to eliminate the date portion from the name completely and rename the files.
– theeviininja
Jan 3 at 14:57
What happens if you have two files which contain EOY or some other Identifier? Would you just want to append a numeric to the end of the file name or use the date time inside the file name?
– Ryan Wilson
Jan 2 at 20:59
What happens if you have two files which contain EOY or some other Identifier? Would you just want to append a numeric to the end of the file name or use the date time inside the file name?
– Ryan Wilson
Jan 2 at 20:59
Please assume that the name will not change unless the dates change.I want to eliminate the date portion from the name completely and rename the files.
– theeviininja
Jan 3 at 14:57
Please assume that the name will not change unless the dates change.I want to eliminate the date portion from the name completely and rename the files.
– theeviininja
Jan 3 at 14:57
add a comment |
1 Answer
1
active
oldest
votes
The following is a C# Script Task that will remove the date (numeric) part of the file names as you listed and does not need to be executed within any type of loop. As a "safeguard" a surrogate number is appended to each file in order to make the files names unique in case they weren't already once the numbers are removed. If you're sure that the file names will always be unique without the dates you can just remove the i int variable that's added to the file name. The Path.GetFileNameWithoutExtension method gets only the file name to avoid creating the file with the extension in the name and making the file invalid. The first Replace method is used to remove the numbers, with the TrimEnd method omitting the trailing space between the text and where the dates were before this. An incrementing number that starts at 1 is then added to the file name to ensure uniqueness. The outer Replace method is used to change the space between the words in the files names to underscores as in your example, i.e. Test PLA changes to PLA_Test.XLS. After changing the file name to the desired output, the MoveTo method is used move, basically overwrite, the files with their new names. In case the folder path is stored in an SSIS variable I modified this to obtain the folder from such, however this be replaced if necessary. If a variable is used to store the folder make sure to add this variable in the ReadOnlyVariables field on the Script Task Editor. This will require references to the System.IO and System.Text.RegularExpressions namespaces for the file and Regex operations, respectively.
string fileDirectory = Dts.Variables["User::FolderPath"].Value.ToString();
DirectoryInfo dirInfo = new DirectoryInfo(fileDirectory);
int i = 1;
foreach (FileInfo fi in dirInfo.EnumerateFiles())
{
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]", "").TrimEnd().Replace(" ", "_") + i + fi.Extension;
fi.MoveTo(fileDirectory + newFileName);
i++;
}
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
Sorry for the delay! I'm going to try to apply this and will get back to you.
– theeviininja
Jan 9 at 21:00
I tried the solution, but have been having trouble getting it to work. I modified it a bit, but getting exceptions when I execute. I added an update with some more info above.
– theeviininja
Jan 10 at 20:04
What error are you getting? If multiple files have the same name after their name has been modified the names will need to be made unique by either uncommenting the "i" int variable and adding it to the file name after the second Replace method place or making these names unique via another way.
– userfl89
Jan 10 at 20:22
It's a really unfriendly error and there isn't enough room to post it in a comment. I'll add it to the main post.
– theeviininja
Jan 10 at 20:37
I updated the main post with the error.
– theeviininja
Jan 10 at 20:40
|
show 6 more comments
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013084%2fhow-can-i-use-ssis-to-conditionally-rename-each-file-in-a-directory-with-a-uniqu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The following is a C# Script Task that will remove the date (numeric) part of the file names as you listed and does not need to be executed within any type of loop. As a "safeguard" a surrogate number is appended to each file in order to make the files names unique in case they weren't already once the numbers are removed. If you're sure that the file names will always be unique without the dates you can just remove the i int variable that's added to the file name. The Path.GetFileNameWithoutExtension method gets only the file name to avoid creating the file with the extension in the name and making the file invalid. The first Replace method is used to remove the numbers, with the TrimEnd method omitting the trailing space between the text and where the dates were before this. An incrementing number that starts at 1 is then added to the file name to ensure uniqueness. The outer Replace method is used to change the space between the words in the files names to underscores as in your example, i.e. Test PLA changes to PLA_Test.XLS. After changing the file name to the desired output, the MoveTo method is used move, basically overwrite, the files with their new names. In case the folder path is stored in an SSIS variable I modified this to obtain the folder from such, however this be replaced if necessary. If a variable is used to store the folder make sure to add this variable in the ReadOnlyVariables field on the Script Task Editor. This will require references to the System.IO and System.Text.RegularExpressions namespaces for the file and Regex operations, respectively.
string fileDirectory = Dts.Variables["User::FolderPath"].Value.ToString();
DirectoryInfo dirInfo = new DirectoryInfo(fileDirectory);
int i = 1;
foreach (FileInfo fi in dirInfo.EnumerateFiles())
{
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]", "").TrimEnd().Replace(" ", "_") + i + fi.Extension;
fi.MoveTo(fileDirectory + newFileName);
i++;
}
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
Sorry for the delay! I'm going to try to apply this and will get back to you.
– theeviininja
Jan 9 at 21:00
I tried the solution, but have been having trouble getting it to work. I modified it a bit, but getting exceptions when I execute. I added an update with some more info above.
– theeviininja
Jan 10 at 20:04
What error are you getting? If multiple files have the same name after their name has been modified the names will need to be made unique by either uncommenting the "i" int variable and adding it to the file name after the second Replace method place or making these names unique via another way.
– userfl89
Jan 10 at 20:22
It's a really unfriendly error and there isn't enough room to post it in a comment. I'll add it to the main post.
– theeviininja
Jan 10 at 20:37
I updated the main post with the error.
– theeviininja
Jan 10 at 20:40
|
show 6 more comments
The following is a C# Script Task that will remove the date (numeric) part of the file names as you listed and does not need to be executed within any type of loop. As a "safeguard" a surrogate number is appended to each file in order to make the files names unique in case they weren't already once the numbers are removed. If you're sure that the file names will always be unique without the dates you can just remove the i int variable that's added to the file name. The Path.GetFileNameWithoutExtension method gets only the file name to avoid creating the file with the extension in the name and making the file invalid. The first Replace method is used to remove the numbers, with the TrimEnd method omitting the trailing space between the text and where the dates were before this. An incrementing number that starts at 1 is then added to the file name to ensure uniqueness. The outer Replace method is used to change the space between the words in the files names to underscores as in your example, i.e. Test PLA changes to PLA_Test.XLS. After changing the file name to the desired output, the MoveTo method is used move, basically overwrite, the files with their new names. In case the folder path is stored in an SSIS variable I modified this to obtain the folder from such, however this be replaced if necessary. If a variable is used to store the folder make sure to add this variable in the ReadOnlyVariables field on the Script Task Editor. This will require references to the System.IO and System.Text.RegularExpressions namespaces for the file and Regex operations, respectively.
string fileDirectory = Dts.Variables["User::FolderPath"].Value.ToString();
DirectoryInfo dirInfo = new DirectoryInfo(fileDirectory);
int i = 1;
foreach (FileInfo fi in dirInfo.EnumerateFiles())
{
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]", "").TrimEnd().Replace(" ", "_") + i + fi.Extension;
fi.MoveTo(fileDirectory + newFileName);
i++;
}
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
Sorry for the delay! I'm going to try to apply this and will get back to you.
– theeviininja
Jan 9 at 21:00
I tried the solution, but have been having trouble getting it to work. I modified it a bit, but getting exceptions when I execute. I added an update with some more info above.
– theeviininja
Jan 10 at 20:04
What error are you getting? If multiple files have the same name after their name has been modified the names will need to be made unique by either uncommenting the "i" int variable and adding it to the file name after the second Replace method place or making these names unique via another way.
– userfl89
Jan 10 at 20:22
It's a really unfriendly error and there isn't enough room to post it in a comment. I'll add it to the main post.
– theeviininja
Jan 10 at 20:37
I updated the main post with the error.
– theeviininja
Jan 10 at 20:40
|
show 6 more comments
The following is a C# Script Task that will remove the date (numeric) part of the file names as you listed and does not need to be executed within any type of loop. As a "safeguard" a surrogate number is appended to each file in order to make the files names unique in case they weren't already once the numbers are removed. If you're sure that the file names will always be unique without the dates you can just remove the i int variable that's added to the file name. The Path.GetFileNameWithoutExtension method gets only the file name to avoid creating the file with the extension in the name and making the file invalid. The first Replace method is used to remove the numbers, with the TrimEnd method omitting the trailing space between the text and where the dates were before this. An incrementing number that starts at 1 is then added to the file name to ensure uniqueness. The outer Replace method is used to change the space between the words in the files names to underscores as in your example, i.e. Test PLA changes to PLA_Test.XLS. After changing the file name to the desired output, the MoveTo method is used move, basically overwrite, the files with their new names. In case the folder path is stored in an SSIS variable I modified this to obtain the folder from such, however this be replaced if necessary. If a variable is used to store the folder make sure to add this variable in the ReadOnlyVariables field on the Script Task Editor. This will require references to the System.IO and System.Text.RegularExpressions namespaces for the file and Regex operations, respectively.
string fileDirectory = Dts.Variables["User::FolderPath"].Value.ToString();
DirectoryInfo dirInfo = new DirectoryInfo(fileDirectory);
int i = 1;
foreach (FileInfo fi in dirInfo.EnumerateFiles())
{
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]", "").TrimEnd().Replace(" ", "_") + i + fi.Extension;
fi.MoveTo(fileDirectory + newFileName);
i++;
}
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
The following is a C# Script Task that will remove the date (numeric) part of the file names as you listed and does not need to be executed within any type of loop. As a "safeguard" a surrogate number is appended to each file in order to make the files names unique in case they weren't already once the numbers are removed. If you're sure that the file names will always be unique without the dates you can just remove the i int variable that's added to the file name. The Path.GetFileNameWithoutExtension method gets only the file name to avoid creating the file with the extension in the name and making the file invalid. The first Replace method is used to remove the numbers, with the TrimEnd method omitting the trailing space between the text and where the dates were before this. An incrementing number that starts at 1 is then added to the file name to ensure uniqueness. The outer Replace method is used to change the space between the words in the files names to underscores as in your example, i.e. Test PLA changes to PLA_Test.XLS. After changing the file name to the desired output, the MoveTo method is used move, basically overwrite, the files with their new names. In case the folder path is stored in an SSIS variable I modified this to obtain the folder from such, however this be replaced if necessary. If a variable is used to store the folder make sure to add this variable in the ReadOnlyVariables field on the Script Task Editor. This will require references to the System.IO and System.Text.RegularExpressions namespaces for the file and Regex operations, respectively.
string fileDirectory = Dts.Variables["User::FolderPath"].Value.ToString();
DirectoryInfo dirInfo = new DirectoryInfo(fileDirectory);
int i = 1;
foreach (FileInfo fi in dirInfo.EnumerateFiles())
{
string newFileName = Regex.Replace(Path.GetFileNameWithoutExtension(fi.Name), "[0-9]", "").TrimEnd().Replace(" ", "_") + i + fi.Extension;
fi.MoveTo(fileDirectory + newFileName);
i++;
}
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
answered Jan 3 at 15:49
userfl89userfl89
3,2841515
3,2841515
Sorry for the delay! I'm going to try to apply this and will get back to you.
– theeviininja
Jan 9 at 21:00
I tried the solution, but have been having trouble getting it to work. I modified it a bit, but getting exceptions when I execute. I added an update with some more info above.
– theeviininja
Jan 10 at 20:04
What error are you getting? If multiple files have the same name after their name has been modified the names will need to be made unique by either uncommenting the "i" int variable and adding it to the file name after the second Replace method place or making these names unique via another way.
– userfl89
Jan 10 at 20:22
It's a really unfriendly error and there isn't enough room to post it in a comment. I'll add it to the main post.
– theeviininja
Jan 10 at 20:37
I updated the main post with the error.
– theeviininja
Jan 10 at 20:40
|
show 6 more comments
Sorry for the delay! I'm going to try to apply this and will get back to you.
– theeviininja
Jan 9 at 21:00
I tried the solution, but have been having trouble getting it to work. I modified it a bit, but getting exceptions when I execute. I added an update with some more info above.
– theeviininja
Jan 10 at 20:04
What error are you getting? If multiple files have the same name after their name has been modified the names will need to be made unique by either uncommenting the "i" int variable and adding it to the file name after the second Replace method place or making these names unique via another way.
– userfl89
Jan 10 at 20:22
It's a really unfriendly error and there isn't enough room to post it in a comment. I'll add it to the main post.
– theeviininja
Jan 10 at 20:37
I updated the main post with the error.
– theeviininja
Jan 10 at 20:40
Sorry for the delay! I'm going to try to apply this and will get back to you.
– theeviininja
Jan 9 at 21:00
Sorry for the delay! I'm going to try to apply this and will get back to you.
– theeviininja
Jan 9 at 21:00
I tried the solution, but have been having trouble getting it to work. I modified it a bit, but getting exceptions when I execute. I added an update with some more info above.
– theeviininja
Jan 10 at 20:04
I tried the solution, but have been having trouble getting it to work. I modified it a bit, but getting exceptions when I execute. I added an update with some more info above.
– theeviininja
Jan 10 at 20:04
What error are you getting? If multiple files have the same name after their name has been modified the names will need to be made unique by either uncommenting the "i" int variable and adding it to the file name after the second Replace method place or making these names unique via another way.
– userfl89
Jan 10 at 20:22
What error are you getting? If multiple files have the same name after their name has been modified the names will need to be made unique by either uncommenting the "i" int variable and adding it to the file name after the second Replace method place or making these names unique via another way.
– userfl89
Jan 10 at 20:22
It's a really unfriendly error and there isn't enough room to post it in a comment. I'll add it to the main post.
– theeviininja
Jan 10 at 20:37
It's a really unfriendly error and there isn't enough room to post it in a comment. I'll add it to the main post.
– theeviininja
Jan 10 at 20:37
I updated the main post with the error.
– theeviininja
Jan 10 at 20:40
I updated the main post with the error.
– theeviininja
Jan 10 at 20:40
|
show 6 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013084%2fhow-can-i-use-ssis-to-conditionally-rename-each-file-in-a-directory-with-a-uniqu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What happens if you have two files which contain EOY or some other Identifier? Would you just want to append a numeric to the end of the file name or use the date time inside the file name?
– Ryan Wilson
Jan 2 at 20:59
Please assume that the name will not change unless the dates change.I want to eliminate the date portion from the name completely and rename the files.
– theeviininja
Jan 3 at 14:57