Solved

wrong national (Norwegian) characters when importing dbf files into MS SQL Server

Posted on 2011-09-03
6
761 Views
Last Modified: 2016-02-23
Hi,

I have tried to buid an SSIS package importing dbf files into MS SQL server  database. I have problems with national characters. I use ADO Net data source with ODBC connector, I have defined DSN with uses Microsoft dbase driver. The resulting tables contain wrong charactes instead of national ones (Ø, Æ, Å). I have tried also FoxPro Ole DB driver. This one allows to force coding table ( which appears to be 1252) and results are correct, but it takes ages! to read files. I have also tried to read into MS Access 2010 database, errors are the same. The same errors when trying to open in excel.

I attach sample file.

PKPOLIKL.dbf.zip
0
Comment
Question by:kvilimas
  • 4
6 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
By default there are OEM encoding ("DataCodePage"="OEM", you should change it to ANSI (for Access 2010):

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Xbase]
"DataCodePage"="ANSI"

0
 

Author Comment

by:kvilimas
Comment Utility
Hi! Will this affect also how data are imported into sql server?
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
I've never tried to import DBF files directly to SQL, you can try.
If you link or import file to Access and then import from Access to SQL, all will be OK.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:als315
Comment Utility
Before Access 2007 this parameter  was in Jet path of registry:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Xbase]
"DataCodePage"="ANSI"
May be it will work for SQL.
You can try to search in registry "Xbase" and "DataCodePage". By default there is "OEM"
0
 

Expert Comment

by:Bbase
Comment Utility
Thank you for sharing this usefull information. One remark: changing "DataCodePage" to "ANSI" solves the problem for opening DBF files in Access 2010. Not in Excel 2010.

Is there a similar registry setting for Excel?
Many thanks.
Bbase
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Sorry Bbase, as I know Excel can work only with OEM DBFs.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now