topic Re: Fecha incorrecta desde Labview 2009 a Excel in LabVIEW

Fecha incorrecta desde Labview 2009 a Excel

Miguel_Fernandez — Wed, 16 Dec 2009 12:00:39 GMT

WRONG FORUM. PLEASE MOVE TO SPANISH FORUM. THANK YOU

Hola. Estoy usando el report generation Toolkit 1.0.1 para traspasar datos de una tabla de texto a Excel 2003 SP3.

Dentro de la tabla hay una columna de fecha con formato DD/MM/AAAA HH:MM:SS (ejemplo 06/11/2009 09:35:12 donde 6 es el dia y 11 es el mes)

Al usar Excel Easy Table toda la tabla se pasa correctamente pero si una fecha tiene el campo DD menor de 13, en la hoja de excel aparece invertida ( MM/DD/AAAA HH:MM:SS)

No es problema de excel porque si copio y pego directamente esos campos de texto no me los convierte. Lo que me interesa es que los textos de la tabla de texto me aparezcan en excel sin ningun cambio y por lo visto durante la exportacion se hace alguna conversion.

Esta es la tabla que quiero exportar

El codigo que uso como snipett:

Espero que alguien pueda echarme una mano

Gracias y un saludo

Mensaje editado por Miguel_Fernandez

Re: Invalid date from Labview 2009 to Excel

Ray.R — Wed, 16 Dec 2009 13:19:57 GMT

English translation:

Hello. I'm using the Report Generation Toolkit 1.0.1 to transfer data from a text table to Excel 2003 SP3.

Within the table is a column of date format DD / MM / YYYY HH: MM: SS (06/11/2009 09:35:12 example where 6 is the day and 11 is the month)

By using Excel Easy Table entire table is passed correctly but if a date is less than 13 DD field in the excel sheet is reversed (MM / DD / YYYY HH: MM: SS)

Excel has no problem because if I copy and paste text directly those fields do it. What interests me is that the texts of the text table appears in excel without any changes, and apparently converts when exported.

This is the table you want to export

(see first image in post above)

Here is the code snipett:

(see 2nd image in post above)

I hope someone can help me out.

Thanks and best regards

Message Edited by Ray.R on 12-16-2009 08:19 AM

Re: Invalid date from Labview 2009 to Excel

Ray.R — Wed, 16 Dec 2009 13:32:42 GMT

I suspect that the problem is still with Excel or the way the cells are formatted for dates.

When you use the Toolkit, it probably sends a single entry at a time. Excel tries to interpret the date based ondate format that it supports and takes the first value as the month. You might get away with YYYY-MM-DD, but for a single entry, Excel may always try to interpret it as MM-DD-YYYY.

When manually entering the value, one cell at a time, Excel reacts the same way as you describe.

When you copy & paste the entire table, Excel interprets it as text and not dates, which is why it "looks" correct.

The problem is not LabVIEW.

Sospecho que el problema sigue con Excel o la forma en que las células tienen el formato de las fechas.

Cuando se utiliza el kit de herramientas, es probable que envía una sola entrada a la vez. Excel intenta interpretar la fecha formato basado en Ondate que apoya y toma el primer valor que el mes. Puede que salga con la AAAA-MM-DD, pero para una sola entrada, Excel siempre puede tratar de interpretarlo como DD-MM-AAAA.

Al introducir manualmente el valor de una celda a la vez, Excel reacciona de la misma manera que usted describe.

Al copiar y pegar toda la tabla, Excel lo interpreta como texto y no las fechas, por lo que "parece" correcto.

El problema no es de LabVIEW.

Message Edited by Ray.R on 12-16-2009 08:32 AM

Re: Fecha incorrecta desde Labview 2009 a Excel

Miguel_Fernandez — Wed, 16 Dec 2009 14:25:47 GMT

Thank you for the answer.

I´ve tried to reproduce the error typing directly in the Excel Page but it doesn´t work like you say.

If I type 06/11/2009 13:12:45 (DD/MM/YYYY HH:MM:SS) excel automatically changes to 06/11/2009 13:12 (DD/MM/YYYY HH:MM)

If I type 14/11/2009 13:12:45 (DD/MM/YYYY HH:MM:SS) excel automatically changes to 14/11/2009 13:12 (DD/MM/YYYY HH:MM)

If i copy the entire table, it is pasted unchanged (as plain text)

I still think that the report kit is doing some kind of conversion before writing the values to excel

However if the report is written to Microsoft Word the date is correct

I have the Spanish locale and windows 7 btw

I will try to see if I can do something.

Thank you.

Re: Fecha incorrecta desde Labview 2009 a Excel

Ray.R — Thu, 17 Dec 2009 00:27:19 GMT

When I typed in the date, I did not enter the hours and minutes. I was using Excel 2003.

I tried it again with Excel XP and for 14/11/2009 I get text.

text:

For 06/11/2009, I get date.

date:

(Notice how it displays the time as 11:15:00 AM)

The same happens if I include the hours & minutes.

The values were entered directly into Excel.

Message Edited by Ray.R on 12-16-2009 07:27 PM

Re: Fecha incorrecta desde Labview 2009 a Excel

Miguel_Fernandez — Thu, 17 Dec 2009 09:26:29 GMT

That last example is exactly whats happening to me. If i type directly into excel, no problem but if i export the table is when strange things occurs.

This is what i get from this table

Anyway thanks for the help. Im going to try another excel version in windows xp to check this.

Re: Fecha incorrecta desde Labview 2009 a Excel

Miguel_Fernandez — Thu, 17 Dec 2009 12:35:11 GMT

Ugh... Same thing happens in windows XP and Excel 2007

Guess the next thing to try is Report generation toolkit from 8.2.1 as i had a project done with this and i dont remember these kind of problems with dates.

Thank you.

Re: Fecha incorrecta desde Labview 2009 a Excel

Ray.R — Thu, 17 Dec 2009 19:24:29 GMT

Actually, why not simply enter the date in the format that Excel expects it.. Which is not usually the right way.. It likes MM-DD-YYYY, whereas logic dictates that YYYY-MM-DD makes more sense or DD-MM-YYYY would also be acceptable. ... but not to Excel...

Try to change the datestamp and it should solve your problem. I think YYYY-MM-DD would work.

Well, it will, but Excel converts 2009/11/06 to 11/6/2009

Unless you also change the locale..

I changed it to English (Canadian) and it will display it correctly, although the cell is displayed differently at the top (edit window). So you may get away with playing with the locale.

see below

Message Edited by Ray.R on 12-17-2009 02:24 PM

Re: Fecha incorrecta desde Labview 2009 a Excel

Miguel_Fernandez — Mon, 21 Dec 2009 12:28:06 GMT

Problem solved (well, sort of)

With Excel 2000 the date export with the right format.

So in my case (excel 2003) I change the date format to "DD/MM/YYYY -- HH:MM:SS" before the export so excel interprets as pure text.

Thx.