Load FROM EXCEL

Post Reply
User avatar
avista
Posts: 301
Joined: Fri Jun 01, 2007 9:07 am
Location: Macedonia

Load FROM EXCEL

Post 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
ali
Posts: 23
Joined: Wed May 07, 2008 2:50 pm
Location: Austria

Re: Load FROM EXCEL

Post 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
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Load FROM EXCEL

Post 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
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
joseluisysturiz
Posts: 2024
Joined: Fri Jan 06, 2006 9:28 pm
Location: Guatire - Caracas - Venezuela
Contact:

Re: Load FROM EXCEL

Post 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
 
Dios no está muerto...

Gracias a mi Dios ante todo!
User avatar
lucasdebeltran
Posts: 1303
Joined: Tue Jul 21, 2009 8:12 am
Contact:

Re: Load FROM EXCEL

Post by lucasdebeltran »

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.
User avatar
joseluisysturiz
Posts: 2024
Joined: Fri Jan 06, 2006 9:28 pm
Location: Guatire - Caracas - Venezuela
Contact:

Re: Load FROM EXCEL

Post 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:
Dios no está muerto...

Gracias a mi Dios ante todo!
User avatar
lucasdebeltran
Posts: 1303
Joined: Tue Jul 21, 2009 8:12 am
Contact:

Re: Load FROM EXCEL

Post 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!.
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.
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Load FROM EXCEL

Post by nageswaragunupudi »

Regards

G. N. Rao.
Hyderabad, India
User avatar
cnavarro
Posts: 5792
Joined: Wed Feb 15, 2012 8:25 pm
Location: España

Re: Load FROM EXCEL

Post 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
C. Navarro
Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo
Si alguien te dice que algo no se puede hacer, recuerda que esta hablando de sus limitaciones, no de las tuyas.
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Load FROM EXCEL

Post by nageswaragunupudi »

After using the oRange,

Code: Select all

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

G. N. Rao.
Hyderabad, India
User avatar
cnavarro
Posts: 5792
Joined: Wed Feb 15, 2012 8:25 pm
Location: España

Re: Load FROM EXCEL

Post by cnavarro »

Thanks
Perfect
C. Navarro
Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo
Si alguien te dice que algo no se puede hacer, recuerda que esta hablando de sus limitaciones, no de las tuyas.
Post Reply