Excel to CSV corrupting punctuation and accents

[twl2009]

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]

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.

 

[twl2009]

Hi,

Tried Arial it still corrupts all apostrophes and accents.

 

[Joe4]

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]

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 Õ
[TABLE="width: 171"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: left"]é becomes Ž

etc[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

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)?

 

[twl2009]

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]

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]

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]

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.??