topic You may be using a newer… in Product Forum

Import CSV to Excel Worbook

NarendraMundwad — Wed, 13 Mar 2019 20:39:00 GMT

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?

You may be using a newer…

AmiBarrett — Wed, 13 Mar 2019 22:35:00 GMT

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.

@amibarrett, I'm not sure…

david.l.morris — Fri, 15 Mar 2019 14:39:00 GMT

@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.

Right/Down*

david.l.morris — Fri, 15 Mar 2019 14:40:00 GMT

Right/Down*

RE: You may be using a newer…

LucieKennedy — Wed, 04 Sep 2019 19:20:00 GMT

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

RE: Import CSV to Excel Worbook

LucieKennedy — Wed, 04 Sep 2019 19:22:00 GMT

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

RE: Import CSV to Excel Worbook

BenKirimlidis — Wed, 04 Sep 2019 19:49:00 GMT

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

RE: Import CSV to Excel Worbook

LucieKennedy — Wed, 04 Sep 2019 20:36:00 GMT

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

RE: Import CSV to Excel Worbook

LucieKennedy — Wed, 04 Sep 2019 21:32:00 GMT

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

RE: Import CSV to Excel Worbook

GopalBhaire — Thu, 05 Sep 2019 04:32:00 GMT

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

RE: Import CSV to Excel Worbook

BenKirimlidis — Thu, 05 Sep 2019 08:19:00 GMT

I had them defined yes. Shouldn't have to, but that is what I had.

And having over 100k+ lines in a .csv will cause that memory issue. You should break it into smaller files.

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

RE: Import CSV to Excel Worbook

sahana.mohan — Mon, 27 Feb 2023 10:40:00 GMT

Thanks Gopal. It worked.

------------------------------
Sahana Mohan
Solution Designer
Blueprism
------------------------------