This is a really quick tip in Excel while opening CSV files
You’ve different methods available for the data export to csv like bcp,OPENROWSET etc.
One issue that I came across recently in this was the CSV file after getting generated, if you try to open it in excel all column values gets wrapped inside same cell in excel as below.
You’ve different methods available for the data export to csv like bcp,OPENROWSET etc.
One issue that I came across recently in this was the CSV file after getting generated, if you try to open it in excel all column values gets wrapped inside same cell in excel as below.
This was not what I expected as I always thought Excel would render CSV format properly onto different columns.
I sent the sheet to my colleague and thought I would clarify this issue with him to see if he has any ideas. But to my surprise I found sheet being displayed properly in his machine. This gave me an indication that its something to do with client machine configuration. I did some analysis and found out the solution.
Apparently regional settings of my machine had list separator set as ; as per below
I changed it to comma character (,) and then it worked as intended.
So key thing to understand is it uses this setting to identify column separator within the sheet.
