Solved

Determine if Unicode reading characters in Excel file

Posted on 2011-03-22
2
535 Views
Last Modified: 2012-05-11
I have a problem when I open data from an Excel file to a DBF file in FoxPro.

I want to create a VB.NET application to see if any Unicode characters exist in an excel file before I convert to a dbf.  (Stop using dbf is not an option)

I know I can determine the character set using .NET, but I dont have time to research.  Anyone have a solution or sample code using "System.Text.Encoding"

Basically, I want to: read an excel file using .NET.  I would then evaluate each character in the excel file.  If the character is not a match for Ascii character, or is Unicode character set, then I would indicate what character was found, in what column, in what row, and exactly what character #.  This record could then be extracted by someone before it is converted to dbf.
0
Comment
Question by:handyjay
2 Comments
 
LVL 11

Accepted Solution

by:
lenordiste earned 500 total points
ID: 35193242
here s a nice discussion on how to do this. Look for the first answer:
http://stackoverflow.com/questions/4459571/how-to-recognize-if-a-string-contains-unicode-chars

also, a similar topic has been already discussed in EE:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22513239.html
0
 

Author Comment

by:handyjay
ID: 35193535
Thanks, in the meantime I wrote something similar, which may or maynot be as effective.
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        Dim lerr As New List(Of String)
        Dim dt As Data.DataTable = loadDtfromExel()

        Dim rowId As Integer = 1
        For Each row As Data.DataRow In dt.Rows
            Dim sSearch As String = row("Name").ToString
            ' Create two different encodings.
            Dim ascii As System.Text.Encoding = System.Text.Encoding.ASCII
            Dim unicode As System.Text.Encoding = System.Text.Encoding.Unicode

            ' Convert the string into a byte array.
            Dim unicodeBytes As Byte() = unicode.GetBytes(sSearch)

            ' Perform the conversion from one encoding to the other.
            Dim asciiBytes As Byte() = System.Text.Encoding.Convert(unicode, ascii, unicodeBytes)

            ' Convert the new byte array into a char array and then into a string.
            Dim asciiChars(ascii.GetCharCount(asciiBytes, 0, asciiBytes.Length) - 1) As Char
            ascii.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0)
            Dim asciiString As New String(asciiChars)

            ' Display the strings created before and after the conversion.
            Console.WriteLine("Original string: {0}", sSearch)
            Console.WriteLine("Ascii converted string: {0}", asciiString)

            For Each c As Char In asciiString
                If c = "?" Then
                    lerr.Add("RowID: " & rowId.ToString & "  Name: " & sSearch)
                End If

            Next

            rowId += 1
        Next

    End Sub

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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