topic-29965

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Load FROM EXCEL
Posted: Tue Jan 13, 2015 12:12 PM

Hi,

Please some help and sample
How to load ALL data or PART of data from Excel file into Array (or .DBF)

Best regardsm

ali

Posts: 23

Joined: Wed May 07, 2008 02:50 PM

Re: Load FROM EXCEL
Posted: Tue Jan 13, 2015 12:57 PM

Hi!

I have only a sample to import CSV to DBF.

Code (fw): Select all Collapse

function mkdbf()

    if isDir(  ".\dataimport" ) = .f.
         lMKDir( ".\dataimport" )
   endif
   
   if file( ".\dataimport\import.csv") = .f.
         msginfo("Import.csv fehlt" + CRLF + "Programm wird beendet")
   endif
   
   
    if file( ".\dataimport\import.dbf") = .T.
         ferase(".\dataimport\import.dbf")
   endif

DbCreate(".\dataimport\import",;
    { {"F1", "C",   50,   0} ,;
      {"F2", "C",   50,   0} ,;
        {"F3", "C",   50,   0} ,;
        {"F4", "C",   50,   0} ,;
        {"F5", "C",   50,   0} ,;
        {"F6", "C",   50,   0} ,;
        {"F7", "C",   50,   0} ,;
        {"F8", "C",   50,   0} ,;
        {"F9", "C",   50,   0} ,;
        {"F10", "C",  50,   0} ,;
        {"F11", "C",  50,   0} ,;
        {"F12", "C",  50,   0} ,;
        {"F13", "C",  50,   0} ,;
        {"F14", "C",  50,   0} ,;
        {"F15", "C",  50,   0} ,;
        {"F16", "C",  50,   0} ,;
        {"F17", "C",  50,   0} ,;
        {"F18", "C",  50,   0} ,;
        {"F19", "C",  50,   0} ,;
        {"F20", "C",  50,   0} ,;
        {"F21", "C",  50,   0} ,;
        {"F22", "C",  50,   0} ,;
        {"F23", "C",  50,   0} ,;
        {"F24", "C",  50,   0} ,;
        {"F25", "C",  50,   0} ,;
        {"F26", "C",  50,   0} ,;
        {"F27", "C",  50,   0} ,;
        {"F28", "C",  50,   0} ,;
        {"F29", "C",  50,   0} ,;
        {"F30", "C",  50,   0} ,;
        {"F31", "C",  50,   0} ,;
        {"F32", "C",  50,   0} ,;
        {"F33", "C",  50,   0} ,;
        {"F34", "C",  50,   0} ,;
        {"F35", "C",  50,   0} ,;
        {"F36", "C",  50,   0} ,;
        {"F37", "C",  50,   0} ,;
        {"F38", "C",  50,   0} ,;
        {"F39", "C",  50,   0} ,;
        {"F40", "C",  50,   0} ,;
        {"F41", "C",  50,   0} ,;
        {"F42", "C",  50,   0} ,;
        {"F43", "C",  50,   0} ,;
        {"F44", "C",  50,   0} ,;
        {"F45", "C",  50,   0} ,;
        {"F46", "C",  50,   0} ,;
        {"F47", "C",  50,   0} ,;
        {"F48", "C",  50,   0} ,;
        {"F49", "C",  50,   0} ,;
        {"F50", "C",  50,   0} ,;
        {"F51", "C",  50,   0} ,;
        {"F52", "C",  50,   0} ,;
        {"F53", "C",  50,   0} ,;
        {"F54", "C",  50,   0} ,;
        {"F55", "C",  50,   0} ,;
        {"F56", "C",  50,   0} ,;
        {"F57", "C",  50,   0} ,;
        {"F58", "C",  50,   0} ,;
        {"F59", "C",  50,   0} ,;
        {"F60", "C",  50,   0} ,;
        {"F61", "C",  50,   0} ,;
        {"F62", "C",  50,   0} ,;
        {"F63", "C",  50,   0} ,;
        {"F64", "C",  50,   0} ,;
        {"F65", "C",  50,   0} ,;
        {"F66", "C",  50,   0} ,;
        {"F67", "C",  50,   0} ,;
        {"F68", "C",  50,   0} ,;
        {"F69", "C",  50,   0} ,;
        {"F70", "C",  50,   0} ,;
        {"F71", "C",  50,   0} ,;
        {"F72", "C",  50,   0} ,;
        {"F73", "C",  50,   0} ,;
        {"F74", "C",  50,   0} ,;
        {"F75", "C",  50,   0} ,;
        {"F76", "C",  50,   0} ,;
        {"F77", "C",  50,   0} ,;
        {"F78", "C",  50,   0} ,;
        {"F79", "C",  50,   0} ,;
        {"F80", "C",  50,   0} ,;
        {"F81", "C",  50,   0} ,;
        {"F82", "C",  50,   0} ,;
        {"F83", "C",  50,   0} ,;
        {"F84", "C",  50,   0} ,;
        {"F85", "C",  50,   0} ,;
        {"F86", "C",  50,   0} ,;
        {"F87", "C",  50,   0} ,;
        {"F88", "C",  50,   0} ,;
        {"F89", "C",  50,   0} ,;
        {"F90", "C",  50,   0} ,;
        {"F91", "C",  50,   0} ,;
        {"F92", "C",  50,   0} ,;
        {"F93", "C",  50,   0} ,;
        {"F94", "C",  50,   0} ,;
        {"F95", "C",  50,   0} ,;
        {"F96", "C",  50,   0} ,;
        {"F97", "C",  50,   0} ,;
        {"F98", "C",  50,   0} ,;
        {"F99", "C",  50,   0} ,;
        {"F100", "C",  50,   0} ,;
        {"F101", "C",   50,   0} ,;
      {"F102", "C",   50,   0} ,;
        {"F103", "C",   50,   0} ,;
        {"F104", "C",   50,   0} ,;
        {"F105", "C",   50,   0} ,;
        {"F106", "C",   50,   0} ,;
        {"F107", "C",   50,   0} ,;
        {"F108", "C",   50,   0} ,;
        {"F109", "C",   50,   0} ,;
        {"F110", "C",  50,   0} ,;
        {"F111", "C",  50,   0} ,;
        {"F112", "C",  50,   0} ,;
        {"F113", "C",  50,   0} ,;
        {"F114", "C",  50,   0} ,;
        {"F115", "C",  50,   0} ,;
        {"F116", "C",  50,   0} ,;
        {"F117", "C",  50,   0} ,;
        {"F118", "C",  50,   0} ,;
        {"F119", "C",  50,   0} ,;
        {"F120", "C",  50,   0} ,;
        {"F121", "C",  50,   0} ,;
        {"F122", "C",  50,   0} ,;
        {"F123", "C",  50,   0} ,;
        {"F124", "C",  50,   0} ,;
        {"F125", "C",  50,   0} ,;
        {"F126", "C",  50,   0} ,;
        {"F127", "C",  50,   0} ,;
        {"F128", "C",  50,   0} ,;
        {"F129", "C",  50,   0} ,;
        {"F130", "C",  50,   0} ,;
        {"F131", "C",  50,   0} ,;
        {"F132", "C",  50,   0} ,;
        {"F133", "C",  50,   0} ,;
        {"F134", "C",  50,   0} ,;
        {"F135", "C",  50,   0} ,;
        {"F136", "C",  50,   0} ,;
        {"F137", "C",  50,   0} ,;
        {"F138", "C",  50,   0} ,;
        {"F139", "C",  50,   0} ,;
        {"F140", "C",  50,   0} ,;
        {"F141", "C",  50,   0} ,;
        {"F142", "C",  50,   0} ,;
        {"F143", "C",  50,   0} ,;
        {"F144", "C",  50,   0} ,;
        {"F145", "C",  50,   0} ,;
        {"F146", "C",  50,   0} ,;
        {"F147", "C",  50,   0} ,;
        {"F148", "C",  50,   0} ,;
        {"F149", "C",  50,   0} ,;
      {"F150", "C",  50,   0} })

