Why Hindi text and leading zeros break when you save a CSV (and the fixes)

A CSV file is plain text with separators in it. It carries no column types and no dependable statement of which character encoding wrote it, so Excel guesses both. With Indian data it guesses wrong in three predictable ways: Devanagari, Tamil and Gujarati text arrives as हिंदी or a row of question marks; a PIN code stored as 001100 comes back as 1100; and 03/04/2025 becomes 4 March instead of 3 April. All three are fixable, mostly by changing how the file is written rather than how it is opened.

Symptom, cause, fix

UTF-8, and UTF-8 with a BOM

The byte order mark is three bytes, EF BB BF, at the very start of the file. In UTF-8 it says nothing about byte order; it is purely a marker meaning "this file is UTF-8". Excel on Windows wants that marker. Without it, double-clicking a UTF-8 CSV makes Excel fall back to the system ANSI code page, and each multi-byte Indic character appears as the two or three Latin-1 characters its bytes happen to resemble. That is the whole ह family of symptoms, and it is only a display failure: the bytes on disk are still correct, so a proper import recovers the text intact.

????? is worse. Excel's plain CSV (Comma delimited) save writes the ANSI code page, which contains no Devanagari at all, so it writes one literal question mark per character and the information is gone from that file for good. Since Excel 2016 the Save As list carries CSV UTF-8 (Comma delimited) (*.csv) as a separate entry, which writes UTF-8 with a BOM. Use that one, or do not use Excel to write the CSV.

The BOM cuts both ways. Plenty of older importers, including several Indian accounting packages, read those three bytes as part of the first heading and then report that the file has no Invoice No column. Python has the same trap in a friendlier form: pd.read_csv("sales.csv") on a BOM file hands you a first column named Invoice No, which is why df["Invoice No"] then raises a KeyError. Read it with encoding="utf-8-sig" instead.

Leading zeros: the honest answer

CSV has no type system. There is nowhere in the format to record that pincode is text, so the zeros in the file are always intact and the loss happens entirely in whatever reads it. Three common workarounds do not hold up. An apostrophe prefix ('001100) keeps the value in the sheet but is not written into the CSV. ="001100" survives a round trip and writes a literal ="001100" that most import screens reject. Formatting the column as Text after the file is open is too late, because the zeros were discarded during the parse.

What actually works, in order of how often I use it:

1. Power Query. Data → Get & Transform Data → From Text/CSV → choose the file → set File Origin to 65001: Unicode (UTF-8) and confirm the Delimiter → click Transform Data, not Load → select the column → Transform → Data Type → Text → Home → Close & Load. The query is saved with the workbook, so next month's file takes one refresh.
2. The legacy Text Import Wizard, faster for a one-off. Turn it on at File → Options → Data → Show legacy data import wizards → From Text (Legacy), then use Data → Get Data → Legacy Wizards. Step 3 lets you mark each column Text, or Date with DMY order.
3. Never open it in Excel. In pandas, pd.read_csv("master.csv", dtype={"gstin": "string", "pincode": "string", "mobile": "string"}).

A GSTIN usually escapes this, because at 15 characters with letters in it 07AABCU9603R1ZM stays text on its own. The columns that get hurt are the pure-digit ones beside it: the two-digit state code, HSN codes like 0401, and any invoice series the billing software zero-padded.

Dates flip silently, which is the dangerous part

dd/mm/yyyy and mm/dd/yyyy are only ambiguous when the day is 12 or lower. For roughly 12 days in every month, about 40 percent of a year's rows, Excel reads an Indian date as a valid American one and hands you a real date that is simply the wrong day. For the rest, month 13 through 31 does not exist, so it either errors or leaves the cell as text. You end up with a column that is part date and part string, sorts nonsensically, and looks fine until a monthly total comes up short.

For anything crossing a system boundary, write dates as yyyy-mm-dd. Excel reads ISO order correctly under every locale, it sorts correctly even as text, and pandas parses it without dayfirst. Change the column's display format to yyyy-mm-dd before exporting, because a CSV export records what the cell shows, not what Excel stores.

Delimiters, and where Indian data makes it worse

A well-written CSV quotes any field containing the delimiter, so "Shop 4, Sadar Bazar, Nagpur" is one cell. Trouble comes from files assembled by hand or by a script that concatenates strings without quoting. Lakh grouping is the common version of this: 1,20,000 written unquoted is two fields, and you find out when the amount column totals to something absurd.

Tab is the safest delimiter when text is messy, because tabs almost never appear inside cell values. Pipe suits old importers that mishandle quotes. Semicolon matters when the receiving machine's Windows list separator is set to it. Whatever you pick, the rupee sign ₹ still needs UTF-8; it does not exist in the ANSI code page.

Doing this in the browser

Excel to CSV exports one sheet or every sheet at once, sets the delimiter to comma, semicolon, tab or pipe, and has a UTF-8 BOM checkbox. Keep the BOM ticked when the data has Hindi, Marathi or Tamil text or the rupee sign, and untick it when the target system complains about stray characters in the first heading. Formulas export as their last calculated result, so recalculate first if a figure looks stale.

CSV to Excel goes the other way and detects the delimiter itself. The setting that matters is Convert numbers & dates. Leave it on and amounts behave as numbers; untick it and every column is written as text, which is what saves your GSTIN, PIN, HSN and mobile columns. The cost of unticking it is that amount and quantity columns arrive as text too, and you convert those back yourself. For a file where the identifiers matter more than the arithmetic, that is the right trade. Column widths are set from the content, so nothing shows as #####.

Both tools read the file with JavaScript in your browser, so a salary sheet never leaves the machine. The limit is memory, not a file-size cap: a few hundred thousand rows is fine on a laptop, far less on a phone.

The five-step routine

1. Decide who reads the file next, and ask that system what it wants. Tally, a bank portal and a pandas script all want different things.
2. Set date columns to yyyy-mm-dd display format in the workbook before exporting.
3. Export with UTF-8 BOM on if there is any non-Latin text, off if the consumer is old accounting software.
4. Never double-click the resulting CSV to check it. Import it through Power Query with the identifier columns typed as Text.
5. Verify one known row, the row count and one column total before you send it on. The CSV analyzer reports the first two in a few seconds.

Frequently asked questions

Which should I choose, UTF-8 or UTF-8 with BOM? BOM if Excel will open the file, no BOM if a program will parse it. Excel needs the marker to detect UTF-8; most parsers, including older Indian accounting imports, treat it as unexpected junk in the first field.

My phone numbers already lost their zeros. Can I put them back? Only if every number has the same length. For Indian mobile numbers stored with a leading zero, =TEXT(A2,"00000000000") restores an 11-character value. For account numbers of varying length there is no correct answer, and you have to go back to the source file.

Why does one CSV open fine and an almost identical one lands in column A? Excel uses your Windows list separator, not a fixed comma, when you double-click a file. If that setting is a semicolon, comma files fail. Importing through Power Query lets you state the delimiter and sidesteps the setting entirely.

Is there a way to force Excel to stop guessing types on every file? Not globally. Power Query is the nearest thing: the column types you set are saved in the query, so the same file shape imports identically every month with one click on Refresh. No Excel setting turns off type inference for a double-clicked CSV.