Leer archivo Excel / lectura lenta

[jparada]

Hola,
Tengo un comportamiento extraño al leer un archivo Excel, las primeras 999 filas el programa las lee a una velocidad "normal"/rápida, cuando llega a la fila 1000 comienza a leer muy lento.

Este es el código que estoy utilizando, a ver si alguien puede ver algo "raro" y comentas.

Code: Select all

#include "hmg.ch"

#xcommand TRY  => BEGIN SEQUENCE WITH {| oErr | Break( oErr ) }
#xcommand CATCH [<!oErr!>] => RECOVER [USING <oErr>] <-oErr->
#xcommand FINALLY => ALWAYS

FUNCTION Main()  
  LOCAL lastRow
  LOCAL oErr, oErrRS
  LOCAL oCnn, oRs
  LOCAL oExcel, cQry
  LOCAL oWorkBook, oHoja, nFilas
  LOCAL i
  LOCAL codigo, descripcion, costo, precio  
  LOCAL cTime :=  Time()
  
  IF ( oCnn := win_OleCreateObject( "ADODB.Connection" ) ) == NIL
    ? 'No es posible crear objeto conexión, ' + win_OleErrorText()
    RETURN NIL
  ENDIF

  TRY    
    oCnn:Open( "Provider=SQLNCLI11;Server=IP\INSTANCIA;Database=database;Uid=user;Pwd=pwd;" )
  CATCH oErr
    ? "Error al intentar conectar a la base de datos " +  e"\n" + hb_StrToUtf8(oErr:Description)
    RETURN NIL
  END

  //RecordSet
  //oRs := win_OleCreateObject( "ADODB.Recordset" )
  //

  IF ( oExcel := win_oleCreateObject( "Excel.Application" ) ) == NIL
    ? 'Error: Microsoft Excel no está disponible, ', win_OleErrorText()
    RETURN NIL
  ENDIF

  oExcel:Visible := .F.
  oExcel:DisplayAlerts := .F.
  oWorkBook := oExcel:WorkBooks:Open( "C:\actualizar_precios\hmg\lista_precios_2020.xlsx" )  
  
  oExcel:Sheets(1):Select()
  oHoja  := oExcel:ActiveSheet
  nFilas := oHoja:UsedRange:Rows:Count()
  
  QOut( "Número total de filas: " + Alltrim( hb_NtoS( nFilas ) ) )  

  FOR i := 2 TO nFilas
    codigo      := Cell2Chr( oHoja:Cells(i,1):Value )
    descripcion := Cell2Chr( oHoja:Cells(i,2):Value )
    costo       := Cell2Val( oHoja:Cells(i,3):Value )
    precio      := Cell2Val( oHoja:Cells(i,4):Value )

    QOut( " " + CRLF ) 
    
    QOut( 'Registro ' + Alltrim( hb_NtoS(i) ) )
    QOut( hb_utf8ToStr('Código: ') + codigo )
    QOut( hb_utf8ToStr('Descripción: ') + descripcion )
    QOut( "Costo: " + Alltrim( hb_NtoS( costo ) ) )
    QOut( "Precio: " + Alltrim( hb_NtoS( precio ) ) )        
    

    cQry := "UPDATE admProductos SET " + ;
            "precio1 = " + DataToSQL(precio) + ", " + ;
            "precio2 = " + DataToSQL(precio) + ", " + ;
            "precio3 = " + DataToSQL(precio) + ", " + ;
            "precio4 = " + DataToSQL(precio) + ", " + ;
            "precio5 = " + DataToSQL(costo) + ", " + ;
            "precio6 = " + DataToSQL(precio) + ", " + ;
            "precio7 = " + DataToSQL(precio) + ", " + ;
            "precio8 = " + DataToSQL(precio) + ", " + ;
            "precio9 = " + DataToSQL(precio) + ", " + ;
            "precio10 = " + DataToSQL(precio)
    
    QOut( cQry )
    //Inkey(0)
    
    /*
    TRY
      oCnn:Execute( cQry )
    CATCH oErrRS
      ? "Error al actualizar datos en la tabla" +  e"\n" + hb_StrToUtf8(oErrRS:Description)
      RETURN .F.
    END
    */

  NEXT

  oExcel:WorkBooks:Close() // CIERRA LIBRO ACTIVO
  oExcel:Quit() // CIERRA EXCEL
      
  ?
  ? "Todo OK, Tiempo transcurrido : " + ElapTime( cTime, time() )
  ?

  //cerrar conexión y recordset (si es que utilizamos RecordSet)
  oCnn:Close()
  oCnn := NIL

  // RecordSet
  //oRS:Close()
  //oRS := NIL

RETURN NIL
/*******/

