BankStatementDesk

Keep leading zeros and long IDs when converting PDF tables to CSV

Open the CSV in a plain text editor before opening it in a spreadsheet. If the file contains 000731, the extraction preserved the reference. Import that column as text. If the file already contains 731, return to the source PDF or an earlier export. A spreadsheet cannot infer whether the missing prefix was one zero, three zeros, or an entirely different character.

In this guide
  1. Decide which columns contain identifiers
  2. Check the PDF, the preview and the CSV separately
  3. Import codes into Excel as text
  4. Work through a failed supplier match
  5. Handle mixed shapes without inventing values
  6. Hand off a CSV that another person can use
  7. Trace a code across two systems

Decide which columns contain identifiers

Start by asking what you will do with a value after import. An invoice number identifies one document. You might compare it with a purchase order or use it to retrieve an archived invoice. Adding two invoice numbers has no business meaning. Unit price and quantity serve different purposes. They should support arithmetic once you have checked how the supplier represents decimals and units.

Two values that look the same on paper

Consider a stock report with a part number of 00012 beside a quantity of 00012. The stock system expects the five-character part number exactly. The warehouse counted twelve units. Treat the first column as text and the second as a quantity. Setting every column to a number breaks the part key. Setting every column to text preserves the characters but leaves the quantity requiring a later conversion before totaling stock.

A numeric-looking value deserves a second look if it appears beneath a heading such as customer, account, reference, SKU, postal code, project, or branch. Look at several rows. A code containing a letter often reveals the role of its all-digit neighbors. Establish the expected shape with someone who uses the records. Color, alignment and font style in a PDF tell you little about the meaning of a field.

Check the PDF, the preview and the CSV separately

The original digital PDF is your first reference. Select one short identifier with leading zeros, one unusually long identifier and one mixed reference. Copy each into a plain text editor. If copying produces separated characters or a different reading order, record what you can verify visually. Use a sample that includes an identifier from the last page because repeated page headers can change the positions the extractor sees.

Open the PDF table to CSV converter and compare these references in the editable grid. Check for spaces that belong to adjacent columns, a trailing amount attached to a reference, or a missing first digit. Correct verified extraction errors here. Do not normalize punctuation until you know whether the destination uses it for matching. A supplier's order code AB-004 and your purchase order AB004 may describe different things.

After download, view a copy in a text editor. Locate a reference by its neighboring description rather than searching for the number you hope to find. This helps you notice an incorrect number without mistaking a matching value from another row for the correct one. Record whether the characters first changed in the source text, the extraction preview, the CSV, or the destination workbook. Each failure belongs to a different stage.

Import codes into Excel as text

Open an empty workbook and use the data import command for a text or CSV file. In versions with Power Query, choose the transformation option so you can inspect the column types before loading the worksheet. Labels differ by version. If the import has already converted a code in an automatically added type-change step, remove or revise that step before applying Text to the identifier. Converting an already shortened numeric value into text merely preserves the shortened value.

Verify the underlying cell

After loading the table, select an identifier and inspect the formula bar as well as the cell. Narrow columns can display scientific notation or cut off a visible sequence. Widen the column, then compare the characters against the plain text export. A custom display format can make a number appear padded while its underlying value remains numeric. That matters when you join records or send the workbook to someone who exports it again.

Excel has a precision limit for numeric entries. An account reference with more than fifteen significant digits can lose information when converted to a number. Widening the column can change how the value looks but cannot recover altered digits. Return to the untouched text, import the complete original string as text, and compare the last few characters too. Microsoft provides instructions for importing identifiers and keeping leading zeros.

Work through a failed supplier match

A supplier delivers invoice 00731 for branch 004 and invoice 731 for branch 117. The purchasing workbook holds both invoices. An analyst imports all columns as numbers, then looks up invoice 731 and sees a valid supplier name. The lookup appears successful because it found a record. The first invoice may now point to the second invoice's details. Checking only for missing matches would miss this error.

