Excel with ODBC

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
Vanguarda
Posts: 543
Joined: Wed Feb 11, 2009 10:56 am
Location: Americana - SP
Contact:

Excel with ODBC

Post by Vanguarda »

Hi friends,

I am have some difficulties in work with ODBC and excel tables.

On example (in attachament), i have a tables with 3 fields. The first line of table, have the name of fields. But when i execute the conexion, the data of first line not is complete. The field "Matricula" comes with value "0", and the function len(oConexion:aRecordSet[1]) returns with value 4. But i have only 3 fields on this table?! :o

I don´t understood...

When i work with ODBC+Firebird or Access, work fine. But i never had work with Excel with ODBC, is my first experience with this.

Therefore, i call help of HmgForum.

Thank any help with my problem.


regards,
Attachments
Teste-XLS.rar
(585.72 KiB) Downloaded 311 times
--
Paulo Sérgio Durço (Vanguarda)


http://hmglights.wordpress.com/
User avatar
luisvasquezcl
Posts: 1258
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Contact:

Re: Excel with ODBC

Post by luisvasquezcl »

Hola
grabé todos los registros del recordset en una variable de texto y me di cuenta que el último registro que muestra es un /0. Es como un fin de linea; eso puede ser debido a como lo interpreta el driver Odbc. Entonces... debes considerar el largo del registro -1
o sea Len(oxls:aRecordset[x])-1.
Espero te sea de ayuda.
Saludos cordiales,
Luis Vasquez

Hello
I recorded all the records in a recordset variable text and realized that the last record shows that a / 0. It's like an order online, this can be interpreted as due to the ODBC driver. So ... should consider the long record of -1
or Len (oxls: aRecordset [x]) -1.
I hope you be able to help.
Best regards,
Luis Vasquez
User avatar
Vanguarda
Posts: 543
Joined: Wed Feb 11, 2009 10:56 am
Location: Americana - SP
Contact:

Re: Excel with ODBC

Post by Vanguarda »

Hi my friend luisvasquezcl,

At first, thank very much for you attention.

This tips is very usefull for me. Will help me a lot. But anyway, that occurs with the last record of table (Field Matricula)? It not showed on array RecordSet.

Thanks in advance

with best regards,
--
Paulo Sérgio Durço (Vanguarda)


http://hmglights.wordpress.com/
User avatar
luisvasquezcl
Posts: 1258
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Contact:

Re: Excel with ODBC

Post by luisvasquezcl »

Paulo,
Why not use ole to open excel file directly instead of odbc?
Ole is much better because direct talks with excel without intermediaries ... ODBC is how to translate texts on google, or is similar but not equal
Regards,
Luis Vasquez.

Paulo,
¿Por que no utilizas ole para abrir directamente el archivo excel en vez de odbc?
Es mucho mejor con ole ya que conversas directamente con excel sin intermediarios... con odbc es como traducir textos en google, o sea, es parecido pero no es igual
Cordialmente
Luis Vasquez
User avatar
Vanguarda
Posts: 543
Joined: Wed Feb 11, 2009 10:56 am
Location: Americana - SP
Contact:

Re: Excel with ODBC

Post by Vanguarda »

Hi my friend,

I´ll try with OLE.

Thank for you attention and help.


Regards,
--
Paulo Sérgio Durço (Vanguarda)


http://hmglights.wordpress.com/
User avatar
Vanguarda
Posts: 543
Joined: Wed Feb 11, 2009 10:56 am
Location: Americana - SP
Contact:

Re: Excel with ODBC

Post by Vanguarda »

hi my friend luisvasquezcl,

I´m try open a Table of excel with OLE\ADO, but i´m still get a error and not i can find the cause.

This error occurs when i try this command : oConexion:Open() and oTable:Open().
please see the attached file.

i want thank your help once again. i never work with ADO\OLE think that it will be very gratifying for me.


Thank again

regards,
Attachments
Import.rar
(583.62 KiB) Downloaded 298 times
--
Paulo Sérgio Durço (Vanguarda)


http://hmglights.wordpress.com/
User avatar
luisvasquezcl
Posts: 1258
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Contact:

Re: Excel with ODBC

Post by luisvasquezcl »

Hi Paulo
There was a small mistake but corrected it.
Rewrite the program to see how the data are derived.
Always considered a column ghost / 0, but recovered as the data is not disturbed.
Anyway there is an easier way to retrieve data from excel. I will prepare an example and I'm sending.
I hope you be able to help.
regards
Luis Vasquez.

Habia un pequeño error pero ya lo corregí.
Reescribí el programa para que veas como se obtienen los datos.
Siempre considera una columna fantasma /0, pero como recuperé los datos no molesta.
De todas formas hay una forma más fácil para recuperar datos de excel. Voy a preparar un ejemplo y te lo estoy enviando.
Espero te sea de ayuda.
Attachments
Import.zip
(691.58 KiB) Downloaded 312 times
User avatar
luisvasquezcl
Posts: 1258
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Contact:

Re: Excel with ODBC

Post by luisvasquezcl »

New example...
you must see carga_excel procedure.
Regards,
Luis VAsquez
Attachments
Import.zip
new example.
(692.42 KiB) Downloaded 352 times
User avatar
Vanguarda
Posts: 543
Joined: Wed Feb 11, 2009 10:56 am
Location: Americana - SP
Contact:

Re: Excel with ODBC

Post by Vanguarda »

Hi my friend Luis,

I think it will be very usefull for me. Thanks for your attention and help.

I´m glad for i've "brothers" as you... Thank, thank and thank again


with best regards,
--
Paulo Sérgio Durço (Vanguarda)


http://hmglights.wordpress.com/
User avatar
Vanguarda
Posts: 543
Joined: Wed Feb 11, 2009 10:56 am
Location: Americana - SP
Contact:

Re: Excel with ODBC

Post by Vanguarda »

hi my friend luisvasquezcl,

I still read your main.prg that you send me, and i see the function Carga_Excel(), and i see that it is very simple and complete for my needs. I think that this way (OLE) is really that i search... I have some question:

1-) If i working with OLE, i have need of include the 'ADO.CH' on my MAIN.PRG?
2-) Exist someway, (function) to i know the names of the "sheets" of one determinate file.xls?


Thanks a lot....

With best regards,
--
Paulo Sérgio Durço (Vanguarda)


http://hmglights.wordpress.com/
Post Reply