Excel Import Shows Phone Numbers as 1.39E+10 and Dates as 45000—Is the File Corrupted?

Bottom line first: When an Excel file filled in by operations staff is imported and phone numbers become scientific notation such as 1.39E+10, or dates become five-digit numbers such as 45000, in most cases the file is not corrupted, and the code is not necessarily wrong. The column was stored as numeric values in Excel, and the import did not read the formatted text. Each cell in xlsx has a type and display format. First confirm the file format (xlsx or CSV) and the reading method, then decide whether to change the template or the code. If this order is reversed, it is easy to make changes that solve nothing. Based on common 2026 delivery experience, this type of issue appears at a not-low frequency in rework on backend system import features.

Phone Numbers Become Scientific Notation and Dates Become Five Digits—Which Layer Is the Problem?

An xlsx file is essentially a set of XML inside a compressed package. Each cell stores the raw value plus a display format ID. When the phone number column is set to General or Number format, what is stored is the number 13912345678; it only becomes scientific notation when displayed. Dates are the same: Excel stores dates as serial numbers starting from 1900, and around 45000 roughly corresponds to around 2023, so that integer is what gets read.

So the root cause falls into two categories: one is that the value has already been changed in Excel; the other is that the value has not changed, but the import did not take the formatted text. The former cannot be rescued by changing code; the latter can be solved by changing the reading method, so the order of judgment must not be reversed.

• 11-digit phone numbers: Stored as numeric values generally do not lose precision; both reading formatted text and converting numeric values to strings can restore them. When the display layer already shows scientific notation, it is easy to misjudge the file as corrupted.
• ID card numbers and bank card numbers: They exceed the 15 significant digits of double precision. Once stored as numeric values, the last few digits are zeroed out, and no reading method can recover them; the only option is to have operations staff re-enter them as text.
• Date columns: The underlying value is a serial number. Conversion must use the 1900 date system consistently and account for time zones; manual conversion often makes the entire column off by one day.
• Codes with leading zeros: After employee ID 001 is stored as a numeric value, the 0 disappears, which is an irreversible loss.

First Distinguish xlsx from CSV—They Go Wrong in Different Places

xlsx has type and format information that can be read; CSV is only plain text, and there is no such thing as 'stored as numeric' in the file itself. When Excel opens a CSV, it guesses column types according to its own rules, guesses the phone number as a number, and then displays it as scientific notation. That is, in CSV scenarios the file content is often correct; the problem occurs at the step of 'opening it in Excel to look at it'. The server parsing CSV as text generally does not lose precision; what really needs to be guarded against is encoding and delimiters.

• xlsx: You can read cell types and display formats, and use a mature reading library to get formatted text. Pitfalls are concentrated in precision loss, date serial number conversion, and multiple sheets.
• CSV: It can only be parsed as text. Pitfalls are concentrated in encoding (mixed GBK and UTF-8), commas or line breaks inside fields, and nonstandard quote escaping.
• Common misconception: Asking operations staff to first open the CSV in Excel, save it as xlsx, and upload it. This step actually writes the phone number as a numeric value, effectively creating the precision problem yourself.
• Implementation rule: For internal operations scenarios, uniformly accept xlsx and read formatted text; for external or system-to-system exchange, uniformly accept CSV and parse it as text. Do not mix the two pipelines.

Three-Layer Parsing: What the File Layer, Cell Layer, and Business Layer Each Handle

A relatively stable delivery approach is to split import into three layers, write down the responsibility of each layer, and when something goes wrong, locate directly which layer it is in. This split is not for architectural beauty; the three layers receive different inputs. If they are mixed together during debugging, it is impossible to determine whether the file itself is problematic or the field validation is too strict and blocks normal data.

1. File layer: Determine format, size, encoding, whether it is encrypted or contains macros; reject directly if it fails, and do not enter the parsing flow.
2. Cell layer: Read the formatted text by column rather than the raw numeric value, and explicitly declare the expected type for date and numeric columns.
3. Business layer: Run regex and range validation on phone numbers, ID numbers, amounts, and enums; for failed rows, record the row number, column name, and reason.

The second layer should not try to guess. Reading according to the column types declared by the template is more reliable than writing a pile of judgments like 'does this cell look like a date'. The third layer must be able to return a failed-row list; otherwise operations staff can only compare row by row with their eyes. This is a link with more rework in import features, and it is also a direct standard for judging whether an import is qualified.

