Import Chinese Characters from XLS

[cdmmaui]

Hello Everyone,

I am trying to import Chinese characters from XLS and when I read the XLS cell, the value is ?? and not the correct text. I have used FW_Setunicode(.T.) in the application. In addition, I can see the correct values in XLS without a problem.

Can someone tell me what I am doing wrong?

Thank you,

[richard-service]

Hi cdmmaui,

Could you show a little import XLS source code?

[cdmmaui]

Hello,

Here is the code. Primary function is _LoadLanguages() below.

/***************************
#include "FiveWin.ch"

# DEFINE xlHAlignGeneral 1
# DEFINE xlHAlignLeft -4131
# DEFINE xlHAlignCenter -4108
# DEFINE xlHAlignRight -4152
# DEFINE xlHAlignJustify -4130
# DEFINE xlVAlignBottom -4107
# DEFINE xlVAlignTop -4160
# DEFINE xlVAlignCenter -4108
# DEFINE xlVAlignJustify -4130


REQUEST DBFCDX
REQUEST DBFFPT

function Main()

local oSay , ;
oSay2 , ;
cSay := "" , ;
cSay2 := '' , ;
oFont , ;
oMenu , ;
oIcon

IF PCOUNT()<1
cDebug := ""
ENDIF

// Init...
lAds := .F.
Pbuild := "20190205-1634"
cRdd1 := "DBFCDX"
cString := "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
lDebug := IF(EMPTY(cDebug),.F.,.T.)
cRoot := ""
cTitle := "CDM Web Freight Data Integration"
xPROVIDER := "SQLOLEDB"
xSOURCE := "x"
xDATABASE := "x"
xUSERID := "x"
xPASSWORD := "x"

// Init...
SET DATE FORMAT TO 'mm/dd/yyyy'
SET EPOCH TO 1980
SET EXCLUSIVE OFF
SET DELETED ON
SET DECIMALS TO 2

// Unicode
FW_SetUnicode(.T.)

// RDD...
RddSetDefault( cRdd1 )

PUBLIC cEol := CHR(13) + CHR(10)

DEFINE ICON oIcon RESOURCE "FRT"
DEFINE FONT oFont NAME GetSysFont() SIZE 0,-12 BOLD

MENU oMenu 2007
MENUITEM "&Functions"
MENU
MENUITEM "&A. Load Languages" ACTION _LoadLanguages( @oSay, @oSay2 )
SEPARATOR
ENDMENU

MENUITEM "&Exit" ACTION oWnd:End()
ENDMENU

DEFINE WINDOW oWnd TITLE "CDM Web Freight - Application Tools" ICON oIcon ;
MENU oMenu

@01,02 SAY oSay VAR cSay FONT oFont OF oWnd COLOR CLR_BLUE,CLR_WHITE SIZE 500,25

@03,02 SAY oSay2 VAR cSay2 FONT oFont OF oWnd COLOR CLR_BLUE,CLR_WHITE SIZE 500,25

SET MESSAGE OF oWnd TO "(C) 1988-2019 - CDM Software Solutions, Inc. - 281-298-8880 - www.cdmsoft.com " + Pbuild ;
CLOCK KEYBOARD 2007

ACTIVATE WINDOW oWnd

RETURN nil

//----------------------------------------------------------------------------//
function _XlsRead( oSheet, nRow1, nCol1, cValType )
local xReturn

xReturn := oSheet:Cells( nRow1, nCol1 ):Value

IF (VALTYPE(xReturn)="U")
DO CASE
CASE cValType=="C"
xReturn := ""
CASE cValType=="D"
xReturn := CTOD( " / / " )
CASE cValType=="N"
xReturn := 0
CASE cValType=="L"
xReturn := .F.
ENDCASE
ENDIF

xReturn := _CdmType( xReturn, cValType )

RETURN (xReturn)

//-----------------------------------------------------------------------------
FUNCTION _CdmType( xValue, cValType )

local xReturn := xValue

IF (VALTYPE(xValue)<>cValType)
DO CASE
CASE cValType="C"
DO CASE
CASE (VALTYPE(xValue)="N")
xReturn := LTRIM(STR(xValue,20))
CASE (VALTYPE(xValue)="D")
xReturn := DTOC( xValue )
CASE (VALTYPE(xValue)="L")
xReturn := IF( xValue, "Y", "N" )
OTHERWISE
xReturn := ""
ENDCASE
IF UPPER(ALLTRIM(xReturn))="NULL"
xReturn := ""
ENDIF
xReturn := UPPER(xReturn)

CASE cValType="N"
DO CASE
CASE (VALTYPE(xValue)="C")
xReturn := VAL( xValue )
CASE (VALTYPE(xValue)="L")
xReturn := IF( xValue, 1, 0 )
OTHERWISE
xReturn := 0
ENDCASE

CASE cValType="D"
DO CASE
CASE (VALTYPE(xValue)="C")
xReturn := CTOD( xValue )
CASE (VALTYPE(xValue)="T")
xReturn := xValue
OTHERWISE
xReturn := CTOD( " / / " )
ENDCASE
ENDCASE
ELSE
IF VALTYPE(xReturn)="C"
xReturn := UPPER(xReturn)
ENDIF
ENDIF

RETURN (xReturn)

//-----------------------------------------------------------------------------
FUNCTION _SqlBuild( cSqlField, cSqlValue, cSqlType, cSqlString1, cSqlString2 )
local cTemp := "", cChar, nX, cPrefix := ""

// String1....
IF ! EMPTY( cSqlString1 )
cSqlString1 += ","
ENDIF
cSqlString1 += cSqlField

// String2...
IF ! EMPTY( cSqlString2 )
cSqlString2 += ","
ENDIF
DO CASE
CASE cSqlType="N"
cSqlString2 += LTRIM(STR(cSqlValue,12))
CASE cSqlType="D"
cSqlString2 += LTRIM(STR(cSqlValue,14,3))
OTHERWISE
IF UPPER(cSqlValue)$'ZH-HANS'
cPrefix := 'N'
ENDIF
FOR nX=1 TO LEN( cSqlValue )
cChar := SUBSTR( cSqlValue, nX, 1 )
IF cChar="'"
cChar := ""
ENDIF
cTemp += cChar
NEXT nX
cSqlString2 += cPrefix + "'" + ALLTRIM( cTemp ) + "'"
ENDCASE

RETURN (.T.)

//-----------------------------------------------------------------------------
FUNCTION _LoadLanguages( oSay, oSay2 )

local cModule := 'Languages' , ;
oSql , ;
cSql := "" , ;
cSqlIns := "" , ;
dDateChk := DATE()-365 , ;
nAdded := 0 , ;
nTotrec := 0 , ;
nError := 0 , ;
oExcel, oSheet , ;
nX1 := 0 , ;
nX2 := 0 , ;
nMax := 65000 , ;
lCont := .F. , ;
lShow := .T. , ;
nUpdated := 0 , ;
lSource := .F. , ;
cSource := "language.xlsx" , ;
cDrive := DISKNAME() + ":\" + CURDIR( DISKNAME() ) + "\" , ;
cTitle := "Lanugages" , ;
nBad := 0

// Validate XLS...
IF (! FILE( cDrive + cSource ))
MsgInfo( ALLTRIM( cDrive + cSource ) + " could not be located.", cTitle )
RETURN (.F.)
ENDIF

TRY
oExcel := GetActiveObject( "Excel.Application" )
CATCH
TRY
oExcel := CreateObject( "Excel.Application" )
CATCH
Alert( "Cannot Connect to Excel. [" + Ole2TxtError()+ "]" )
RETURN (.T.)
END
END

// Open XLS...
TRY
oExcel:WorkBooks:Open( cDrive + cSource )
oSheet = oExcel:ActiveSheet
CATCH
Alert( "Error Reading XLS: " + cDrive + cSource + " - [" + Ole2TxtError()+ "]" )
RETURN (.F.)
END

TRY
oSql:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait("It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
RETURN (.F.)
END

// Set...
oSql:CursorType := 1 // opendkeyset
oSql:CursorLocation := 3 // local cache
oSql:LockType := 3 // lock opportunistic


FOR nX1=2 TO nMax

// Message...
oSay:SetText( "Processing XLS Row " + LTRIM(TRANSF( nX1, "999,999" ) ) )
Sysrefresh()

nId := _XlsRead( @oSheet, nX1, 1, "N" )
cSourceLang := _XlsRead( @oSheet, nX1, 2, "C" )
cSourceText := _XlsRead( @oSheet, nX1, 3, "C" )
cDestLang := _XlsRead( @oSheet, nX1, 4, "C" )
cDestText := _XlsRead( @oSheet, nX1, 5, "C" )

IF (nId<1) .AND. (EMPTY(cSourceLang)) .AND. (EMPTY(cSourceText))
nBad++
ENDIF
IF (nBad>10)
EXIT
ENDIF
nTotRec++

IF MsgNoYes(cSourceText + cEol + cDestText)
EXIT
ENDIF

// Insert...
IF (nId>0) .AND. (!EMPTY(cSourceLang)) .AND. (!EMPTY(cSourceText))
nBad := 0
lSql := .T.
cSql2a := "INSERT INTO webfreight"
cSql2b := ""
cSql2c := ""

_SqlBuild( "id" , nId , "N", @cSql2b, @cSql2c )

_SqlBuild( "iso" , cSourceLang, "C", @cSql2b, @cSql2c )

_SqlBuild( "text" , cSourceText, "C", @cSql2b, @cSql2c )

// SQL....
cSqlIns := cSql2a + " (" + cSql2b + ") VALUES (" + cSql2c + ")"

TRY
oSql:Open( cSqlIns, 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xDATABASE+';User Id='+xUSERID+';Password='+xPASSWORD )
nAdded++
CATCH oError
MsgAlert( "SQL Insert Error" + cEol + cSqlIns + cEol + oError:Description, cTitle )
//lSql := .F.
nError++
END

IF ! EMPTY(cDestText)
nBad := 0
lSql := .T.
cSql2a := "INSERT INTO webfreight"
cSql2b := ""
cSql2c := ""

_SqlBuild( "id" , nId , "N", @cSql2b, @cSql2c )

_SqlBuild( "iso" , cDestLang , "C", @cSql2b, @cSql2c )

_SqlBuild( "text" , cDestText , "C", @cSql2b, @cSql2c )

// SQL....
cSqlIns := cSql2a + " (" + cSql2b + ") VALUES (" + cSql2c + ")"

TRY
oSql:Open( cSqlIns, 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xDATABASE+';User Id='+xUSERID+';Password='+xPASSWORD )
nAdded++
CATCH oError
MsgAlert( "SQL Insert Error" + cEol + cSqlIns + cEol + oError:Description, cTitle )
//lSql := .F.
nError++
END
ENDIF
ENDIF
Sysrefresh()
NEXT nX1
oExcel:Quit()

MsgAlert( cModule + ' Complete.' + cEol + LTRIM(TRANSF(nAdded,'999,999,999')) + ' records added out of ' + LTRIM(TRANSF(nTotRec,'999,999,999')) )

RETURN (.T.)

/***************************

[nageswaragunupudi]

Hello Everyone,

I am trying to import Chinese characters from XLS and when I read the XLS cell, the value is ?? and not the correct text. I have used FW_Setunicode(.T.) in the application. In addition, I can see the correct values in XLS without a problem.

Can someone tell me what I am doing wrong?

Thank you,

Harbour works well if you also set HB_CDPSELECT( "UTF8" )

[cdmmaui]

Hi Rao,

Thank you so much, that resolved it!