Weekly CSV import: mixed dates, refund duplicates, repeatable load
A colleague exports order data from a web storefront to Orders.csv each Monday. The file has 5,200 rows and columns OrderID, OrderDate, Region, and Amount. OrderDate values arrive in at least four formats: 2024-03-05, 05/03/2024, March 5, 2024, and 20240305. Amount values include a dollar sign, thousands separators, and occasional trailing spaces, so Excel treats many as text. About 60 OrderID values are duplicated because the export includes refunds; a refund row repeats the original ID with a negative Amount. Region is blank for 38 rows. You must import the file into an existing workbook that already has a formatted report on another sheet, preserve the raw data, produce a clean analysis range, and print a one-page summary. Your colleague wants to overwrite the file next week and repeat the process.
1. What is the most reliable way to convert OrderDate to real dates, given the mix of formats?
Read the worked answer
The formats are ambiguous, so do not run a single Text to Columns pass on the original column. Work on a copy and test a sample of at least 20 rows. For the ISO and eight-digit values, Text to Columns with Date YMD works. For slash dates such as 05/03/2024, you must decide whether the source is MDY or DMY; Text to Columns with Date MDY will interpret it as May 3. The month-name values usually convert with DATEVALUE. A robust repeatable option is Data > From Text/CSV (Power Query): import, set the column type to Date using the correct locale, and load the cleaned table to a new sheet. That handles mixed formats and preserves the raw file. If Power Query is not allowed, add a helper column with DATE/RIGHT/LEFT parsing and keep the original text column for audit. Format the result as a real date and verify counts by month.
2. Should you use Remove Duplicates on OrderID? Why or why not?
Read the worked answer
No. Remove Duplicates on OrderID alone would treat a refund row and its original sale as duplicates and keep only the first one it encounters, silently deleting a legitimate negative transaction or the original sale. Because refunds intentionally reuse the ID, the business key is a combination of OrderID, Amount sign, and date, or a separate transaction type if one exists. First flag duplicates with COUNTIFS on the composite key, or use conditional formatting, and review the 60 pairs with the colleague. If the rule is 'keep the original sale and one refund,' deduplicate on a helper key and keep a copy of the removed rows. Remove Duplicates is destructive and cannot be undone after saving, so run it on a copy and reconcile row counts before and after. Document the rule so next Monday's import uses the same logic.
3. How do you bring the cleaned data into the existing workbook without disturbing formatting and keep the process repeatable?
Read the worked answer
Keep the raw import on its own sheet, load the cleaned data as an Excel table on a second sheet, and leave the formatted report untouched. If you must copy, use Paste Special > Values to avoid carrying source formatting and formulas; use Transpose only if the source orientation differs. For a repeatable weekly process, use Data > From Text/CSV or Power Query and load to a table; next week replace the file and Refresh, and the table expands. Build the one-page summary with formulas or a PivotTable that references the table by name, not a fixed range. Set the print area to the summary range and use Fit to 1 page, then export to PDF. Keep the original CSV untouched as an audit trail.