Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

CSV change column type

Copper Contributor

Hello,

 

I have to upload a CSV file to another non Microsoft system and it ask me an specific format for my columns.

I configure my columns as Text and it looks like this:

daviduribemora1_0-1674745941971.png

I save the file with this data and column format (Text).

However when I uploaded the data into the other system it showed an error, so I opened my csv file and I found out that both data and format of my columns has been automatically change (General) and looks like this:

daviduribemora1_1-1674746106836.png

 

Has anyone found a way to solve this?

 

Really apreciate your observations ;)

3 Replies

@daviduribemora1 Do not open a CSV directly in Excel. Import it via the legacy text wizard or use Get&Transform Data, from CSV/TXT. Then you have the chance to explicitly instruct Excel to treat columns as Text or something else. When you open it directly, Excel guesses what you want to achieve and formats anything that looks like a number as a number disregarding leading zeroes as in your case.

@Riny_van_Eekelen   Thanks this was helpful.  The problem for me is that when I close to load it, it includes a header row and has formatted it into a table and the requirements for my upload doesn't allow a header.  

@Jaustin1417 Then you used the "From Text/CSV" option. That starts Power Query and indeed loads the result back as a table with headers.

 

If you go for the "Legacy wizard, from Text" it doesn't do that. Then it will be the same as when you use the Text-to-columns button. 

Perhaps the link below will help you on your way.

https://support.microsoft.com/en-us/office/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857#I...