how do I display spanish characters in a UK database

I have a client in London, just purchased a Spanish company and they need to be able to import the customers addresses and then display the accented characters properly.

I can see the data in Excel is OK but when I import and look in Nav I get alternate characters.

Any help here please as I am also going to import from other countries soon.

TIA

Colin

Hi Colin,

I don’t think the issue will be with the actual display of Characters, all the characters needed (ñ º á, é, í, ó, ú ü ) should be available in CP 437 or 850. The issue is going to be sorting and case conversion. Specifically I am not sure how the double consonants are sorted in Spanish ( ch , rr , ll ) but I think they use normal Latin sorting now-a-days.

I think where your current issue lies is in how you are getting the data. I would assume (since you mention Excel) that the data is probably in Unicode, and probably converting to DO using the wrong code page. Unfortunately I have Excel 2007, but in earlier versions of Excel, there was the possibility to export text and choose the Code Page you wanted, and then get the text in the correct format. Unfortunately Excel 2007 with its rather pathetic ribbons makes it impossible to find that option, even if it does exist somewhere. I think it used to be File → Export → Text → MSDOS or something similar.

If what you need to do is just have the text appear correct for printing purposes then once you work out the code page it shoudl all work, but you may still have issues using it in NAV, for example if you enter a name in a customer card, then the Search Name will look funny.

Lets hope someone from Spain (or Portugal [;)] ) can help us solve this.

Also take a look at these two blogs,

Nuno has developed a tool that makes the conversion simpler

http://dynamicsuser.net/blogs/nunomaia/archive/2007/10/31/nav-codepage-encoding-converter-2.aspx

http://dynamicsuser.net/blogs/nunomaia/archive/2007/03/15/navision-and-unicode.aspx

Off topic: export as text in Excel 2007: you have to use ‘Save As’ for this. Click the funky Office button, Save as, click on ‘other formats’, in the ‘Save as Type’ list you’ll find unicode text. Much easier than previous versions actually.

and then when you click on the tools button, and select ‘Web Options’, you can select a different code page on the encoding tab. So much for user friendliness :wink:

The display is the problem, for example:

C/ Romeria del RocÝo 7

The i with an accent I can see in a text editor (textpad) as í but as Ý in Navision. The “Nuno & Unicode” discussion is beyond me and looks too complex for what I am trying to understand.

I have 2 choices at the moment I think, I can change all the í to i (and all the other characters you mentioned to an “international” character) or find a way to display them as is.

I would be surprised if the Local Spanish version has to display the ñ º á, é, í, ó, ú ü characters as anything else but how they appear on the Spanish keyboard.

My worry is I will have people in Barcelona connecting to the London server and typing gibberish when they needed the ñ º á, é, í, ó, ú ü characters. They of course will have the Spanish keyboards.

Can someone confirm what existing Spanish users get displayed?

When I copy paste ñ º á, é, í, ó, ú ü into a text field in NAV it works fine. The issue I think is in the conversion from Unicode to MS-DOS code pages.

The save as has always been there, but it give you the option of MSDOS, Macintosh and a couple of others. but in earlier versions of Excel you could export as text, and select the code page that you needed.

This was critical to me back when I was translating NAV in multiple languages that had different code pages. With a US system, you probably have code page 437 so the default would work OK, but once you get Unicode excel files, its just not that easy.

Nuno’s tool solves this, but as Colin says, it is somewhat technical to use.

Thanks David, I also can copy and paste and get the correct result.

I will try another tack, thanks for your help.

Colin

Yes that’s what I guessed, which means the issue is not NAV, but Excel. I think the issue maybe then telling Excel which code page you are using. So first determine what code page your are using.

Open DOS prompt (go to RUN and type command, or in accesories COMMAND PROMPT type CHCP

this will tell you what code page you are currently using (most likely 437).

Now try typing

C:> CHCP 850

Now I am not sure if excel will read this, but if so then your expert probably will now work.

In principle the issue I think is that Excel is exprting as Code page 437 and you need it to use 850.

i had such experience with arabic font, i usually solve it in 2 ways:

  • in excel 2007, press file save as “ms dos csv comma delimited” and import via dataport

or use the NAV tool universal excel importer, it keeps the same format as copy paste

With the csv file open in Textpad, tools/convert to DOS, then import to Nav…perfect.

Off topic, as regards Excel, I prefer Open Office as it has the option to give you a better csv file with an option of delimiters to use, not sure if Excel 2007 does that?

2,“GL/CM”,“Mr”,“D. Peter”,“Pearson”,“Cerros del Aguila 285”, etc…

This is helpful when the data contains commas, although I normally search and replace all " with # and then change them back once imported.

The default was 850 so no problem there, Excel was not at fault I expect, in any case, now I have the methods of saving in DOS mode it seems the problem has gone away.

Thanks all,

Colin

I have arrived late to this topic, but I want to complete only one thing.

Microsoft Excel don’t have the option to control delimiter chars and string separator while exporting to MS-DOS. When you want to control those options use Microsoft Access, this software allows you to control that.

Good tip thanks.

PS does it also allow you to chose Code Page at export time? I am wondering how long ago they removed that option from Excel, and it may have been further back than just 2003.

Just to correct myself. Microsoft Access 2003 had the option of opening Excel files and exporting to CSV by controlling string delimiters and field separator controls. That option has removed from Access 2007. The only option that I found has to export has CSV MS-DOS. Codepage control and file delimiters where removed. I guess somebody thought that option was only used by Navision consultants…