Import CSV to Excel Worbook

[NarendraMundwad]

I'm trying to import .csv to Excel workbook so that I can save it in .xls format. When I use Import csv action it throws an error mentioned below: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))   Can anyone please suggest on what needs to be done?  

[AmiBarrett]

You may be using a newer version of the Excel VBO than I have, but is that where the import CSV action you're using is? If so, an alternative would be to use the one in Utility - Strings, Get CSV as Collection. From there, you should be able to write the collection to Excel using Write Collection.

[david.l.morris]

@amibarrett, I'm not sure when it was added to the Excel VBO, but yeah it's an action called 'Import CSV'. But it is (though I haven't looked at the code) apparently doing the same thing as 'Get CSV as Collection' and then 'Write Collection' together do. @Narendra__Mundwadkar, What inputs are you giving the 'Import CSV' action? The only input I can see that might cause a problem is the Destination Range. Since you're getting an invalid index error, it may be that the destination range you're giving doesn't match the size of the source range. To avoid the issue entirely, just give a single cell as an input. Try ""A1"" as an input to 'Destination Range', and it will start at A1 and proceed Left/Down with the rest of the data.  

[david.l.morris]

Right/Down*

[LucieKennedy]

​Hi Ami, Did you ever get this to work? I have tried Dave's suggestion and I am still getting this error.

------------------------------
Payroll Specialist
Zurich Services
------------------------------

[LucieKennedy]

Hi Narendra,

did you ever solve this? I have tried Dave's suggestion and I am still getting the same error. thanks. ​

------------------------------
Payroll Specialist
Zurich Services
------------------------------

[BenKirimlidis]

Hi,

Have the file path of the .csv file ready.  Open an instance of Excel, record the handle, name of the workbook, the worksheet name, a collection ready to be used.

1) Utility - File Management -> Action: Read All Text From File
Input the file path and the output should grab the text into a data item

2) Utility - Strings -> Action get CSV as Collection
Input the string taken from step 1 and store the output in a collection

3) MS - Excel VBO -> Write Collection
Pass in the excel handle, the collection populated in step 2, the name of the workbook, the worksheet name, cell reference set = "A1", set 'include columns names' to 'True'

------------------------------
Ben Kirimlidis
RPA Developer and Data Analyst
PTSB
Europe/London
------------------------------

[LucieKennedy]

​Thanks, Ben. I tried your suggestion and I'm getting a message now Internal : Could not execute code stage because exception thrown by code stage: Cannot find column 1. Do I need to define the columns in the collection?

------------------------------
Payroll Specialist
Zurich Services
------------------------------

[LucieKennedy]

I am getting an Internal : Could not execute code stage because exception thrown by code stage: Exception of type 'System.OutOfMemoryException' was thrown. now too since the data item from the read all text stage is over 100K I believe. ​

------------------------------
Payroll Specialist
Zurich Services
------------------------------

[GopalBhaire]

I had created a saveas method for csv to xlsx. Just paste the below code on new page in Excel VBO.

To run Open csv using open wb, then run this action where filename will be path to save the xlsx file (C:\Test.xlsx)

<process name="__selection__MS Excel VBO" type="object" runmode="Exclusive"><stage stageid="7ab39259-a4b0-496c-8ad1-401784a6368f" name="Save CSV as Excel" type="SubSheetInfo"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><narrative></narrative><displayx>-195</displayx><displayy>-105</displayy><displaywidth>150</displaywidth><displayheight>90</displayheight><font family="Segoe UI" size="10" style="Regular" color="000000" /></stage><stage stageid="0688bd03-f051-432e-864f-f817f6f2f682" name="End" type="End"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><loginhibit /><narrative></narrative><displayx>15</displayx><displayy>90</displayy><displaywidth>60</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><outputs><output type="text" name="New Workbook Name" narrative="The new workbook name which Excel will use to identify the workbook within the instance." stage="newworkbookname" /></outputs></stage><stage stageid="8caf0dd3-15a7-42dd-8068-30a42879c386" name="filename" type="Data"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><narrative></narrative><displayx>-180</displayx><displayy>90</displayy><displaywidth>120</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>text</datatype><initialvalue /><private /><alwaysinit /></stage><stage stageid="d45aa8fc-34c0-4c7f-a391-2c30b78d352b" name="handle" type="Data"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><narrative></narrative><displayx>-180</displayx><displayy>0</displayy><displaywidth>120</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>number</datatype><initialvalue>0</initialvalue><private /><alwaysinit /></stage><stage stageid="b88491dd-b080-49a8-b201-a7c5c45624c0" name="Start" type="Start"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><loginhibit /><narrative></narrative><displayx>15</displayx><displayy>-105</displayy><displaywidth>60</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><inputs><input type="number" name="handle" narrative="The integer handle identifying the instance on which the workbook which should be saved resides. The default of zero indicates the currently active instance." stage="handle" /><input type="text" name="Workbook Name" narrative="The name of the workbook which should be saved" stage="workbookname" /><input type="text" name="Filename" narrative="The full path and filename to which the workbook should be saved with .xlsx extension" stage="filename" /></inputs><onsuccess>5def3c7c-0ef7-40a3-b35f-4fe794422808</onsuccess></stage><stage stageid="3ff606b8-299f-4dcf-80fa-818948873216" name="workbookname" type="Data"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><narrative></narrative><displayx>-180</displayx><displayy>45</displayy><displaywidth>120</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>text</datatype><initialvalue /><private /><alwaysinit /></stage><stage stageid="5def3c7c-0ef7-40a3-b35f-4fe794422808" name="Save as Excel" type="Code"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><loginhibit /><narrative></narrative><displayx>15</displayx><displayy>-15</displayy><displaywidth>120</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><inputs><input type="number" name="handle" expr="[handle]" /><input type="text" name="workbookname" expr="[workbookname]" /><input type="text" name="filename" expr="[filename]" /></inputs><outputs><output type="text" name="newworkbookname" stage="newworkbookname" /></outputs><onsuccess>0688bd03-f051-432e-864f-f817f6f2f682</onsuccess><code><![CDATA[Dim wb as Object  = GetWorkbook(handle,workbookname)
Dim excel as Object = wb.Application

excel.DisplayAlerts = False
wb.SaveAs(FileName:=filename, FileFormat:=51, CreateBackup:=False, ConflictResolution:=2)
excel.DisplayAlerts = True

newworkbookname = wb.Name]]></code></stage><stage stageid="502365e8-0d57-4c88-9e04-3d5ce5dcb29d" name="newworkbookname" type="Data"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><narrative>The new workbook name assigned by Excel after saving the book</narrative><displayx>-165</displayx><displayy>180</displayy><displaywidth>120</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>text</datatype><initialvalue /><private /><alwaysinit /></stage><stage stageid="4bc69e18-aa4d-4dee-851c-291487b8f848" name="Timeout" type="Data"><subsheetid>dcf60489-37e5-442e-81b9-29c1e4e22c0b</subsheetid><narrative></narrative><displayx>-180</displayx><displayy>135</displayy><displaywidth>120</displaywidth><displayheight>30</displayheight><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>number</datatype><initialvalue>30</initialvalue><private /><alwaysinit /></stage></process>

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------