Excel to CSV corrupting punctuation and accents

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
I need to transfer a large database from and xlsx file to a csv file in order to update a website. When I try to do this all apostrophes and accents are corrupting. Is there a way of preventing this?????


Thanks
 

GJSBED

Board Regular
Joined
May 3, 2016
Messages
91
I need to transfer a large database from and xlsx file to a csv file in order to update a website. When I try to do this all apostrophes and accents are corrupting. Is there a way of preventing this?????


Thanks

Could be the font the XLSX is in is not CSV compatible?

Put everything in Arial and see what happens.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
How exactly are you trying to convert the XLSX data to CSV?
Are you sure that issue is with the XLSX to CSV conversion, and not with how the website needs/wants it?
What program are you using exactly to view the CSV file once it is created?

Can you post an example of this corruption?
So, show some data in Excel format, and then what it looks like in the CSV file afterwards.
 

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
I have tried 4 different ways I have used the xlxs and saved as a csv, I have copy and pasted from the xlxs into the csv, I have copy and pasted from google sheets into the csv and I have saved the xlxs into xls and then saved as csv. All have the same result they look fine in the csv, but if i save and reopen csv it is corrupted and its corrupted if I upload the csv file into the website.

Corruptions are
' becomes Ô or Õ
é becomes Ž

etc

<colgroup><col></colgroup><tbody>
</tbody>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
All have the same result they look fine in the csv, but if i save and reopen csv it is corrupted
So, from what you are saying, it sounds like the conversion to CSV is working fine.
It appears to be this "saving and reopening the csv" which is corrupting that.
So how exactly are you doing that step?
What program are you using to view the CSV?
Can't you just close it without saving it after viewing it (if you are just viewing the converted file, there should be no need to re-save it again)?
 
Last edited:

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
I am using excel to view it I think. Basically it is the application that it opened in when I downloaded a sample csv from the website.
I can try not saving the doc and uploading it to the website with it open, alternatively is there a better application to open the csv with?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
NEVER use Excel to view CSV files if you want to see what the data really looks like! Excel does automatic conversions on the data as it opens it! So you will not get an accurate view of what the CSV data actually looks like.
Therefore, NEVER open a CSV file in Excel, and then re-save it! You will save those automatic conversions Excel did!

Once you have converted the data to a CSV file, use a Text Editor to view the data to see what it really looks like.
Microsoft usually comes with NotePad and WordPad. You can use either of those to view the CSV file. There are also many good after market Text Editors.
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
This sounds like an encoding-decoding problem. You can try this.

Rename a copy of the downloaded file to somename.txt. Open the .txt file from within Excel, not from a file manager. This will bring up the text import wizard. On the first page of the wizard, there is a dropdown box labeled "File origin:". Change the setting to "65001: Unicode (UTF-8)". That setting may be a long way down the list. You also have to set the file type to "Delimited". Click "Next".

On the second page of the wizard, check "Comma", then click "Finish". With luck, the file will open without the text corruptions.

You can open csv files with Notepad but there may be a maximum limit to the file size. From the File >> Open menu in Notepad, you can change the encoding from "ANSI" to "UNICODE" (actually UTF-16LE) to "UNICODE big endian" (UTF-16BE) to "UTF-8". The default Excel encoding is UTF-16LE; the Internet usually uses UTF-8.
 

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
Cheers thisoldman, that opens up the csv without problems. What i then need to be able to do is add my data (copy and paste) and then save the doc as a csv again so that i can upload it to the website. Is this possible. I tried saving and then changing the file ext back to .csv, but the website wouldnt except it.??
 

Forum statistics

Threads
1,082,126
Messages
5,363,317
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top