How to save a .csv file as an excel file in Excel 2013

[Mian]

Hi, I am new to AutoIT but I have many years of IT experience. I searched a lot for saving the document as xls and xlsx file but no success. The following is my code. Please help.

#include <Excel.au3>

Global $oExcel="D:\ChromeDownloads\abc.csv"
$prog_excel = _Excel_Open() ;opens a new instance of the Excel software
sleep(5000)
$workbook = _Excel_BookOpen($prog_excel, $oExcel) ; open an existing excel file
$oExcel.ActiveWorkbook.SaveAs ( "D:\ChromeDownloads\abc.xlsx",50,"","",False,False)
;_Excel_BookSaveAs($oExcel, "D:\ChromeDownloads\abc", "xlWorkbookDefault", True)
;_Excel_BookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

[UEZ]

Try

_Excel_BookOpenText

to open CSV.

Edited December 17, 2015 by UEZ

[water]

You need to add some error checking. What is the value of @error after _Excel_BookOpen or _Excel_BookSaveAs?

[Mian]

Hi my question is how can I save a file as xls type. For example at the moment I am using the following code which is saving a file as an xls format after it is modified in excel.

; Script to save document.

send("{f12}") ; this code open the save as dialog box in excel
sleep(3000)
Send("{TAB 1}"); this code select the save as type option
sleep(3000)
Send("M") ; this code select the type as xls format
sleep(3000)
Send("{TAB 6}"); this code click the save button
Send("S") ; this code click the save button
sleep(3000)
WinWait('Confirm Save As') ; this code wait until the confirmation pop up appears
WinActivate('Confirm Save As') ; this code activate the save as pop up
Send("{TAB 1}") ; this code select the yes button
Send("{ENTER}") ; this code click the save button to overwrite the previously saved file.

 

Edited December 21, 2015 by Mian

[water]

If you could post an (anonymized) part of your CSV (just a few lines) I'm sure I can come up with a simple script to open the CSV and save it as XLSX.

[Mian]

If you could post an (anonymized) part of your CSV (just a few lines) I'm sure I can come up with a simple script to open the CSV and save it as XLSX.

Thank you @water for your quick reply. The file I am working is a long file of more than 200 columns but any csv will do. In fact it doesnt matter that what file I am opening. All I need is to save a file opened in excel as excel file. 

But for you I am adding the sample csv I downloaded from https://support.spatialkey.com/spatialkey-sample-csv-data/  

FL_insurance_sample.csv

[water]

Rename your CSV file to TXT and the following code will work:
 

#include <excel.au3>
#include <MsgBoxConstants.au3>

Global $sPathTXT = "C:\temp\FL_insurance_sample.txt"
Global $sPathExcel = "C:\temp\FL_insurance_sample.xlsx"
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sPathTXT)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSaveAs($oWorkbook, $sPathExcel)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error saving the workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

[philipyo]

https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

[steveeye]

On 12/21/2015 at 9:30 PM, water said:

Rename your CSV file to TXT and the following code will work:
 

#include <excel.au3>
#include <MsgBoxConstants.au3>

Global $sPathTXT = "C:\temp\FL_insurance_sample.txt"
Global $sPathExcel = "C:\temp\FL_insurance_sample.xlsx"
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sPathTXT)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSaveAs($oWorkbook, $sPathExcel)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error saving the workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

This one worked for me. I got more issues that I am facing can I ask them to you 'water'.

[water]

Sure. Simply post them here if they are related to the subject of this thread or open a new one.