Handling Leading Zeros in CSV Files

Excel examines the first eight records of each field when importing and decides how to treat the column based upon that examination. That can be changed, but... amongst ofhter things, telling it to examine all the rows, say, could mean examining, say, a million records for each of, say, 30 fields... multiplies up, eh? And if none of your rows has characters like you need for the desired effect, it was pointless. One can open in Notepad, say, then insert patterned rows at the start (eight), then delete them after import though. But no one's USER is going to do any part of that, so those records would stay in.

Breaking the file association helps YOU, but only sometimes. I have files I want to import carefully, ones I simply want to open and overwrite, and some that the data in them isn't hurt by this, and the appearance, if it were somehow important (and it can be, though for me the importance is in the output spreadsheet which is already formatted for the material I move over), can be restored with a couple, not fifty, formatting changes. But no USER is ever going to be good with this. But mostly, break the association and you then have to hand-import everything.

Power Query (PQ) is a terrific thing, but it has its own problems, never touted by its proponents, nor even acknowledged. I can't set up a query, then apply it easily to a file with a different name from the one used. Rename the import files to that? Great... unless the currently named "that" is still in use by one or more Users. Hold off until some graet moment? Someone might use old data, or one of the "still in use by" Users might unknowingly use the new data which can lead to action/inaction issues. Think of a spreadhseet used for A/P calls, for example.

Worse, PQ has... its own "I know best" preferences. It like to essentially do a Paste|Special|Values with all formatting changed to Text. Likes may not be a strong enough word. And "essentially" is not fully accuraet: not just the all formatting changed to Text thing, but it also does Precision As Displayed (PAD) unless you override that. That is a HARD thing to notice with more than a wee bit af data being imported until solving some major problem gives you the go-ahead to really delve into it. Like I mentioned above, no one (in the "PQ is awesome, I even use it to cut my hair and boil pasta" camp) talks about it either, so it's a learn-by-screwing-up kind of thing. The fact you're trying to not work with the details —automating something — makes it easy to not worry about things too. It seriously does not want to import formulas, though that is not a problem with CSV material, of course, just a broader issue with PQ.

Easy to overcome all, they say, and it is, if you put the work into it AND have warning of all these things and more so you know the work to do, but... then you're right back to the problem that it is only for THAT data source, i.e., the filename you worked with and one has the issue from above. I'm not even aware of any way to set a preference for each of the import issues I've seen, or even some of them. Once again, like with simple CSV import, MS knows best, MS knows all, MS, like honeybadger, "don't care."

Sad as it is, writing your own import macros is really the only "industrial" (vs. "limping along from one battle to the nmext") solution. And if your organization dislikes macros, well, maybe some Indian company would like to open a new line of support service you could subscribe to.

Second alternative? Muddle through, put out the fires that arise. Each day. Take your lumps in meeting when you get blamed for everything. Accept that whipping boys don't get raises or promotions. And enjoy your vacation leaving the company-paid cellphone at home, whether they like that or not. (They expect you to take it along, and respond, but somehow, being paid for that day and saving the vacation day, does not logically follow...)

Alex B's comment about doing this a lot of this is good.
I'd suggest another approach - Power Query. One of the great strengths of Power Query is that it works by recording your steps so you can have it repeat them on each new dataset. And if they need adjustment you can modify them as needed. And finally having a recording of them provides both an audit trail of the data manipulations and a way to share the process with others.
Note that in newer versions of Excel this is on the Data tab in Get & Transform Data to create them and in Queries & Connections to maintain them.

two other ways around this are
1 duplicate the first data line and put alpha characters in the needed field. delete after import
2 many medical fields have leading zeros, but also characters in the same field
find one of these rows and make it the first data row. no deletion is necessary

To open the file in Notepad you don’t need to change the file extension. Just right click on the file and select “Open With” and then select Notepad.
If you then copy the text into excel from notepad you can access the conversion wizard using Data > Text to columns.
If you are doing a lot of this consider breaking the association between the csv file type and excel. If you change the association to say Notepad double clicking the file will open it in Notepad and in excel file open will launch the wizard.