use ".\dataimport\import" new ALIAS source                           
dbzap()  // Delete records                        
dbpack()                                          
APPEND FROM ".\dataimport\import.csv" DELIMITED WITH ( { '"', ";" } )

select source
use

return nil

Best Regards

Aljoscha

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Load FROM EXCEL
Posted: Tue Jan 13, 2015 02:10 PM

It is extremely easy to read data of an Excel Range into an array. This is just one line code.

aData := ArrTranspose( oRange:Value ) // singe statement
Where oRange is the object of the range in the excel sheet containing the data to be read.

Example:

Code (fw): Select all Collapse

oRange := GetExcelRange( cExcelFileName, cSheetName, { nTop, nLeft, nBottom, nRight } )
if oRange == nil
    // read failed
else
   aData := ArrTranspose( oRange:Value )
endif

XBROWSER aData

If we want to save aData into DBF ( assuming we have created DBF with matching structure )

Code (fw): Select all Collapse

DBCREATE( cDbf, aStruct )
USE ( cDbf ) NEW ALIAS DST
DST->( FW_ArrayToDBF( aData ) )
CLOSE DST

Regards

G. N. Rao.

Hyderabad, India

joseluisysturiz

Posts: 2064

Joined: Fri Jan 06, 2006 09:28 PM

