0

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.

2
  • Unfortunately, questions which ask for product, service, or learning material recommendations are off-topic because they tend to become obsolete quickly and attract subjective answers. For advice on how to ask a question which may require recommending software, see this Meta Super User post.
    – bwDraco
    Apr 22, 2015 at 23:22
  • 1
    The question asks for a tool or process. Answers may well include code or data management techniques, not just products. As it happens, the Microsoft Power BI stack has a suitable approach. Power Query will not go away any time soon.
    – teylyn
    Apr 22, 2015 at 23:26

1 Answer 1

2

Your question reads like a product description of Power Query. This is a free add-in from Microsoft that works with Excel 2010 and 2013.

Get it here: https://www.microsoft.com/en-us/download/details.aspx?id=39379

You can open a CSV file, rename columns, replace values, define data formats etc. Every activity is recorded and can be edited. The query can be saved and repeated at any time. The output can be placed into an Excel sheet and that can be saved as a CSV.

Edit: Power Query is now available for all editions of Office 2013. Quoting from the system requirements on the download page

The following Office versions are supported:

  • Microsoft Office 2010 Professional Plus with Software Assurance
  • Microsoft Office 2013:
  • Power Query Premium: All Power Query features available for: Professional Plus, Office 365 ProPlus or Excel 2013 Standalone
  • Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power Query features, except the following ones: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects, Salesforce.

Microsoft Power Query for Excel requires Internet Explorer 9 or greater.

5
  • Note: PQ is supported only for the following Office versions: Office 2010 Professional Plus with Software Assurance, Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone.
    – Karan
    Apr 23, 2015 at 1:33
  • Checked it out and I think it probably can do everything I want. Now it's just a matter of taking the time to learn it. Thanks for the help! I don't recall stumbling upon this using all the different keywords I tried.
    – user949286
    Apr 23, 2015 at 17:49
  • @Karan, Power Query is now available for all editions of 2013. Details on the Power Query Download Page
    – teylyn
    Jun 4, 2015 at 4:18
  • Looks like they split it up into Power Query Premium and Public. Thanks for the update @teylyn.
    – Karan
    Jun 4, 2015 at 5:07
  • @Karan, yes, looks like it. The "Premium" features seem to include stuff that is typically used by enterprises, like Active Directory. But even the home user can now get all the goodness of Power Query for queries from SQL, web, Excel tables, etc. That's a real treat. Try doing an Unpivot, then you'll know how good Power Query is.
    – teylyn
    Jun 4, 2015 at 6:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .