BankStatementDesk

Fix CSV delimiter and decimal separator problems in Excel

A CSV that opens in one column usually needs a different field separator in the import preview. Amounts that occupy the correct column but have the wrong value need a separate check of the decimal and grouping characters. Solve the column boundary first. Then choose the appropriate regional interpretation for each numeric or date column. These are different decisions even when the same comma appears in both.

In this guide
  1. Read a single record before changing settings
  2. Use the shape of the table as a diagnostic
  3. Set column types after the fields are separated
  4. Work a two-country price list
  5. Avoid repairs that alter unrelated text
  6. Set a delivery format with the recipient
  7. Compare a domestic file with an international one

Read a single record before changing settings

Work on a copy of the file. Open it in a plain text editor and locate the header, one ordinary record, and a record containing punctuation in the description. Suppose the header reads Code;Description;UnitPrice and the following row reads A17;Cable;12,50. Semicolons divide three fields. The comma sits inside the price. Import with a semicolon field separator, then assign the price a numeric type using a locale that recognizes a comma as its decimal character.

What a quoted amount tells you

Another supplier sends Code,Description,UnitPrice with a row reading A17,Cable,"12,50". Commas separate the fields, but quotes protect the comma inside the amount. Choose the comma separator and a parser that recognizes quoted fields. The third field then contains the characters 12,50. Only after that step should an application decide whether those characters mean twelve and one half, a text string or something else.

The filename extension is a poor guide to these choices. A supplier can call a semicolon-delimited file shipments.csv because that is the form their software produces. Inspect the actual text and any instructions supplied by the recipient. Distinguish what the current file contains from what the destination expects. Opening the file successfully in your spreadsheet does not establish that an unrelated web portal will read it using the same settings.

Use the shape of the table as a diagnostic

Count the expected columns from the actual PDF or the export specification. A three-column invoice extract should show three columns in the import preview. If every record appears inside one wide field, test the separator visible between headings. If most records show three fields but rows containing a city or monetary amount show four, investigate quoting around punctuation. The error pattern tells you more than guessing a different language for the entire computer.

Check tricky rows before trusting a preview

Some previews show only the start of the file. A month-end adjustment near the bottom may contain a comma in its description while the first fifty subscriptions contain simple text. Include the adjustment in a small test copy along with the original header and a few ordinary rows. Preserve complete records. Cutting a quoted multiline field in half introduces a fresh error that the original exporter did not make.

Watch for a hidden change in scale

A value of 1,234 may stand for one thousand two hundred thirty-four or just over one unit, depending on the document. A column of identical-looking values does not resolve the interpretation. Find a value with a known magnitude, such as a unit price printed beside a quantity and a line total. Compare several such rows. A three-place decimal amount is possible in some unit-price lists, so a universal rule based on the number of digits after the separator would be unsafe.

Set column types after the fields are separated

In Excel versions with the text or CSV connector, begin from a blank workbook and choose the file in the data import flow. Select the matching delimiter and look for the transformation option. Check whether automatic type detection has already added a step. A mistaken conversion early in the import can obscure the original text. Remove or adjust it and assign data types in a deliberate order using the source values.

Assign a locale where the numbers live

If the source uses a decimal comma and the workstation normally uses a decimal point, use the type conversion option that accepts a locale for the price or amount column. Leave customer identifiers and item descriptions as text. A locale identifies conventions used to interpret characters, while the resulting numeric column can later display according to your workbook settings. Microsoft explains this distinction in its Power Query data types and locale documentation.

Date columns need their own evidence. A delivery dated 04/05/2026 could refer to two different days under common conventions. Look for a date that contains a day greater than twelve, or ask the issuer which order it uses. If the document never resolves the ambiguity, keep the source string while checking. Guessing based on the customer's address can fail when a multinational supplier generates all invoices using a centrally configured system.

Work a two-country price list