Should the Template Lock Key Columns as Text, or Should the Code Be Compatible with All Kinds of Dirty Data?

This item easily causes disputes in import requirements. In experience, template constraints plus a small amount of fault tolerance have a lower overall cost than being compatible with all kinds of dirty data. Every additional compatibility branch adds another judgment that no one dares to delete later, and coverage is still incomplete. The criterion is plain: users download the template, fill it in, upload it directly, and can import successfully without manually changing any cell format—that counts as qualified. If they must first set the columns to text themselves, the template itself is not done right.

• Option A, template constraints as the main approach: Preset text format for phone number, ID number, and employee ID columns in the template, and state the format and example in the column header. Development effort is small; a usable version can usually be produced in a typical range of 1–2 days.
• Option B, full format compatibility: Perform type guessing and multi-format fallback for all columns. Development plus testing effort is, by experience range, about 3–5 times that of Option A, and it will still miss a few cases.
• Compromise approach: Lock key columns through the template, allow fault tolerance for only 2–3 common date formats in the date column, and strictly validate everything else and send it back for re-entry.

A situation often encountered in projects: the client provided a historical template that was an old version from two years ago, and the phone number column in the template was in numeric format. Operations staff filled it in according to habit and uploaded it directly, so what the server read was all scientific notation strings. The constraints at the time were that we could not change the operations staff's form-filling habits, and there was only one week left before launch. The approach was to change the server side to read formatted text, lock that column as text in the template, and add a pre-validation step before import to highlight unqualified rows in red. The cost was two rounds of rework before and after; this import took about 3 days more than planned, with a typical range of 2–5 days.

Applicable Scenarios and Inapplicable Boundaries

Frame the boundary in one sentence first: For imports within a few thousand rows, with fixed fields, filled in by internal operations staff according to a template, 'template constraints + three-layer parsing + failed-row list' is enough; there is no need to introduce async tasks and message queues. Forcing the complexity of a heavy solution onto a light scenario often doubles launch time while yielding little benefit.

• Suitable: Fixed templates filled in by internal operations staff, 10–30 columns, within a few thousand rows per import, requiring immediate feedback on which row is wrong.
• Suitable: Scenarios where field types are clear and the business can be required to re-enter data according to the template; constraint cost is lower than compatibility cost.
• No need to add: Imports of a few hundred rows per import, used only once or twice a month, are not worth async processing, resumable uploads, or file sharding.
• Not recommended: Writing dozens of guessing branches to be compatible with all kinds of dirty data; it is better to send it back and ask the business to re-enter, which has lower long-term maintenance cost.
• Need to change the approach: External files with uncontrollable sources should be data-cleaned first before entering the import API. Only when a single import exceeds ten thousand rows or multiple people import concurrently is it worth doing async parsing and batch database writes.

Frequently Asked Questions

The phone number already shows as scientific notation—can the backend still restore the full number?

An 11-digit phone number stored as a numeric value generally does not lose precision; reading formatted text or converting the numeric value to a string can restore it. If it has already been truncated or has decimal places, the only option is to have operations staff re-enter it in text format.

Can both date formats, 2026/3/5 and 2026-03-05, be recognized?

You can add fault tolerance for 2–3 common formats at the cell layer. A more reliable approach is to set that column as date format in the template and provide an example, reducing guessing branches.

After CSV import, all Chinese characters are garbled—is the file corrupted or is the encoding wrong?

In most cases, the CSV was saved in GBK or GB18030 and the server reads it as UTF-8; the file itself is not corrupted. Detect the encoding first and then convert, or uniformly require xlsx uploads.

When importing tens of thousands of rows, the page keeps spinning and finally times out—is the file too large?

A common cause is synchronous parsing plus row-by-row database writes, where time grows with the number of rows. For more than 10,000–20,000 rows, it is advisable to switch to an async task, write to a temporary table first, and then validate in batches.

Import failure only says format error—how do operations staff know which row to change?

A qualified import should give the row number, column name, and specific reason, and support exporting a failed-row list. If it only gives one overall message, all rework cost falls on operations staff.

If the current import still stops at 'report a format error after upload', you can start with three things: lock key columns as text in the template, change reading to take formatted text, and add a failed-row list. Based on delivery experience, this usually lands within 1–2 days. Conversely, for imports of a few hundred rows per import that are used only a few times a year, there is no need to introduce async processing and a cleaning platform; relying on template constraints and asking the business to re-enter data has lower overall cost.