1

Problem

I'm using Excel 2019 and I have this .csv file(Multiple language) which is in UTF-8 encoding and when I try to open the file, Excel opens it in ASCII encoding — the english characters and numbers are displayed correctly but the other language data are in corrupted characters (unrecognisable).

  • I tried to open the .csv file by opening new excel file and import the csv using the data import through text\csv option and after this the characters of the languages are in proper way but the spreadsheet isn't the rows and columns are messed up.

  • Decimal(.) and Thousands(,) separators in both the excel and the list separator(,) in the regional settings of the windows are in order.

  • And there seems to be no text wizard in Excel 2019? And I'm wondering if there is a way to change default encoding of .csv files in excel 2019(?).

  • I tried to change the encoding using Registry Editor and there is no folder named option under the Excel folder in the Registy Editor.

Edit: By changing the encoding from UTC-8 to UTC-8 BOM the encoding issues for multiple language data is resolved but many data are mismatching the rows and columns.

muralee_maddy
  • 11
  • 4
  • Does your UTF-8 CSV have a byte order marker (BOM) header? If not, try adding one. Otherwise, try removing it. Notepad++ or Textpad can be used to determine this. – leeharvey1 Nov 22 '21 at 11:10
  • @leeharvey1 thank you, this worked for the languages encoding issues but there seem to be a problem with rows and columns this csv file has around 6 variables/columns but with in each cell there are so many words upto 200 words and many data aren't in respective rows and columns. I know this isn't a issue with the CSV file because its from kaggle and i have viewed it there on the site there is not mismatching data issue there. – muralee_maddy Nov 22 '21 at 13:31
  • Sounds like a quoting issue. If cells contains a comma, tab, CR, or LF character, they should be enclosed in double-quotes to inform Excel to keep the text together in one cell. Otherwise, text can be split and sprawl across multiple columns and rows, effectively ruining alignment and table formatting. – leeharvey1 Nov 22 '21 at 14:15
  • @leeharvey1 the double quotes are present still no change. – muralee_maddy Nov 24 '21 at 17:58

0 Answers0