FiveTech Software tech support forums

www.FiveTechSoft.com
http://forums.fivetechsoft.com/

Load FROM EXCEL

http://forums.fivetechsoft.com/viewtopic.php?f=3&t=29965

Page 1 of 1

Load FROM EXCEL

Posted: Tue Jan 13, 2015 12:12 pm
by avista
Hi,

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

Best regardsm

Re: Load FROM EXCEL

Posted: Tue Jan 13, 2015 12:57 pm
by ali
Hi!

I have only a sample to import CSV to DBF.

Code: Select all

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

Re: Load FROM EXCEL

Posted: Tue Jan 13, 2015 2:10 pm
by nageswaragunupudi
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: Select all

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: Select all

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

Re: Load FROM EXCEL

Posted: Tue Jan 13, 2015 4:03 pm
by joseluisysturiz
Yo leo desde Excel asi...saludos... :shock:

Code: Select all

   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
 

Re: Load FROM EXCEL

Posted: Tue Jan 13, 2015 4:18 pm
by lucasdebeltran
Hello,

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

Thank you.

Re: Load FROM EXCEL

Posted: Tue Jan 13, 2015 4:24 pm
by joseluisysturiz
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:

Re: Load FROM EXCEL

Posted: Tue Jan 13, 2015 4:57 pm
by lucasdebeltran
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!.

Re: Load FROM EXCEL

Posted: Fri Jan 16, 2015 1:19 am
by nageswaragunupudi
Please see
http://forums.fivetechsupport.com/viewt ... 75#p170675

Re: Load FROM EXCEL

Posted: Thu Apr 09, 2015 6:31 pm
by cnavarro
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: Select all

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

Re: Load FROM EXCEL

Posted: Thu Apr 09, 2015 11:16 pm
by nageswaragunupudi
After using the oRange,

Code: Select all

   oRange:WorkSheet:Parent:Close()
 
This closes the workbook.

Re: Load FROM EXCEL

Posted: Fri Apr 10, 2015 11:25 am
by cnavarro
Thanks
Perfect
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited