Solved

registered and trademark symbols not importing correctly into Access from csv file

Posted on 2009-03-31
12
1,643 Views
Last Modified: 2012-05-06
I have a .csv file that has data in it that contains symbols such as "  © ®
When I either 1) link to the file 2) import the file, Access trashes those characters and replaces them with garbage characters.  How can I get Access to read these correctly?  

Access has no problem with these symbols when they are entered into an Access table, just in .csv files.  I can convert the csv to Excel, and Access can read them but 1) Excel ruins the zip codes and 2) I don't want my end users to have to spend the time converting a bunch of .csv files every two weeks.
0
Comment
Question by:OrganicTrade
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24035978
I think special characters such as the registered trademark symbol ® can be imported if the csv file is encoded in UTF-8 format.
0
 

Author Comment

by:OrganicTrade
ID: 24038409
How would I encode it that way, or what software could I use to do it?  It's coming from a mySQL database.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24039639
I am sure there are several ways you can do it.  How do you create your csv file.....do you create the csv file by executing the transferText import commands from Access?  If so, one way you can do it is to pass utf-8 for the charSet argment of the transferText method. Another way is to identify utf-8 in an import spec.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:OrganicTrade
ID: 24039730
The csv is created from a mysql database.  This database is in UTF-8 format from what I can tell.  Our web programmers set up some code (probably in php) on our site that we click on that creates the csv file for us.  I'll check into using and actually import spec in Access, I usually just use the import wizard or link to the csv files.
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 24040105
If you set up an import specification, you need to use it in the TransferText command.

To build a specification:
from the Table Tab in the Database window, right click, Link Table, select CSV, find the file, OK

An importing wizard is launched. In the bottom left is a button 'Advanced' - select this (this is where you identify utf-8)

A new dialog opens, which is pretty self explanatory - you can select columns, data types etc, then save as a specification. Take a note of the specification - lets say this is called "ABC Link Specification".

Now in your VBA use that as follows:

DoCmd.TransferText acImportDelim, "ABC Link Specification", "tblCSVload", "C:\DATABASE\FRONTIERIMPORT.CSV", False

0
 

Author Comment

by:OrganicTrade
ID: 24040947
doing the import and selecting UTF-8 in the specification eliminated those characters altogether on import.  It didn't work.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24041094
Does your access table have the unicode property code setting? Did you type utf-8 with quotes?  If that does not fix, try using utf-8 for the code page argument of the transferText method.
0
 

Author Comment

by:OrganicTrade
ID: 24051521
1)There is nothing that I can see in the table properties where I can tell it to have unicode properties.  I do know if you type into any of the tables, including this one, it accepts the " characters.
2) I didn't have to type it, Unicode UTF-8 was an option in a drop down box, both on the import specification, and in creating a macro for the transfer text command.  I think it's safe to assume that if I wrote the VBA code that did the same thing as the macro, the same thing would happen--It eliminates these characters now, instead of replacing them with garbage characters.

If you have any other suggestions, I'd be happy to try, although I might not be able to until the begining of next week.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24052367
Are you executing your macro from ms access as an import.?  It would be helpful if you posted it here so that I can see it. If you place your ms access table in design view, the last field property  is the unicode setting.  Check  that again.  In the meantime, I will see if I can find any additional info about the problem you are having.
0
 

Author Comment

by:OrganicTrade
ID: 24052603
attached copy of macro, and the field properties.  I have tried setting the Unicode compression to Yes and to No and neither helps.  btw-  I am using Access 2003
macro.JPG
tableprop.JPG
0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 500 total points
ID: 24055450
From my research , I am finding out that most of the unicode problems result from incorrect unicode encoding in the source csv file created by the sql server.   This incorrect encoding is encoding that does not meet the iso standard, and makes it impossible for Access to decode even if utf-8 s used for decoding.

So, my advice is to review the encoding in the csv source file and see if you can determine how the encoding needs to be changed so that it meets the iso standard, and be "understood" by utf-8.        
0
 

Author Comment

by:OrganicTrade
ID: 24110951
I found that if I went right into mysql and exported the file directly, then imported it using utf-8, it worked.  So you were right on both accounts, it needed to be imported as utf-8 AND something is up with the way the file is create.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question