Spent my lunch re-entering 238 shipment IDs because the vendor’s CSV had leading zeros and Excel turned half of them into scientific notation (again) - why is this still a thing? What’s your go-to pre-import tool or workflow (Power Query settings, external CSV viewer, anything) to lock column types before opening so I don’t have to babysit every field?
I just never open CSVs directly — Data > Get Data > From Text/CSV, hit Transform, set the ID column to Text, and in Power Query Options turn off auto type detection; save that as a query so you can just refresh next time. Stops Excel from trying to do rocket science with your IDs. Are your vendor files consistent enough that one saved query would cover them?
Same pain after retyping 238 IDs. I just rename the.csv to.txt, Data > Get Data > Legacy From Text, set the shipment ID column to Text, then save as.xlsx so the zeros stick and no 1.23E+07 weirdness. On 365, turn on the legacy wizard once and it sticks across files.
Ugh after the “238 IDs” fiasco I dropped a schema.ini next to the vendor CSV that defines ShipmentID as Text, and Excel stops flipping it to scientific notation. It’s a set-and-forget fix if you import from that folder via the text driver; @ccampbell, have you tried schema.ini for this?