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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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
57,664
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
57,664
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
57,664
Office Version
  1. 365
Platform
  1. 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,074
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.??
 

Watch MrExcel Video

Forum statistics

Threads
1,133,427
Messages
5,658,733
Members
418,467
Latest member
sc356448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top