Skip to main content

CSV Handling & Data Import

What is a CSV file, and why is it used for exports and reports?

A CSV (Comma-Separated Values) file is a plain text format used to store tabular data. Each row is a record, and columns are separated by commas. CSV files are widely used because they are compatible with many systems and easy to import into spreadsheet software for analysis.

How do I open or import a CSV file into Microsoft Excel, Google Sheets, or other spreadsheet software?

Microsoft Excel:

  • Open Excel, then choose File > Open and select your CSV file.
  • Or use Data > From Text/CSV to import with additional settings and more control over column data types.

Note: In newer Excel versions, it is not necessary to rename .csv files to .txt, as Excel supports direct .csv import and parsing with these features.

Google Sheets:

  • Open Google Sheets.
  • Select File > Import, then upload your CSV.
  • Choose import options and confirm.

Other spreadsheet software has similar import functions. Always verify column separators and data formats during the import process.

Why does Excel sometimes show long numbers (such as acquirerReferenceNumber) in scientific notation when importing CSV files?

Excel auto-formats long numeric values that exceed 15 digits into scientific notation (e.g., 1.23E+18). This can cause precision loss or truncation, compromising data integrity for IDs or reference numbers.

How can I import a CSV and ensure long numbers or IDs (like acquirerReferenceNumber) do not get converted to scientific format in Excel?

Using Power Query (recommended):

  1. Go to Data > From Text/CSV in Excel.
  2. Select your CSV file and open it.
  3. In the import preview window, click Transform Data.
  4. In the Power Query Editor, locate columns with long numbers such as acquirerReferenceNumber.
  5. Right-click the column header and choose Change Type > Text to treat values as text rather than numbers.
  6. Click Close & Load to import the data, preserving full numeric precision.

Using the Legacy Text Import Wizard (if preferred):

  1. Go to Data > Get Data > From Text/Legacy Wizards > From Text (Legacy).
  2. Select your CSV file and start the wizard.
  3. Choose Delimited, then Next.
  4. Select Comma as the delimiter, then Next.
  5. Click the column with long numeric values and set its format to Text.
  6. Finish the wizard and complete the import.

This approach prevents Excel from converting long numbers into scientific notation and truncating data.

What steps should I follow to import CSV files with long numeric fields as text into Google Sheets?
  1. Open a new or existing Google Sheets document.
  2. Use File > Import and upload your CSV file.
  3. When presented with import options, uncheck “Convert text to numbers and dates” if that option appears.
  4. Complete the import.
  5. After import, select the columns with long numbers (e.g., acquirerReferenceNumber).
  6. Go to Format > Number > Plain Text to format these columns as text and preserve exact values.

If your data has already been converted to scientific notation, clear those cells and re-import following these steps to retain accurate formatting.