Skip down to the research record

CSV deal export: 5 checks before loading a table

Quota Forecast Editorial Team · Published · 11 min read

Before loading a CRM CSV export into a table, check field counts, footer lines, filters, special number values, and close dates and stages.

Key takeaways

  • Read the raw file for extra fields and footer lines.
  • Write stage and date rules before the load.
  • Trace unusual amount values through the load.
  • Compare the loaded deals and totals with CRM records using the same filter.

You download a CRM export, load it into a table, and start counting deals by stage. Then a total looks off, a deal appears twice, or a close date lands in the wrong month. At that point nobody is sure whether the problem is in the CRM or in the load.

Revenue leaders care about this because the questions they ask are about movement. Which deals moved their close date? Which moved to a later stage, and which slipped out of the quarter? A small error in the load can make a deal look like it slipped when it did not, or hide a slip that really happened.

Metabase says CSV files still make up 40% of the world's data (Metabase, SumIf). That is a reason to get good at checking CSV files, not a reason to trust them without looking.

This post is for revenue leaders who load a CRM CSV export into a table to track close-date and stage changes. You may not run the load yourself, and you do not need to write code to use most of these checks.

Close date and stage are the two fields this post focuses on, because they show when a deal was expected to close and where it sits in the sales process. A load that gets either one wrong distorts the story a revenue leader tells about the quarter, even when the total number of deals looks right.

The five checks below run in order, from the raw file to the table. Each one names a specific place where a load can go wrong. Where a check depends on a tool, I link its documentation. If you load the file another way, the idea still applies, but the exact options may be different.

1. Look for rows with more fields than the header

Look for rows that have more fields than the header row. A row like that can be a sign that a value contains a comma, and the parser reads that comma as the start of a new field. The rest of the row can then shift into the wrong columns, so a deal amount may end up under a stage name.

Here is a small example. Suppose the header row has five columns: deal ID, account, stage, close date, and amount. One row has an account name with a comma in it, and the value is not wrapped in quotes. That row now has six fields where the header has five. Depending on how the load is set up, it could stop, it could skip the row, or it could shift the later values one column over. You want to know which of those happened before any chart is built.

A quick way to start is to open the file in a plain text editor and read the header and a few rows next to each other. Lines that look longer than the header are the ones to flag. A plain comma count can mislead you when values contain commas, so read the rows instead of trusting the count alone.

In pandas read_csv, a bad line is "a line with too many fields"; its bad-line setting controls what happens next (pandas, read_csv). The value 'error' raises an exception when a bad line is encountered. If a bad-line function returns None, that line is ignored.

For a deal file, I would rather stop the load than drop a line quietly. An ignored line is a deal that disappears from the table, and nothing in your totals says it is missing. Use the error behaviour on the first load, read the message, and fix the bad lines in the export or the source record. Then load again and compare the row count with a CRM report for the same filter.

2. Check the bottom of the file for lines that are not deals

Check the last lines of the file for totals, notes, or blank lines. A total or note at the bottom is not a deal, and you want it out of the table. Look at the file itself, because the last lines are easy to skip when you scan the top.

A total row, if your export has one, may put a sum where an amount belongs. A note may have no stage or date at all. Either way, a row that does not describe a deal can show up in a count of deals by stage, and it can make that count wrong.

Blank lines need the same attention. A blank line in the middle of the file is not a deal, and a blank line at the end can be read as an empty row. Scan both places before you load, and count the blanks you find so the number is in your notes.

To skip a footer, pandas read_csv accepts a "Number of lines at bottom of file to skip", but the option is "Unsupported with engine='c'" (pandas, read_csv). Check which parsing engine your code uses before you rely on that option, and confirm the result by reading the output.

Keep the footer's total, if there is one, in your notes. It is the number you compare against after the load.

Removing the footer by hand is fine for a single file you are checking. If you load files like this on a schedule, write the rule down, so the load does the same thing each time. After you skip or remove the footer, compare the row count with what you expected, and check that only footer lines are missing.

3. Filter rows with a test you can read back

Filter rows with a test you can read back, not by deleting lines by hand. A report may need only some of the rows, such as one pipeline, one stage, or the deals whose close date falls in a given range. Hand edits are hard to repeat, and nobody can see later which lines were removed and why.

A pandas read_csv example uses the callable argument "lambda x: x in [0, 2]" (pandas, read_csv). That example is only a pattern for the shape of a test. Check whether your test returns true for the rows you want to keep or for the rows you want to drop, since the two are easy to mix up. Your own test should name your own stages and dates. Write it in plain words first, such as picking out the deals in two stages, and then turn those words into code.