/*
* Cell2Val
*/
FUNCTION Cell2Val( xCadena )
  LOCAL nNumero := 0
  LOCAL cTipo   := VALTYPE(xCadena)
  
  DO CASE
    CASE cTipo = 'C'
      nNumero := VAL( xCadena )
    CASE cTipo = 'N'
      nNumero := xCadena
    CASE cTipo = 'U'
      nNumero := 0
    OTHERWISE
      MsgInfo('Error : '+cTipo)
  ENDCASE
  
RETURN( nNumero )
/*******/
  
/*
* Cell2Chr
*/
FUNCTION Cell2Chr( xCadena )
  LOCAL cCadena := ' '
  
  IF ! VALTYPE( xCadena ) = 'U'
    DO CASE
      CASE VALTYPE(xCadena) = 'C'
        cCadena := xCadena
      CASE VALTYPE(xCadena) = 'N'
        cCadena := VAL(STR(INT(xCadena)))
      CASE VALTYPE(xCadena) = 'D'
        cCadena := DTOC(xCadena)
      ENDCASE
  ENDIF
  
RETURN( cCadena )
/*******/

/*
* DataToSQL
*/
FUNCTION DataToSQL( xField )
  LOCAL cType, result := 'NULL'
  
  cType := ValType( xField )
  
  IF cType == "C" .OR. cType == "M"
    IF !Empty( xField ) 
      result := "'" + xField + "'"
    ENDIF
    
    IF xField == "NIL"
      result := 'NULL'
    ENDIF

  ELSEIF cType == "D" .AND. !Empty( xField )
    // formato fecha ISO 8601 YYYYMMDD
    result := "'" + StrZero( Year( xField ), 4 )
    result += StrZero( Month( xField ), 2 )
    result += StrZero( Day( xField ), 2 ) + "'"

  ELSEIF cType == "N"
    result := hb_ntos( xField )
    result := "'" + result + "'"

  ELSEIF cType == "L"
    result := IIF( xField, "'t'", "'f'" )
  ENDIF

RETURN result
/*******/

Agradezco su ayuda.

Saludos,
Javier

[AUGE_OHR]

hi,

as i see you "transfer" data direct from ADO to EXCEL.

have you try to read Data and store it to Array and assign "hole" Array to Excel

Code: Select all

   // save Errorblock
   bSaveError := ErrorBlock()
   // set new Errorblock
   ErrorBlock( {|e| Break(e)} )

   BEGIN SEQUENCE
      // Start Excel
      oExcel := CreateObject( "Excel.Application" )
      IF NIL == oExcel
         // Excel.Application could not be created.
         MSGBOX( "Excel Verbindung konnte nicht erstellt werden" )
         BREAK
      ELSE
         oExcel:visible := .T.                   // visible
      // If there is a problem, don't let excel pop up messages
         oExcel:Application:DisplayAlerts := .F.
         oExcel:Application:Workbooks:open(cPATH+cFILE)

      // Make the first one active
         oWorkBook := oExcel:activeWorkBook
         oExcel:Application:Worksheets(1):activate()

      // Speed things up by creating an object containing the cells
         oSheet := oExcel:Worksheets(1):cells

      // does NOT work ???
      // oSheet:Select
      // oSheet:usedRange:Select

      // but this work
         oWorkBook:workSheets(1):usedRange:Select

         numRows    := oWorkBook:workSheets(1):usedRange:Rows:Count
         numColumns := oWorkBook:workSheets(1):usedRange:Columns:Count

      // build Array with this size
         FOR i := 1 TO numRows
            AADD(aExcel,ARRAY(numColumns))
         NEXT
         
       //  assign hole Array to Excel
         cEnde := ZAHL2CHR(numColumns)
         aExcel := oSheet:range( "A1:"+cEnde+LTRIM(STR(numRows)) ):value

      // Save the workbook
      // If you don't put a path, the file will be somewhere in
      // My Documents or some other Excel default directory
        oWorkBook:saveas(ZPATH+cFILE)

      // Quit Excel
         oExcel:Quit()

Code: Select all

FUNCTION ZAHL2CHR(numColumns)
LOCAL nMal
LOCAL cEnde

   IF numColumns > 26
      nMal  := INT(numColumns/26)
      cEnde := CHR(nMal+64)+CHR((numColumns-(nMal*26))+64)
   ELSE
      cEnde := CHR(numColumns+64)
   ENDIF

RETURN cEnde

[jparada]

Hi,
What I try is to read an Excel file and update an MS-SQL Server table via ADO, this way that you show me I think I can't apply it in the way that I require it, can I?

I appreciate your help.

Regards,
Javier

[AUGE_OHR]

What I try is to read an Excel file and update an MS-SQL Server table via ADO, this way that you show me I think I can't apply it in the way that I require it, can I?

uuuuuuuups ... sorry my Way was just other Direction.

---

when use SQL and INSERT there is a Trick to "speed up".
if you have a INSERT Statement normal it will "just" have "some Byte" of a Record.

