Autoit CSV(Semicolon) to XLS Array

[9tailsfox]

Hi i need help to convert the CSV file content with ; semicolon data to Excel 2003 xls file with the array column. But there is an issue also the content of the CSV has unique number value of 20 digits. which as I know the Excel will display the number to some like "9.109442E+19" . When you click on it the cell show "91094210325010300000" it was duplicated to all other column instead of show its unique number "91094210325010331013". Please guide me as I'm trying to create automation CSV to EXCEL conversion. Thank you 

[Subz]

Can you post example document?

[9tailsfox]

Semicolon to Comma.csv Yes this is edited file from the CSV semicolon to Comma. Because there are some customers details So i had edited it. please take a look. thank you

[Marc]

According the loss of detail in your numbers: If you enter the number 91094210325010331013 into an excel field, you will notice a loss of detail immediately. That is because the number is simply too big for excel.

Workaround: import this as text field, see this article

By the way, in your .csv file, the numbers are already cut off (9.10942E+19).

[9tailsfox]

thanks for the reply. If the the CSV file I leave untouched and I wanna convert it to xls with arrayed column without open through MS Excel. I think the number won't change to (9.10942E+19). Anyway possible way just through AUTOIT ONLY to convert to XLS? thanks

[funkey]

You can surround the value with ="1234" to make it a function or you can append a tab char to the number to make it a text.

 

12345678987654321234,="12345678987654321456",12345678987654321234

 

[FrancescoDiMuro]

22 minutes ago, 9tailsfox said:

I wanna convert it to xls with arrayed column without open through MS Excel.

You can't.

You always have to open your workbook and use _Excel_SaveAs() function.

In your CSV file you should have numbers formatted without scientific notation.

As @Marc suggested, you should format your column as Text, so the number won't "auto-format" itself as a number, maintaining the format you want

[Marc]

29 minutes ago, 9tailsfox said:

Anyway possible way just through AUTOIT ONLY to convert to XLS? thanks

I doubt it. Easiest way would be to use the Excel UDF from @water to open/convert it in Excel.

The only possible way I can think of, which could perhaps work is to

1. create an empty excel file on a PC with office on it
2. attach that empty file to your script
3. install Microsoft Access Database Engine on the PC where the script has to run
4. on every run, the template is dumped to disk, the scripts reads your csv and then writes the found data into the excel file as described here.

 I just found the linked thread, dunno if my workflow as described above would really work. 

[Subz]

As others have mentioned you would require Excel or OpenOffice or something to convert it to xls format.  To use Excel UDF you would only require the following:

#include <Excel.au3>
Local $sCsvFile = @ScriptDir & "\Semicolon to Comma.csv"
Local $sXlsFile = @ScriptDir & "\Semicolon to Comma.xls"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sCsvFile)
$oWorkbook.ActiveSheet.Usedrange.NumberFormat = "0"
_Excel_BookSaveAs($oWorkbook, $sXlsFile, $xlExcel8)

 

[9tailsfox]

yeah, or I if I could use or create a program tool to convert it. But now i'm writing the script and test so far so good. the only thing is the Blockinput is not working. any idea to make it work on windows 10. otherwise someone accidentally click mouse or keyboard would mess up the whole process. thanks

 

#include <IE.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

;Local $oAppl = _Excel_Open()
;Local $oMyDocDir = @MyDocumentsDir & "\Omg.xls"
;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error Copy Date" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Workbook 1
;Local $oWorkbook1 = _Excel_BookOpen($oAppl, $oMyDocDir, False)
;Local $oRange = $oWorkbook1.ActiveSheet.Range("A4")
;_Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange) ;paste it to program like notepad, wordpad, DMS, or msword

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel, 1)
Local $sWorkbook = @MyDocumentsDir & "\OMG.xls"
_Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)

Local $xlMaximized = -4137
$oWorkbook.Application.Activewindow.WindowState = $xlMaximized
WinActivate ("[CLASS:NetUIHWND]", "")
BlockInput(1)
Sleep(2000)

Sleep(1000)
Send("{ALT}")
Send("{a}")
Send("{f}")
Send("{t}")
sleep(1000)
ClipPut ("c:\users\thianpoh.ang\documents\Snoopy1.csv")
sleep(1000)
Send("^v")
Sleep(1000)
Send("{Enter}")
Sleep(2000)
Send("{Tab}")
Sleep(500)
Send("{Tab}")
Sleep(500)
Send("{DOWN}")
Sleep(500)
Send("{DOWN}")
Sleep(500)
Send("{DOWN}")
Sleep(500)
Send("{Tab}")
Sleep(500)
Send("{Tab}")
Sleep(500)
Send("{Tab}")
Sleep(500)
Send("{Tab}")
Sleep(1000)
Send("{Enter}")
Sleep(5000)
Send("^s")
Sleep(2000)
Send("{Enter}")
Sleep(1000)
Send("!{F4}")
Sleep(1000)
BlockInput(0)
 

[Subz]

So did you try the code I provided?