Forum Discussion
Ensure Excel retains field value as text string, rather than converting to number
glennlee You can connect to the CSV with Power Query and set-up the transformations once. Like indicating that this particular column should be seen as text. Next time when the CSV gets updated, refresh the query and the import will run automatically as is was set up the first time.
It sounds like this is also a CSV file you are importing repeatedly. So what's the source? Can something be done at the starting end to make that file clearly a text file (e.g., instead of it reading as "000000000" make it "TXT000000000" and then just use a formula to strip off the "TXT"? Granted, it's still a step, but if it's a repetitive thing, you could write a macro or VBA that takes care of that.
The other possibility, if it's really only and always "000000000" --and I realize you may have just been using that as an illustration of a generic 9-character code--but if it's really just nine "0" characters, then it has no meaning anyway.
Then there's always a VBA routine that goes through the import wizard......
[For the record, I'm not someone who writes VBA routines, but I do know they can be helpful at times.]
mathetes - Thanks for the pointers. The file is coming from a piece of software that my org built. We can certainly do things to alter the outputs in the file, but was hoping that there was something we could do to either label certain data in the CSV a particular way so it was treated the same each time by Excel or get Excel to handle file fields a certain way, without building in VBA or going through CSV import wizard. In other words, hoping to avoid actually tampering with the file contents to force this.
glennlee You can connect to the CSV with Power Query and set-up the transformations once. Like indicating that this particular column should be seen as text. Next time when the CSV gets updated, refresh the query and the import will run automatically as is was set up the first time.