Copy Paste CSV to Excel Formating Error

[pramodgn]

Help Required.

While using _ExcelReadSheetToArray and copying CSV file to .xls File.

It work beautifully but there is a catch. One CSV file contain Date + Time format, so while copying it to .xls. It copy some format which is unknown.

Am new to this AutoIT, I love this product.

Thanks

PG

[sleepydvdr]

Do you think _ExcelNumberFormat might help? What does the format of the number look like?

[pramodgn]

  On 7/12/2011 at 7:51 PM, 'sleepydvdr said:

Do you think _ExcelNumberFormat might help? What does the format of the number look like?

I have tried _ExcelNumberFormat too its not working. It in date + time (DD/MM/YYYY HH:MM:SS) format

thanks

Pramod

[JoHanatCent]

  On 7/12/2011 at 6:48 PM, 'pramodgn said:

Help Required.

While using _ExcelReadSheetToArray and copying CSV file to .xls File.

It work beautifully but there is a catch. One CSV file contain Date + Time format, so while copying it to .xls. It copy some format which is unknown.

Am new to this AutoIT, I love this product.

Thanks

PG

Why copy the CSV file if you can just save as a .xls file direct?

If the format is still not correct just format those fields from AI?

[Juvigy]

You will have to post your code. If i paste to excel :

"12/12/2011 12:03:01"

The format is recognized as a Date/time and i can reformat it whatever i want - for example 40889.50209 (general).

[pramodgn]

  On 7/13/2011 at 2:31 PM, 'Juvigy said:

You will have to post your code. If i paste to excel :

"12/12/2011 12:03:01"

The format is recognized as a Date/time and i can reformat it whatever i want - for example 40889.50209 (general).

Hi,

Please find my code, You can clearly see that it array display show different format. I have converted CSV into excel 2007.

#include <Excel.au3>
#include <Array.au3>


$sFilePath1 = @ScriptDir & "\export(1).xlsx" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf
$aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
_ArrayDisplay($aArray, "Array using Default Parameters")

I dont have premition to upload file

Date

4/7/11 9:24 AM

4/7/11 10:22 AM

4/7/11 11:17 AM

4/7/11 11:41 AM

4/7/11 12:14 PM

4/7/11 1:51 PM

4/7/11 2:22 PM

4/7/11 2:38 PM

4/7/11 2:48 PM

4/7/11 4:02 PM

4/7/11 4:07 PM

4/7/11 4:27 PM

4/7/11 5:17 PM

4/7/11 5:40 PM

4/7/11 5:48 PM

4/7/11 6:02 PM

5/7/11 8:35 AM

5/7/11 11:09 AM

[Juvigy]

And what do you get in the array ? something like :

[0]|18|1
[1]||20110407092400
[2]||20110407102200
[3]||20110407111700
[4]||20110407114100
[5]||20110407121400
[6]||20110407135100
[7]||20110407142200

This is normal. Lets say you have it in cell A2.Add this to the end of your script :

ConsoleWrite("ASD:"&$oExcel.Application.ActiveSheet.Range("A2").Value&@CRLF)
ConsoleWrite("ASD:"&$oExcel.Application.ActiveSheet.Range("A2").Text&@CRLF)

The output will be:

ASD:20110407102200
ASD:4/7/2011 10:22

[pramodgn]

  On 7/14/2011 at 6:27 AM, 'Juvigy said:

And what do you get in the array ? something like :

[0]|18|1
[1]||20110407092400
[2]||20110407102200
[3]||20110407111700
[4]||20110407114100
[5]||20110407121400
[6]||20110407135100
[7]||20110407142200

This is normal. Lets say you have it in cell A2.Add this to the end of your script :

ConsoleWrite("ASD:"&$oExcel.Application.ActiveSheet.Range("A2").Value&@CRLF)
ConsoleWrite("ASD:"&$oExcel.Application.ActiveSheet.Range("A2").Text&@CRLF)

The output will be:

ASD:20110407102200
ASD:4/7/2011 10:22

Thanks for your support. After adding it am getting this.

ASD:20110407092400

ASD:########

[JoHanatCent]

Well at least "20110407092400" is normal as Juvigy said.

It is Year Month Day Hour minute and seconds. (YYYYMMDDHHMMSS)

What do you want to do with it from here and in what format should it be in then?

[Juvigy]

You can use the string functions to rearange "20110407092400" in what ever format you like.

The second one looks strange to me (ASD:########) - can you make a screenshot of how it looks in excel ?