Importar desde Excel

17/04/2008 - 16:45 por Franklin Maza Pineda | Informe spam
Hola amigos del foro.
Estoy usando este codigo para importar datos desde el Excel, que se publico
en portalfox.
Pero tengo un problema siempre que hago el SELECT, me devuelven el cursor
solo campos del tipo MEMO, hay alguna forma de que me devuelva campos de
tipo caracter
de un maximo de 100 de longitud.

gracias


*--
* AUTHOR: Trevor Hancock
* CREATED: 02/15/08 04:55:31 PM
* ABSTRACT: Code demonstrates how to connect to
* and extract data from an Excel 2007 Workbook
* using the "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
* from the 2007 Office System Driver: Data Connectivity Components
*--
LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
lcSQLCmd AS STRING, lnSuccess AS INTEGER, ;
lcConnstr AS STRING
CLEAR

lcXLBook = [C:\SampleWorkbook.xlsx]

lcConnstr = [Driver=] + ;
[{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
[DBQ=] + lcXLBook

IF !FILE( lcXLBook )
? [Excel file not found]
RETURN .F.
ENDIF
*-- Attempt a connection to the .XLSX WorkBook.
*-- NOTE: If the specified workbook is not found,
*-- it will be created by this driver! You cannot rely on a
*-- connection failure - it will never fail. Ergo, success
*-- is not checked here. Used FILE() instead.
lnSQLHand = SQLSTRINGCONNECT( lcConnstr )

*-- Connect successful if we are here. Extract data...
lcSQLCmd = [Select * FROM "Sheet1$"]
lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )
IF lnSuccess < 0
LOCAL ARRAY laErr[1]
AERROR( laErr )
? laErr(3)
SQLDISCONNECT( lnSQLHand )
RETURN .F.
ENDIF


*-- Show the results
SELECT xlResults
BROWSE NOWAIT
SQLDISCONNECT( lnSQLHand )

Preguntas similare

Leer las respuestas

#1 Gux (MVP)
17/04/2008 - 18:50 | Informe spam
Usted debería, una vez recibidos los campos MEMO, transformarlos a caracteres
de la longitud deseada.

Gustavo Larriera, Microsoft MVP
https://mvp.support.microsoft.com/profile/gux
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"Franklin Maza Pineda" wrote:

Hola amigos del foro.
Estoy usando este codigo para importar datos desde el Excel, que se publico
en portalfox.
Pero tengo un problema siempre que hago el SELECT, me devuelven el cursor
solo campos del tipo MEMO, hay alguna forma de que me devuelva campos de
tipo caracter
de un maximo de 100 de longitud.

gracias


*--
* AUTHOR: Trevor Hancock
* CREATED: 02/15/08 04:55:31 PM
* ABSTRACT: Code demonstrates how to connect to
* and extract data from an Excel 2007 Workbook
* using the "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
* from the 2007 Office System Driver: Data Connectivity Components
*--
LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
lcSQLCmd AS STRING, lnSuccess AS INTEGER, ;
lcConnstr AS STRING
CLEAR

lcXLBook = [C:\SampleWorkbook.xlsx]

lcConnstr = [Driver=] + ;
[{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
[DBQ=] + lcXLBook

IF !FILE( lcXLBook )
? [Excel file not found]
RETURN .F.
ENDIF
*-- Attempt a connection to the .XLSX WorkBook.
*-- NOTE: If the specified workbook is not found,
*-- it will be created by this driver! You cannot rely on a
*-- connection failure - it will never fail. Ergo, success
*-- is not checked here. Used FILE() instead.
lnSQLHand = SQLSTRINGCONNECT( lcConnstr )

*-- Connect successful if we are here. Extract data...
lcSQLCmd = [Select * FROM "Sheet1$"]
lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )
IF lnSuccess < 0
LOCAL ARRAY laErr[1]
AERROR( laErr )
? laErr(3)
SQLDISCONNECT( lnSQLHand )
RETURN .F.
ENDIF


*-- Show the results
SELECT xlResults
BROWSE NOWAIT
SQLDISCONNECT( lnSQLHand )

email Siga el debate Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaRespuesta Tengo una respuesta
Search Busqueda sugerida