Excel Power Query, manual cleanup or a tool: what to use for messy import files

There are three common ways to get a messy export or PDF into the shape an import screen wants. Each is the right answer in some situations.

Manual cleanup in Excel

Good for: a one-off file, small files, unusual layouts.

You delete extra rows and columns, fix dates with formulas, and save as CSV. Free and flexible, but slow, and the same mistakes repeat every month (swapped dates, missed total rows, amounts with brackets).

Excel Power Query

Good for: the same file from the same source every month, if you (or someone in the team) knows Power Query.

You build the clean-up steps once and refresh them on next month's file. Very powerful and free with Excel. The trade-offs: it takes skill to build and maintain, it breaks when the bank changes its layout, PDF support is basic (no scanned pages, no label/value extraction), and it doesn't know the import rules of QuickBooks, Xero or HubSpot, so the file can still be rejected.

A purpose-built tool such as Rowvio

Good for: bookkeepers and teams who import many different files into specific apps.

Rowvio knows the import format of each destination (QuickBooks, Xero, HubSpot, Salesforce, Shopify, Mailchimp), fixes dates, amounts, names, emails and phones, checks the file against the import rules before you download, reads PDFs including scanned and password-protected ones, and saves the recipe so next month's file takes one click. It runs in the browser, so files are not uploaded. The trade-off: it is a separate tool, and for very unusual layouts you may still need to adjust columns.

A simple rule

• One file, once: manual.
• Same file every month and you love Excel: Power Query.
• Many files, many clients, PDFs or strict import rules: a tool built for imports.