Error in CSV import

[k1asit]

I need to import a large CSV file into Excel 365 and I noticed that there are sometimes errors in the value of a certain field. The field I'm talking about is a field with a unique document code and it displays correctly in 99% of the rows but just sometimes it's wrong.

It has 5 columns: ID, Path, Document code, Year, Document group

For instance the document code "1E0241" is displayed in Excel as "1,00+241" while the document code "3T0142" is displayed correctly. The cell format for this faulty imported field is scientific while the rest is displayed as standard text. I get the problem in Excel 365 both on Windows and Mac.

How is it possible Excel displays the imported fields in the same column as a different format?

[marcus_sommer]

It's very likely that if you open the csv per editor the value of "1E0241" is there correct stored. If so it means the issue is a converting-logic within Excel which applies an own interpretation by opening the file which couldn't be really customized.

Therefore you need to bypass the OnOpen logic - maybe by copying the data as one-column-strings into Excel and then performing the text-in-columns feature manually because in the third step you could define for each column how it should be handled and by columns which Excel interpret wrongly you set it to text.

[henry74]

Excel tends to 'guess' at the format for each cell individually rather than as a column. You can typically get around this by either using the Import Wizard or using Power Query.