Avatar of DMV
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
Microsoft Access

Avatar of undefined
Last Comment
DMV

8/22/2022 - Mon
puppydogbuddy

You need to go to the table importing to, place it in design view, highlight the memo field and set unicode compression to no.
DMV

ASKER
Hi puppydogbuddy.

Unicode compression is set to "No."  and yet the characters are still added to the table.

Deb
puppydogbuddy

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
DMV

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

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

ASKER
Thanks for your thoughts, but I rather not hard-code the questions in the survey as they are subject to change.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
puppydogbuddy

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

DMV

ASKER
Ha, puppydogbuddy!

It is because that I am using my imagination that I am looking for other alternatives.

ciao,
Deb
ASKER CERTIFIED SOLUTION
jefftwilley

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
puppydogbuddy

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? >>>>>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jefftwilley

ok
puppydogbuddy

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
DMV

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DMV

ASKER
Wow ~ that took longer than I thought.  Thanks Jeff - you directed me to the right trail.

Best,
Deb