now normal MTU Packet is 1500 so you can put more that 1 x INSERT Statement into a Query

Code: Select all

   cQuery := "INSERT ..."
   cQuery += ";"
   cQuery += "INSERT ..."
   cQuery += ";"
   cQuery += "INSERT ..."

---

"Server" most have a "Cache". you might hit limit so have a look on Server side how Resource look like

---

i recommend to read Excel Sheet to Array, close Excel and than call ADO.
you can use Code above which read Excel Sheet into Array

Code: Select all

aExcel := oSheet:range( "A1:"+cEnde+LTRIM(STR(numRows)) ):value

[jparada]

Hi,

i recommend to read Excel Sheet to Array, close Excel and than call ADO.
you can use Code above which read Excel Sheet into Array

Code: Select all

aExcel := oSheet:range( "A1:"+cEnde+LTRIM(STR(numRows)) ):value

Hi,
I get compilation error on this line: oWorkBook:WorkSheets(1):usedRange:Select

actualiza.prg(51) Error E0020 Incomplete statement or unbalanced delimiters
1 error

How can i solve it?.

And I'm trying to read the contents of the array like this:

Code: Select all

FOR i := 1 TO numRows
    AADD( aExcel, Array(numColumns) )
NEXT
  
cEnde := DataToChr(numColumns)  
aExcel := oSheet:Range( "A2:"+cEnde+LTrim(Str(numRows)) ):Value  

FOR j:=1 TO Len(aExcel)    
    codigo      := aExcel [J][1]
    descripcion := aExcel [J][2]
    costo       := aExcel [J][3]    
    precio      := aExcel [J][4]   

    ? 'Registro ' + Alltrim( Str(j) ) 
    ? hb_utf8ToStr('Código: ') + codigo 
    ? hb_utf8ToStr('Descripción: ') + descripcion
    ? 'Costo: ' + costo
    ? 'Precio: ' + precio
NEXT

And I don't get what I need, can you help here please.

I appreciate your help.

Regards,
Javier

[AUGE_OHR]

hi,

I get compilation error on this line: oWorkBook:WorkSheets(1):usedRange:Select

actualiza.prg(51) Error E0020 Incomplete statement or unbalanced delimiters
1 error

this line is to select Sheet and need for

Code: Select all

   numRows    := oWorkBook:workSheets(1):usedRange:Rows:Count
   numColumns := oWorkBook:workSheets(1):usedRange:Columns:Count

if you know numRows / numColumns you can skip that part

And I'm trying to read the contents of the array like this:

Code: Select all

    ? hb_utf8ToStr('Código: ') + codigo 
    ? hb_utf8ToStr('Descripción: ') + descripcion

And I don't get what I need, can you help here please.

not sure what your Problem is
you doing some UTF8 "translation" ... this i don't know

Question : why is ADO need

[jparada]

Hi,
Can you please tell me how to read the array that is generated...

In this way:

Code: Select all

FOR j:=1 to Len(aExcel)
    codigo      := aExcel [J][1]
    descripcion := aExcel [J][2]
    costo       := aExcel [J][3]
    precio      := aExcel [J][4]

    ? Valtype(codigo)
    ? Valtype(descripcion)
    ? Valtype(costo)
    ? Valtype(precio)

    ? 'Registro ' + Alltrim( Str(j) )
    ? hb_utf8ToStr('Código: ') + codigo
    ? hb_utf8ToStr('Descripción: ') + descripcion
    ? 'Costo: ' + costo
    ? 'Precio: ' + precio    
  NEXT

It doesn't read correctly and an error occurs.

img1.png (29.08 KiB) Viewed 3995 times

Sample Excel file

img2.png (23.42 KiB) Viewed 3995 times

The use of hb_utf8ToStr is to correctly display characters (Spanish) and nothing else

ADO because I am using MS-SQL Server database.

Regards,
Javier

[andyglezl]

Así a simple vista, como que estás tomando los datos de los 4 primeros registros...

Quizá deba ser:

Code: Select all

    codigo        := aExcel [1][J]
    descripcion := aExcel [2][J]
    costo          := aExcel [3][J]
    precio         := aExcel [4][J]

Y como Costo y Precio son numéricos, no los puedes concatenar, da ese error.

[AUGE_OHR]

Can you please tell me how to read the array that is generated...

Code: Select all

    ? Valtype(codigo)
    ? Valtype(descripcion)
    ? Valtype(costo)
    ? Valtype(precio)

as you see you got Type "C" for your Variable so you need VAL() for numeric.
if you just want to "?" it use hb_valToExp() -> String

have fun

[Tiger]

As andyglezl & AUGE_OHR said, you need to use following codes with array ...

codigo := Cell2Chr( aExcel [1][J])
descripcion := Cell2Chr(aExcel [2][J])
costo := Cell2Val(aExcel [3][J])
precio := Cell2Val(aExcel [4][J])