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,
Hi cdmmaui,
Could you show a little import XLS source code?
Hello,
Here is the code. Primary function is _LoadLanguages() below.
/*****
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.)
/*****
cdmmaui wrote: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,
Hi Rao,
Thank you so much, that resolved it!