A parts distributor receives a German-language price list and a US spreadsheet template. The PDF lists quantity 3, unit price 12,50, and line amount 37,50. The destination workbook expects a numeric amount it can sum. Use the PDF to CSV table converter to extract the table, check the values against the PDF and inspect a downloaded sample in a text editor. The converter's output separators and the punctuation printed inside the source amounts serve different roles.

Verify a row with an independent calculation

In the workbook, keep quantity, unit price and line amount in separate columns. After location-aware conversion, calculate quantity times unit price on a copy and compare it to the displayed line amount. Three multiplied by twelve and one half should give thirty-seven and one half for this example. A result of three thousand seven hundred fifty points to a scale error. Review the original string and conversion sequence before editing individual numbers.

Extend the test to a return, a large total and a zero price if those occur. A negative quantity can legitimately reverse a charge. A grouping mark may appear only once amounts exceed one thousand, so all small test records can pass while large records fail. Keep discounts or tax adjustments out of the simple multiplication test unless the source's line total clearly includes them. You want each test to diagnose one interpretation at a time.

Avoid repairs that alter unrelated text

A global replacement of every comma with a period can change product descriptions, addresses, quoted notes and identifiers. Replacing every semicolon with a comma can introduce an additional field when a description already contains a comma. Use a parser to identify fields and reserialize the resulting records with the destination delimiter. Transform numeric punctuation only in the specific columns whose source meaning you have verified.

Distinguish spaces from grouping characters

Some amounts display spaces between thousands and hundreds. A PDF may expose a nonbreaking space or a narrow spacing character when copied. The result can look correct in a spreadsheet while remaining text and failing arithmetic. Test a value containing the spacing character alongside a small ungrouped value. Compare their numeric behavior after import. Apply a targeted cleaning step only to confirmed amount columns, documenting exactly what you removed.

Set a delivery format with the recipient

Ask the recipient for a successful fictional example or the import specification. Record its field delimiter, decimal character, encoding, header names, date order and treatment of missing values. Include the accepted date format even if both parties currently use the same regional settings. A destination running in another region or an integration reading the file on a server may make different default choices from the colleague who opens it on a desktop.

Test the file after the final save

Spreadsheets sometimes choose export settings independent of the settings used to import. Open the delivered copy in a text editor, using its final filename. Check one field containing punctuation and one amount with a decimal fraction. Then use the actual destination import preview where available. Confirm the row count and the sum of one well-understood numeric column. A perfect-looking workbook is an intermediate check. The delivered file is the object that needs to pass.

Know when a different format helps

If the recipient only needs to review statements in Excel, a workbook can carry numeric and text cells directly. The bank statement to Excel tool offers that route for a digital statement. CSV remains useful where a ledger or catalog loader specifically expects delimited text. Keep the destination's requirements alongside the original PDF so the next person can reproduce the transformation without changing their operating system locale.

Explain a one-column file to a colleague

Send the first fictional header and record, state which character separates fields, and identify how a decimal amount appears inside a field. For example, say that the second file has three semicolon-separated fields and the last field uses decimal commas. Give the expected result for one calculation. This short explanation is more actionable than telling someone to try lots of regional settings until the sheet looks right, and it gives the recipient a clear success condition.

Compare a domestic file with an international one

A team receives one report from a local office and another from its overseas branch. Their header names match and both use semicolons. One file writes its prices with decimal points while the other uses decimal commas. Combining them first and applying a single amount conversion can silently change the values from one branch. Import each source with its verified locale, confirm the types and control totals, and then append the normalized records. Retain a source branch field so any mismatch can be traced to its origin.

Check the total for each branch after the append as well as the combined total when the currencies match. An equal and opposite conversion error could leave a combined number unchanged, masking problems with both sources. If the offices send different currencies, keep separate controls for each currency throughout the process. Attach an example of the original source representation to the import notes so another analyst can reproduce your settings using the next month's files.