A 5,000-Row Vendor CSV That Breaks on Import
A vendor sends a CSV containing 5,000 account records: column A holds 16-digit account numbers, column B the IFSC, column C the account opening date and column D the balance. You open the file directly in a spreadsheet by double-clicking it, and three things go wrong.
First, column A displays as 1.23457E+15 and, when you widen the column, the last four digits of every account number are zeros. Second, column C shows dates sitting hard against the left edge of the cell, and a VLOOKUP against the master file returns #N/A for about 400 rows even though the account numbers in both files look identical. Third, after you add a helper formula in column E, a colleague copies the sheet into an email and recipients report #REF! errors, while rows past about 1,000 are simply missing from what they see.
You have to explain each symptom and fix the import so that the same three failures do not recur next month.
1. Explain the 1.23457E+15 display and the zeros, and prescribe the correct fix.
Read the worked answer
The display and the zeros are two different problems that look like one.
The display is scientific notation: the column is too narrow, and widening it brings back ordinary digits - if the value survived intact. It did not. A spreadsheet stores numbers as double-precision floating point, which holds only about 15 significant decimal digits. A 16-digit account number is one digit past that, so the sixteenth digit is lost and replaced by a zero at the moment of entry. Every account number in column A is now wrong, and no amount of column widening or switching to Number format will recover the lost digit, because the original value is gone - only the rounded double remains.
Correct fix: re-import the file rather than repairing it after the fact. Use the text import route (Text Import Wizard, or Power Query's Get Data > From Text/CSV) and set column A's data type to Text before the data lands in cells. Alternatively prefix entries with an apostrophe or pre-format the column as Text, then paste. Then re-verify the last four digits of a sample of rows against the vendor's source file - the import must be repeated from the original CSV, not patched, because the digits already on screen cannot be trusted.
2. Diagnose the left-aligned dates and the 400 rows of #N/A.
Read the worked answer
Both symptoms point to the same root cause: values entered as text where the lookup or the file expects numbers or true dates.
Left alignment is the tell. Spreadsheets right-align genuine numbers and dates and left-align text, so a date column that hugs the left edge is text that merely looks like a date. Those values will not match a master file where the same dates are stored as date serial numbers - which is why exact-match lookups on them fail even when the printed strings are identical.
For the 400 #N/A rows, 'they look identical' is the trap. The usual culprits are trailing or leading spaces carried over from the CSV, non-printing characters, an account number stored as text in one file and as a number in the other, or a stray leading apostrophe. Diagnose before repairing: compare LEN() of the same value in both files, and run ISTEXT/ISNUMBER on each side. If the lengths differ, TRIM() and CLEAN() the imported column. If one side is text and the other numeric, convert with VALUE() or multiply by 1 - but only after confirming the conversion does not reintroduce the 15-digit rounding problem from column A, which is why the account number should stay text on both sides and be matched as text everywhere.
3. Explain the #REF! errors and the missing rows in the emailed copy, and state what you would send instead.
Read the worked answer
Both are consequences of sending formulas rather than results.
The #REF! errors appear because the emailed copy carries live formulas that point at cells, sheets or a workbook the recipient does not have. Once the referenced range is out of reach, the formula has nothing to resolve and the spreadsheet reports a broken reference. The missing rows have a similar cause: what the colleague copied was a visible selection, or the sheet was filtered or partially loaded at the time of copying, so the range past roughly row 1,000 never entered the email.
What to send: a copy in which column E has been converted to fixed values - copy the helper column, then Paste Special > Values, so no formula travels - and the whole data range is selected deliberately (for example by selecting the full used range or exporting the sheet) rather than by dragging over what happens to be on screen. Verify by checking that the last row of the copy matches the last row of the source, and that row count is 5,000.
The habit worth carrying forward: the original working file keeps formulas and stays at your desk; the shared file carries values, a stated row count and a note of the date it was exported.