MS Access 2007: Problem when exporting extended characters

[Michael Juul Hansen]

Frequently we perform exports of Access queries into CSV files. On some
computers extended characters in the resulting export csv file are corrupted.
Does anyone know why and what to do? The Microsoft KB doesn't seem to give
any answers. Searching the Internet has shown that other Danish users have
the same problem

 

[Clifford Bass]

Hi Michael,

What version of Access? Is it completely up-to-date with the most
recent service pack and any subesquent patches? How about posting some
examples of the data as it is stored and as it shows when exported?

Clifford Bass

 

[Clifford Bass]

Hi Michael,

Also, what are the differences between the computers where it works
correctly and where it does not? Regional settings? Version of Windows?
Version of Access? Other?

Clifford Bass

 

[Michael Juul Hansen, Denmark]

Hi Clifford,

We use MS Access 2007. All installations are up to date complete with MSO
2007 SP2 (update.microsoft.com/microsoftupdate)

A few sample data being close to useless

Before export After export (csv file)

 

[Michael Juul Hansen, Denmark]

Hi Clifford,

All computers (PC's or servers) have the exact same

OS (i.e., XP Pro lastest SP)
Regional settings and
Access ver 2007 (SP2).

The one and only computer where export to csv works perfectly is actually
mine. The difference between my installation and all the rest is that I chose
to update my installation of MSO 2002. All other computers got their version
of MSO 2002 removed and MSO 2007 installed subsequently.

Importing the csv file into an Access 2007 database results in "corrupted"
data. Somehow the language driver settings are messed up in the export
process.

 

[Clifford Bass]

Hi Michael,

A possible thing to check, that probably will not make a difference.
On one of the other computers, go to Control Panel, Add or Remove Programs
and choose Microsoft Office ____ 2007. Click Change and choose Add or Remove
Features. Under the Office Shared Features, under International Support
make sure Universal Font is selected. Actually, if may make sense to make
sure all of the Access and the Office Shared Features are selected. And
maybe the Office Tools.

It might be useful to see the binary differences between the data
exported on your machine and one of the others. If you do not have a binary
viewer, download the free JujuEdit from
<http://www.jujusoft.com/software/edit/>. Are the files themselves being
saved as different types of files (ANSI, UNICODE, UTF-8, other?)? In
JujuEdit, View menu, Text Mode menu, will show as checked the mode of the
file.

Clifford Bass

 

[Michael Juul Hansen, Denmark]

Hi Clifford,

Viewing the binary data shows differences in the character sets used with
extended characters.

E.g. the letter æ (Ascii code 145) exported from my machine is assigned
the code E6 and exported from one of the other machines the code is 91.

Your effort is greatly appreciated.

 

[Clifford Bass]

Hi Michael,

I feel pretty sure now that this is a code page issue and not related
at all to Access, other than that Access will be impacted by the code page in
use. æ in say Windows 1252 - "Latin I" is indeed at E6 (see
<http://msdn.microsoft.com/en-us/goglobal/cc305145.aspx>). But in say OEM
858 - "Multilingual Latin I + Euro" it is at 91 (see
<http://msdn.microsoft.com/en-us/goglobal/cc305164.aspx>). The general page
for all code pages is
<http://msdn.microsoft.com/en-us/goglobal/bb964653.aspx>.

In a Command Prompt type "chcp" and then <Enter> It should tell you
the active code page.

At this point, it may be the blind leading the blind, but I would
suggest that you double-check the Regional and Language Options settings
again, especially on the Advanced tab, between your computer and the others.
Otherwise, if you have an IT support section, contact them.

Good Luck!

Clifford Bass

 

[Michael Juul Hansen, Denmark]

Hi Clifford,

I'll promptly forward your suggestions and guidance to our inhouse IT
support. We've tried for some time to solve the problem even with external IT
support and searching both the Microsoft Knowledge Base and the Internet as
well. Posting the question here was the latest and hopefully successful try.

Again thank you for your effort.

 

[Clifford Bass]

Hi Michael,

Okay, I am able to replicate the issue! It is indeed related to the
code page, at least as replicated by me. But it is also related to Access.
When you do a text export, and when you get to the export wizard, click on
the Advanced button. There you can specify the code page to use. If I use
"Western European (Windows)" I get "Hillerød". If I use "OEM Multilingual
Latin I" I get "Hiller›d". How does the default value on your computer
compare to the others? If need be, one of the Unicode code pages could be
used--that would hopefully result in the correct characters regardless of the
code page in use by the computer.

Hope that helps,

Clifford Bass

 

[Clifford Bass]

Hi Michael,

Glad to hear that it is no longer a problem! And glad to have helped.
I learned some stuff in the process also.

Clifford Bass