Keep the test next to the report. When someone asks why a deal is missing, the test should answer the question without another round of checking. Then check that the code does what the words say, using a few rows you know should be kept and a few you know should be dropped. For related reporting checks, see pipeline report filters.

Stage names are a place where a filter can quietly miss rows. A name that differs by one space, one capital letter, or a plural 's' will not match a rule written for the exact name you expected. After the load, compare the distinct stage values with the names in your written rule and record any change to that rule.

The same goes for close dates. Write the date-range rule before the load, then check how the dates read in the table against a few rows where you know the real close date. If a date is read as text, a range test may return rows you did not intend, so look at a few values by eye first.

A rule written after the numbers look wrong can be bent to fit them.

4. Look for special values in the number columns

Look at the number columns for values that are not plain numbers before you total anything. Blanks and text where you expect a number are the first cases to look for.

PostgreSQL's special numeric values are adapted from the IEEE 754 standard and represent "infinity", "negative infinity", and "not-a-number" (PostgreSQL, numeric types). If one of those values reaches a deal amount, a total over that column can be misleading, so find it before you add the column up.

For numeric constant syntax, see Section 4.1.2. If your load writes numbers into SQL by hand, read that section before you write the statement, so you can check how each value should be written.

The type names int2, int4, and int8 are PostgreSQL extensions also used by some other SQL database systems. Check each type name against the database you load into, and do not copy names from another system without checking them first.

For PostgreSQL's extra_float_digits setting, any value greater than 0 selects the shortest-precise format. When a float amount in the table differs slightly from the CRM figure, check the output format before you decide that the load is wrong.

Before you add up an amount column, check how the values are written. A value with a currency symbol, a thousands separator, or a space may not be read as a number in every tool. Pick one example from each kind you find and trace it through the load.

Compare the column types the table ends up with against what you expect. A close date stored as a date, an amount stored as a number, and a stage stored as text will behave differently when you sort, filter, and sum. If one of them is stored as something else, fix it before you report from the table.

Write down any unusual values you find, with the row they came from. A value you cannot explain is worth a question to whoever owns the CRM record. Do not fix it silently in the table, because a silent fix makes the table differ from the file in a way nobody can trace.

5. Confirm close dates and stages after the load

Confirm close dates and stages after the load by checking a handful of deals that changed in the period against the CRM record itself. Open each one in the CRM and check that its date and stage match the table.

Here is what that looks like in practice. Suppose a deal was in the Proposal stage with a close date in March, then moved to Negotiation with a close date in June. After the load, the table should show the deal in Negotiation with the June date. If it shows the old March date, or it shows the deal in two places, the load needs a look before anyone reads a trend from it. This assumes the export holds the current values; confirm those values against the CRM record.

A deal that moved more than once is a useful test, because it shows whether the table gives you the sequence of changes or only the latest value. Write down which one you found, since that tells you what the load can and cannot show you. For the reasons behind a date change, see Close date change log: why the date moved.

Then compare totals. Count the rows, count the deals in each stage, and add up the amount for each stage. Compare each number with a CRM report for the same filter. If a number differs, go back to checks 1 to 4 before you trust any trend.

Illustrative example: compare row counts

Suppose the CRM report contains 10 deal rows and the loaded table contains 0, using the same filter. The difference is 10 rows. Trace those missing rows through the load before comparing stage changes; matching totals alone would still not confirm that individual dates and stages are correct.

Write down what you checked and what you found. Keep the exact file you loaded, the test you used to keep rows, and the report you compared against. If someone later asks why a deal moved, you can show the file and the checks, not just the chart.

Before you share the table, check that:

  • the row count matches the number you expected from the file;
  • no bad lines were ignored without a record of them;
  • footer lines are gone and the footer total is kept in your notes;
  • each filter is written as a rule you can read back;
  • no special values are hiding in the number columns;
  • a few deals that changed close date or stage match the CRM record.

If the same export loads every week, run the checklist each time and keep the results with the file. The first time may take the longest, and later runs may be quicker, because the rules are already written.

These five checks cannot correct the CRM. If the export is wrong, the table will be wrong in the same way. What the checks do is show where the file and the table disagree, so you can decide which one to believe.

Today, take the exact export used for your latest table and compare a changed deal's close date and stage against its CRM record. Record any mismatch before sharing the report.

Questions before loading a CSV deal export

Should a bad line stop the first load?

Stop the first load rather than silently losing a deal row. Read the error, fix the bad line, and compare the new row count with the matching CRM report.

Does a clean load prove the CRM is correct?

A clean load only shows that the file was read as written. Check changed deals against their CRM records before trusting a trend.

Sources