Jump to content

Copy Paste CSV to Excel Formating Error


Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 
 

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:########
Link to comment
Share on other sites

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 ?

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...