Rebuild the comparison using the original keys

Keep the damaged workbook as evidence of the mismatch and reopen the original CSV through an explicit import. Treat both invoice number and branch as text. Compare the supplier, branch, invoice and amount together on these two records. If the accounts system keys invoices by supplier as well as invoice number, include the supplier code in your test. A perfect match on a nonunique column is still an ambiguous match.

For a small job, test the two rows manually before running a lookup across the whole table. For a recurring job, count distinct verified keys before and after the conversion. Investigate unexpected collisions. Two equal entries can be legitimate when an invoice has multiple line items, so your expectation must match the table's level of detail. A line-item file and an invoice-summary file have different definitions of a duplicate.

Handle mixed shapes without inventing values

A column may contain 017, 17, and 00017 because it combines records from several systems. Padding every value to five characters seems efficient until it merges three different identifiers. A fixed width is safe only when a documented destination rule requires it and the underlying source confirms the values refer to the same naming scheme. Keep a separate column for a destination-specific version when the original must remain recoverable.

When a code resembles a date

Codes such as 3-4 and MAR1 can be interpreted according to an application's date rules. Check references containing slashes, short hyphenated sequences and the letter E between digits. The exact behavior depends on the application and its settings. Include those examples in your import trial instead of relying on a column of ordinary numeric strings. Retain the raw description alongside the key while investigating any unexpected conversion.

When an empty value looks like a zero

A blank branch field can mean the source omitted the branch. A zero branch field can mean a specific central office. Avoid filling blanks with padded zeros to satisfy an import screen. Ask for the destination's required missing-value convention. If the destination rejects blank identifiers, isolate the affected rows and seek the missing references. Substituting a valid-looking code makes the file pass a superficial check while attaching data to the wrong branch.

Hand off a CSV that another person can use

Send the original export or the agreed cleaned copy together with a short field description. Identify the columns that must be imported as text, and include one fictional example with leading zeros. Explain the destination delimiter if the recipient works with a different regional setting. The delimiter and decimal separator guide explains how a correct code can still appear in the wrong column when the separator is misread.

A practical acceptance test

Have the recipient import a small, representative sample using their actual process. Ask them to return the values they see for the first code, the longest reference and a code that could be read as a date. Compare the returned values with your originals character by character. If possible, run the destination's standard lookup on each. A text editor proves the CSV contains the right bytes, while the destination test proves those bytes become the intended records.

Keep the verified sample with the source layout description and the date of the test. If the supplier adds a branch prefix or moves its code into another column, rerun the checks before accepting a bulk import. Retire sample records containing real account identifiers once the comparison is complete under your own retention arrangements. Fictional values can cover the same edge cases in training materials without exposing a customer's records.

Why quoting the entire CSV is insufficient

Double quotes around an identifier protect the field boundaries in a CSV. A spreadsheet can still interpret the characters inside that field as a number after reading it. Rely on an explicit column type and a checked destination import. If colleagues need a file for editing, a workbook with verified text cells can be easier to distribute. If a supplier portal specifically requires CSV, test the portal's import rules using that format.

Trace a code across two systems

Suppose a product return arrives with reference 00817 and the warehouse has a separate column for item variant. Purchasing tracks the same reference with a supplier prefix. These can both be accurate identifiers within their own systems. Build a mapping table with the exact supplier reference, the internal reference, the variant and a description of how the mapping was verified. Keep the originally extracted reference available in the transactions table. Then any failed join can be traced to a missing mapping, a bad conversion or a genuinely new product.

Check one product that shares its five digits with another supplier's code. If you match solely on five digits, the ordering system may return the wrong supplier even after Excel has preserved every leading zero. Use the combination of fields required by the actual system, and test a known collision before relying on the join. This distinguishes successful text preservation from successful business matching, two checks that are easy to confuse when most rows happen to match.