Jump to content
Sign in to follow this  
pramodgn

Copy Paste CSV to Excel Formating Error

Recommended Posts

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

Share this post


Link to post
Share on other sites
sleepydvdr

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


#include <ByteMe.au3>

Share this post


Link to post
Share on other sites
pramodgn

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

Share this post


Link to post
Share on other sites
JoHanatCent

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?


Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
pramodgn

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
pramodgn

 
 

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:########

Share this post


Link to post
Share on other sites
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?


Share this post


Link to post
Share on other sites
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 ?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.