I'm looking for a tool or process to help automate repetitive workflow tasks that require with an Excel or CSV file to be in a specific format.
For example, we work with vendors that send us a list of customers. The data in the spreadsheet we receive is the same, but it may be shaped slightly differently. For example, we may receive a file with these columns:
[ID][FName][LName][Birthday]
However, lets assume our system needs it to be shaped like so:
[ID][FirstName][LastName][Birthdate]
Additionally, the data may be in a slightly different format than our system exepects. For example, assume the birthdate column needs to be in a specific format (MM/DD/YYYY) where the vendor may provide it as (YYYY-MM-DD).
Assuming the file we recieve from the vendor always is sent in the same format, we would like to transform these files quickly to be compatible with our system.
In my mind, this would ideally be an application with the following features:
- Ability to work with Excel or CSV files
- Define how to rename columns
- Define the data format for each column
- Clean up bad data (sometimes customers have the string #N/A in a field, which should be treated as blank in our system)
- Output as CSV
- Merge rows by matching columns (sometimes we are provided with multiple files that need to be merged together by ID)
- A GUI interface would be nice for defining the rules, then running it using a CLI would probably be helpful for automation/future runs
- The ability to save these settings so we can quickly rerun them in the future by loading new data with the previously defined settings.
Is there a tool or workflow that would accomplish this with very little friction? We can develop something internally if necessary, but if there's already a tool out there that makes this easy, that would be ideal.