Actively Monitor a shared directory for a new CSV file, rename a portion of the filename then open the spreadsheet and find and replace a name

MilesIT

New Member
Joined
Jun 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi I have a complex issue that should be very simple..

There has been a business name change, however the original name has been hardcoded into some software that they are going to charge mega money to fix so I need a solution that some genius might be able to help with :)
Every day at 6.05am the software writes a csv to a directory -
C:\Program Files (x86)\tech Technologies\Schedule Engine\Reports\20210129 (A new folder is created everyday and named with the days date the CS's are then placed in the directory)


OLDBUSINESS_NAME_20210123_004_Transactions_00.csv (There can be multiple versions of this 01,02,03 etc)
OLDBUSINESS_NAME_20210129_010_Accounts_05.csv (There can be multiple versions of this 01,02,03 etc)
OLDBUSINESS_NAME_20210129_010_Customers_04.csv (There can be multiple versions of this 01,02,03 etc)

1. I need to rename all these csv files within the Reports directory within each folder from example: OLDBUSINESS_NAME_20210123_004_Transactions_00.csv to NEWBUSINESS_NAME_20210123_004_Transactions_00.csv

2. I need to open each CSV within the reports directory (Has multiple folders date named on every day its written) and do a find and replace looking for "OLDBUSINESSNAME" and replacing with "NEWBUSINESSNAME"

I also need this to run everyday at 6.10am...

Help I'm going grey :)

Thanks in advance, I'm sure this will be a challenge for someone to assist with.

Miles
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

MilesIT

New Member
Joined
Jun 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
This powershell command will do the text replacement within the csv files.

powershell -Command "(gc INPUT.csv) -replace 'oldbusiness', 'newbusiness' | Out-File -encoding ASCII OUTPUT.csv"
 

Forum statistics

Threads
1,136,864
Messages
5,678,213
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top