?
Solved

Not correct character encoding when importing csv-file into Windows Vista Contacts

Posted on 2009-04-07
10
Medium Priority
?
1,365 Views
Last Modified: 2012-06-27
I have saved an Excel-file containing contact information into a csv-file. But when I try to import the file using Import in Windows Contacts, the Swedish characters å, ä, ö, are not imported in the correct way. Instead I get a "box" in the place where å, ä or ö should have been. This is strange as my default system language is set to Swedish. I am running a Swedish Vista, Swedish Micrososft Office 2007... Is there a workaround for this problem?
0
Comment
Question by:DWOLTERS
[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
  • 5
  • 4
10 Comments
 

Expert Comment

by:byteharmony
ID: 24100750
did you update the language packs?? do that and tell me if it works. you will need the update for both vista and office
0
 

Author Comment

by:DWOLTERS
ID: 24121530
As I have Windows Update running all the time, including Office updates, and I do have Language Pack for Microsoft .Net Framework 3.5, SP 1 Swedish installed, I am not sure what else I can update? It seems that the contacts wants textfiles in Unicode (UTF-8) when importing contacts, BUT the default option from within MS Word and Excel is Westerneuropean (Windows) when exporting csv-files, it gets kind of clumsy!!!
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24121680
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:DWOLTERS
ID: 24121728
Thanks Stellan! Unfortunately my knowledge of VBA is not quiet at that level yet! My solution to this problem (so far) is to export the data from within Excel 2007 to a csv-file. Import the csv-file into Word 2007. Export the file again as a text-file BUT using the encoding that Windows contacts require (UTF-8)!! And then finally,  import the file into Windows Contacts! So if someone has a quicker way to do this, e.g. changing some hidden option in MS Excel 2007 Swedish version, I would be most grateful, even if this solution I found out myself only takes a minute or so longer to do!
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24121752
Can you provide a sample excel file that should be saved with UTF-8 encoding?

/Stellan
0
 

Author Comment

by:DWOLTERS
ID: 24121816
Sure! Here it is! And the same file after having been imported into Word and then exported using the option UTF-8 text encoding!
Example-file-export-from-Word.txt
Example-file-csv-swedish.txt
0
 
LVL 13

Accepted Solution

by:
StellanRosengren earned 1500 total points
ID: 24122830
Ok, I used the method from the link above in a macro that I would like you to test.
If you do not know how to implement the code you can download a workbook and save it in a folder that I suggest you set as a trusted location. To do that, you can go to Excel options via the Office button (top left) and then select Trust Center->Trust Center Settings->Trusted Locations.

I have assumed that you have 17 columns to save, and that your contact info starts in column A.

If the file is opened from a trusted location the macros are allowed to be executed.

Here is the link to the sample macro file:
http://stellanrosengren.com/upload/MacroToSaveCSVwithUTF8.xlsm

In the sample workbook there is a UserForm where you select which worksheet to convert. In the code snippet below the worksheet to convert must be the active sheet.

Kind regards,
Stellan

PS. I am living in Gothenburg. And you?

Sub ConvertAndSaveAsCSV()
    Dim wksC As Worksheet
    Dim oStream As Object
    Dim i As Integer
    Const iNUMCOLS As Integer = 17
    Dim sOut As String
    Dim lRow As Long
    Dim sName As String
    Dim iPos As Integer
    
    set wksC = ActiveSheet
 
    iPos = InStrRev(wksC.Parent.FullName, ".")
    sName = Left(wksC.Parent.FullName, iPos - 1) & Format(Now, "yymmdd-hhmmss")
    
    lRow = 1
    Do Until lRow > wksC.UsedRange.Rows.Count + wksC.UsedRange.Cells(1).Row - 1
        'First cell in row
        sOut = sOut & wksC.Cells(lRow, 1).Value
        'The rest of the row
        For i = 2 To iNUMCOLS
            sOut = sOut & ";" & wksC.Cells(lRow, i).Value
        Next i
        'linefeed at the end of the row
        sOut = sOut & vbCrLf
        'Increment row number
        lRow = lRow + 1
    Loop
    'Remove last linefeed
    sOut = Left(sOut, Len(sOut) - 2)
    
    Set oStream = CreateObject("ADODB.Stream")
    
    oStream.Type = 2
    oStream.Charset = "utf-8"
    oStream.Open
    oStream.writetext sOut
    
    oStream.savetofile sName & ".csv", 2
    
    Set oStream = Nothing
End Sub

Open in new window

0
 
LVL 13

Assisted Solution

by:StellanRosengren
StellanRosengren earned 1500 total points
ID: 24122848
0
 

Author Closing Comment

by:DWOLTERS
ID: 31567881
Well as I do not know enough VBA, it is impossible for me to evaluate the solution!
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24123285
I suppose that the macro worked to convert the xlsx file. Thanks for the points. But I
would like to know why it is only worth a grade B. What is missing in the solution?

From EE-help:
"
What's the right grade to give?
Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.

Giving a higher grade has no impact on your Available Points.
"


Kind regards,
Stellan
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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