DMV
asked on
Remove Chinese Characters from Excel Spreadheets used as input
Hello All.
I have an A2K application that reads surveys that from Excel worksheets, and tabulates the reponses for further analysis. This app has been up and running for the past 2 years. The problem arose when the questions were translated into Chinese and place below the English text. The following method is used to copy the survey into an Access table:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTableName, strSpreadSheetPath, False, "A1:V133"
LoadSpreadSheet = True
The Chinese characters are translated into a string of "?" of various lengths. One problem is that a question mark signifies the start of the tabulation process. I've tried ignoring a series of question marks, but some stray characters still remain. Can I change the default character for unknown symbols? Actually, I am open to any idea that will allow me to process the surveys as before, outside going through each survey by hand and removing the Chinese translation.)
Many thanks,
Deb
I have an A2K application that reads surveys that from Excel worksheets, and tabulates the reponses for further analysis. This app has been up and running for the past 2 years. The problem arose when the questions were translated into Chinese and place below the English text. The following method is used to copy the survey into an Access table:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTableName, strSpreadSheetPath, False, "A1:V133"
LoadSpreadSheet = True
The Chinese characters are translated into a string of "?" of various lengths. One problem is that a question mark signifies the start of the tabulation process. I've tried ignoring a series of question marks, but some stray characters still remain. Can I change the default character for unknown symbols? Actually, I am open to any idea that will allow me to process the surveys as before, outside going through each survey by hand and removing the Chinese translation.)
Many thanks,
Deb
You need to go to the table importing to, place it in design view, highlight the memo field and set unicode compression to no.
ASKER
Hi puppydogbuddy.
Unicode compression is set to "No." and yet the characters are still added to the table.
Deb
Unicode compression is set to "No." and yet the characters are still added to the table.
Deb
Hi DMV,
On re-reading your description of the problem, you already have Chinese text entered in your spreadsheet below the English text, leaving you basically two options:
1. Set unicode compression to yes and have the ? characters replaced by Chinese characters if you want Chinese characters left in the memo field.
2. Leave unicode set to no, and can create an update query on the memo field that will replace ? with blank spaces. Hence you could execute a DoCmd.OpenQuery command on the line following the code you have now to load the spreadsheet into the table. The Replace syntax would be inserted in the criteria row of your query as follows:
Replace([YourMemoField] ,?, " ")
PDB
On re-reading your description of the problem, you already have Chinese text entered in your spreadsheet below the English text, leaving you basically two options:
1. Set unicode compression to yes and have the ? characters replaced by Chinese characters if you want Chinese characters left in the memo field.
2. Leave unicode set to no, and can create an update query on the memo field that will replace ? with blank spaces. Hence you could execute a DoCmd.OpenQuery command on the line following the code you have now to load the spreadsheet into the table. The Replace syntax would be inserted in the criteria row of your query as follows:
Replace([YourMemoField] ,?, " ")
PDB
ASKER
Again, there are valid question marks in the field that are used to denote the end of a question. Therefore, I can not replace all question marks.
Do you have a delimiter or identifier that identifies the start of the unicode string ....or maybe the first Chinese character is always the same? If not, it is my understanding that Excel uses the ChrW() function to support unicode. The ChrW() function accepts a number that represents the Unicode value of a character and returns that character string. Maybe you could check the Excel spreadsheet and see if this function is in a separate cell by itself in each row, and exclude it from the range being imported, or blank the contents of the unicode cells.
ASKER
Thanks for your thoughts, but I rather not hard-code the questions in the survey as they are subject to change.
Deb,
Come on now....you know I am not recommending that you blank out the questions........... You've heard of copy and paste special ....use your imagination....
Come on now....you know I am not recommending that you blank out the questions........... You've heard of copy and paste special ....use your imagination....
ASKER
Ha, puppydogbuddy!
It is because that I am using my imagination that I am looking for other alternatives.
ciao,
Deb
It is because that I am using my imagination that I am looking for other alternatives.
ciao,
Deb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Jeff,
<<<<Is there not some kind of identifier field inside the spreadsheet to distinguish language? An extra Column or something?>>>
That question was already asked (see my post of 6:03PM, excerpted below)
<<<Do you have a delimiter or identifier that identifies the start of the unicode string ....or maybe the first Chinese character is always the same? >>>>>
<<<<Is there not some kind of identifier field inside the spreadsheet to distinguish language? An extra Column or something?>>>
That question was already asked (see my post of 6:03PM, excerpted below)
<<<Do you have a delimiter or identifier that identifies the start of the unicode string ....or maybe the first Chinese character is always the same? >>>>>
ok
Hi Jeff,
I was just pointing out that I got no response to the same question. This post is all yours...... I am recusing myself.
PDB
I was just pointing out that I got no response to the same question. This post is all yours...... I am recusing myself.
PDB
ASKER
Heel PDB ~ no need to growl like your canine friend.
While Jeff's first question is ostensibly that same as yours, it is his second option is more in line with my request. FYI: The first character is not the same; this is a series of questions in a survey.
Jeff ~ I'll give your alternative a try in a bit and will get back to you ~ thanks.
Deb
While Jeff's first question is ostensibly that same as yours, it is his second option is more in line with my request. FYI: The first character is not the same; this is a series of questions in a survey.
Jeff ~ I'll give your alternative a try in a bit and will get back to you ~ thanks.
Deb
ASKER
Wow ~ that took longer than I thought. Thanks Jeff - you directed me to the right trail.
Best,
Deb
Best,
Deb