Re: Load FROM EXCEL
Posted: Tue Jan 13, 2015 04:03 PM

Yo leo desde Excel asi...saludos...

Code (fw): Select all Collapse

   oExcel := TOleAuto():New( "Excel.Application" ) // ACTIVANDO EXCEL
   oExcel:Workbooks:Open( cFileXls ) // ABRO EL ARCHIVO SELECCIONADO
//
   oBook := oExcel:Get( "ActiveSheet" ) // ACTIVO HOJA EXCEL
   nRows := oBook:UsedRange:Rows:Count() // CANTIDAD LINEAS EXCEL INCLUYENDO LA LINEA 1 QUE ES LA CABECERA
   nCols := oBook:UsedRange:Columns:Count() // CANTIDAD COLUMNAS EXCEL

   FOR nRow = 2 TO nRows // CARGANDO DE EXCEL TABLA REPORTES
      campo :=   oBook:Cells( nRow, 1 ):Value // EL VALOR 1 ES LA COLUMNA
       .
       .
       .
   NEXT

Dios no está muerto...

Gracias a mi Dios ante todo!

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: Load FROM EXCEL
Posted: Tue Jan 13, 2015 04:18 PM

Hello,

I very much like Mr. Nages approach, very few lines of code and very effective.

Thank you.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

joseluisysturiz

Posts: 2064

Joined: Fri Jan 06, 2006 09:28 PM

Re: Load FROM EXCEL
Posted: Tue Jan 13, 2015 04:24 PM

Si necesitas todas las columnas, la forma de Nages es super buena si quieres todas las columnas, pero que si quieres unas columnas y otras no.? lo importante es que hay opciones para usar a gusto del consumidor, saludos... :shock:

Dios no está muerto...

Gracias a mi Dios ante todo!

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: Load FROM EXCEL
Posted: Tue Jan 13, 2015 04:57 PM

Hola,

Se puede manipular el array por ejemplo.

No iba con ningún propósito negativo, sólo reseñar que en los últimos tiempos Mr. Nages ha venido creando una serie de funciones y clases que ahorran un montón de tiempo.

Para mi, por ejemplo, TDataRow es extraordinaria en el ahorro de tiempo!.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Load FROM EXCEL
Posted: Fri Jan 16, 2015 01:19 AM

Please see

viewtopic.php?f=3p=170675#p170675

&

Regards

G. N. Rao.

Hyderabad, India

cnavarro

Posts: 6755

Joined: Wed Feb 15, 2012 08:25 PM

Re: Load FROM EXCEL
Posted: Thu Apr 09, 2015 06:31 PM

nageswaragunupudi wrote:It is extremely easy to read data of an Excel Range into an array. This is just one line code.

aData := ArrTranspose( oRange:Value ) // singe statement
Where oRange is the object of the range in the excel sheet containing the data to be read.

Example:
Code (fw): Select all Collapse
oRange := GetExcelRange( cExcelFileName, cSheetName, { nTop, nLeft, nBottom, nRight } )
if oRange == nil
    // read failed
else
   aData := ArrTranspose( oRange:Value )
endif

XBROWSER aData

Mr. Rao
I followed his example, works well
When you exit the application and open the file with excel tells me that the file is locked read-only
I tried withlOpened: = .F. andlOpened: = .T.
Excel is running
Use Win8, office 2010
The book is formatted Excel 93-2007

He seguido su ejemplo, funciona bien
Al salir de la aplicacion y abro el fichero con excel me dice que el fichero esta bloqueado solo lectura
Excel se queda ejecutando
Uso Win8, office 2010
El libro tiene formato Excel 93-2007

Cristobal Navarro

Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo

El secreto de la felicidad no está en hacer lo que te gusta, sino en que te guste lo que haces

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Load FROM EXCEL
Posted: Thu Apr 09, 2015 11:16 PM

After using the oRange,

Code (fw): Select all Collapse

   oRange:WorkSheet:Parent:Close()

This closes the workbook.

Regards

G. N. Rao.

Hyderabad, India

cnavarro

Posts: 6755

Joined: Wed Feb 15, 2012 08:25 PM

Re: Load FROM EXCEL
Posted: Fri Apr 10, 2015 11:25 AM

Thanks
Perfect

Cristobal Navarro

Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo

El secreto de la felicidad no está en hacer lo que te gusta, sino en que te guste lo que haces

FiveTech Support Forums

Continue the discussion