Convert CSV to Excel Without Changing the Data

To convert CSV to Excel without losing data, do not double-click the file. Import it instead: in Excel, choose Data, From Text/CSV, pick Transform Data, set codes and long numbers to Text and dates to Date with the right locale, then load and save as an .xlsx workbook. Values arrive exactly as the CSV held them.

What changes when you convert CSV to Excel by double-clicking?

Double-clicking a CSV makes Excel open it with its default settings, and those settings convert each value to whatever type it looks like. Leading zeros vanish, day-first dates can be read as month-first, and numbers longer than 15 digits are rounded. The file looks converted, but some values are no longer the ones in the CSV.

Microsoft's guide to importing and exporting text and CSV files says that when Excel opens a CSV, it uses its current default data format settings to decide how to import each column. Its own examples are a date column in an order Excel does not expect, and numbers with leading zeros that you want to keep.

The 15-digit point comes from Excel's specifications and limits, which list number precision as 15 digits. A 16-digit card number or a 19-digit transaction reference typed into a cell as a number keeps only the first 15 significant digits. The rest become zeros. There is no undo for that once the file is saved.

Which statement columns are at risk

A typical bank statement CSV has a handful of columns that suffer:

• Reference and check numbers, which may start with zeros or be very long.
• Account and routing numbers, which are identifiers, not quantities.
• Dates, when the file and your computer disagree on day and month order.
• Descriptions that happen to look like numbers or dates, such as 1-2 or MAR 4.

The amount column is usually safe, since amounts are real numbers. The others need an import that tells Excel what they are.

Importing the CSV with Data, From Text/CSV

The From Text/CSV import reads the file through Power Query, Excel's data import tool, and shows a preview before anything lands in the sheet. That preview step is what lets you set each column's type yourself instead of accepting Excel's guesses.

The steps, from the Microsoft guide above:

1. Open a blank workbook in Excel.
2. On the Data tab, in the Get & Transform Data group, choose From Text/CSV.
3. Find the CSV file and select Import.
4. In the preview, check the Delimiter list. If everything sits in one column, pick the separator the file actually uses.
5. Choose Transform Data rather than Load. This opens the Power Query editor, where you can change column types.
6. Set the type of each column, as described in the next section.
7. Choose Close & Load to put the result on a new sheet.

The guide also describes a legacy route: renaming the file from .csv to .txt before opening it makes Excel run the Import Text Wizard, where you can set a column format in a later step. The Power Query route is the one to learn, because the steps can be refreshed when next month's file arrives.

The type Power Query has already guessed

Microsoft's article on data types in Power Query explains that, for unstructured sources such as CSV and text files, Power Query detects types by inspecting the first 200 rows and adds a Changed Type step. That guess is often fine for amounts. It is the step you correct for codes and dates.

Setting column types for codes, dates and amounts in the CSV import

Setting column types means telling Power Query what each column holds before it loads: Text for anything that is an identifier, Date for dates, and a number type for money. Choose a type by selecting the icon on the left of a column heading, or from the Data Type list on the Home or Transform tab.

Here is an example statement CSV, with made-up values, and the type each column should get:

The Power Query article describes Fixed decimal number, also called Currency, as having four digits after the decimal separator and always being precise. Decimal number is a 64-bit floating-point type with up to 15 digits of precision. For a statement, either works for display, but Fixed decimal avoids tiny rounding differences when you sum thousands of rows.

When you change a column that Power Query already typed, it asks whether to replace the current step or add a new one. Choose replace, so the column is never converted to a number on the way to Text. If a reference column was already turned into a number by the automatic step, adding a new Text step afterwards brings the digits back as text, but the leading zeros are already gone.

Dates in the wrong order after a CSV import

Dates land in the wrong order when the CSV writes them day first and Excel reads them month first, or the other way round. Some dates become wrong dates, such as 4 March for 3 April, and others, such as 24/09, show an error. Fix it with a locale during the import, not with formulas afterwards.

The Power Query data types article walks through this case. With the locale set to English (United States), a UK-format date column produces errors when changed to Date, because a day such as 24 is read as a month that does not exist. The fix is to right-click the column heading, choose Change Type, then Using Locale, and pick Date with the locale that matches the file, such as English (United Kingdom).

Check the result before you trust it. Pick a date late in the month, where day and month cannot be confused, and confirm it reads correctly. Then check one early date, such as the 3rd, which is the kind a wrong order would turn into a valid but wrong date.

Statements with month and day only

Some bank CSVs, and many conversions of older PDFs, carry dates with no year. Excel will either add the current year or treat the value as text. Add the year in Power Query with a custom column before you set the type, using the statement period for the year. If you are converting several years of history, downloading old bank statements covers how far back banks usually let you go and in which formats.

Saving the imported CSV data as an Excel workbook

Saving as an Excel workbook keeps the column types you set; saving back to CSV throws them away. After Close & Load, use File, Save As, and pick Excel Workbook (.xlsx). The sheet, the query and its type steps are all stored in the workbook, and the CSV stays untouched.

If you save the sheet as CSV instead, Excel writes the values as they now appear, and the next person to double-click the file meets the same conversion problems you just avoided. Keep the original CSV as the source and treat the workbook as the working copy.

The query is a bonus. Next month, replace the CSV in the same folder with the new file, or point the query at it, and choose Refresh on the Data tab. The same types and locale apply again, with no repeat of the import steps.

A five-point check after saving

Run these checks on the saved workbook:

1. Row count. The number of data rows matches the number of lines in the CSV, less the header.
2. One reference. A reference with leading zeros shows all its digits.
3. One late date. A date after the 12th of a month reads correctly.
4. The total. The sum of the amount column matches the change in balance, or the bank's printed total.
5. The file type. The file name ends in .xlsx, not .csv.

If the table is headed for a presentation rather than more analysis, moving a PDF table into PowerPoint covers pasting it so it stays editable.

Is the converted CSV safe to open and share in Excel?

A converted CSV is safe to share once its values are checked and its cells contain only data, not formulas. The specific risk is CSV injection: a cell that starts with =, +, - or @ can be read by a spreadsheet as a formula. A description field is the usual place for one to hide.

Bank statements are a lower risk than files from web forms, but descriptions come from merchants and payers, and you do not control that text. Before sharing a workbook built from someone else's CSV, scan the description column for cells beginning with those characters. How CSV injection works and how to defuse it explains which characters matter and how to neutralise them.

Importing as described above helps here too. A column set to Text in Power Query loads as text, so a value such as =1+1 arrives as those characters rather than as a calculation.

Getting the CSV right at the start removes most of this work. FastBankConvert, which is getting ready to open, is built to turn a statement PDF straight into an Excel workbook as well as CSV, so for statements you may not need the CSV step at all; Get started to hear when trial conversions open.