Forum Discussion
Excel .csv Conversion - Date-Formatting keeps returning even with prior formatting
Looks like you need to stick to the regular ASCII keyboard characters such as tilde "~", underscore "_" or vertical line "|". The attraction of the non-breaking hyphen or n-dash was their visual similarity to the normal hyphen.
As I understand it, anything you do by formatting is lost once you convert to CSV. I imagine the damage is done once you bring a CSV file back into Excel and it tries to 'make sense' of it. Viewed in a text editor and the CSV file is probably still OK.
The formula idea only kicks in once the damage is done and you have a mix of text, US dates and 'rest of world' dates. By inserting a formula column that returns the day and month for any date (a number) as text you get to see what was in the CSV file before Excel helpfully scrambled it. Copy/paste values could be used to overwrite the misbehaving ranges with something more acceptable.
Not that I would consider this a well-ordered process. I really dislike such manual processing.
One further thought. Have you tried importing the data using Power Query. That should provide the opportunity to edit and adjust the number formats before trying to load to an Excel table.
PeterBartholomew1No never tried Power Query, but I also don't think it's possible. We have a scanning system in place which delivers all data already in excel-files. So there's only importing from one type of excel-file to a .csv. We don't have a text-file of raw data which is then exported to .xlxs.
If, at any point in the process you have a valid file (.txt, .csv, .xlsx), Power Query will import it and, assuming the data is laid out as a database table, it can be loaded to a Table in Excel. If you chose you could separate the upper and lower limits into two fields or replace the hyphen by underscore (say) before loading to the Excel table. Fresh data could be appended from multiple files.
If you need more flexibility, SergeiBaklan 's approach should offer that.
Opening csv file Excel performs all operation as with excel file, that's how the parser works. For example, if you have comma delimited file with one text string
1,2,=a1+b1
an open it in Excel, result in a1, b1 and c1 will be
1 2 3
and working formula in C1.
To prevent default converting of texts to numbers and dates perhaps the easiest way is to add the space at the front of text which could be converted, or two single apostrophes, but it's more visible.
If such data
save as csv and open it in Excel, we will have
i.e. only A2 is converted.
Otherwise Get Data from Text File using legacy connector and applying Text format to all columns on the third step of the wizard.
