topic RE: Importing CSV in Product Forum

Importing CSV

ErjolaMema — Tue, 08 Mar 2022 13:44:00 GMT

Hi everyone!

I want to Import text to Excel...And I do it with the action "Import CSV",but it doesn't catch me the values that starts with 0.
For example in txt file is "0023456789" whereas when I run the "Import CSV" it is "23456789"

What do I have to change into the code to fix my problem?

Thanks in advance,

Erjola

------------------------------
Erjola Mema
------------------------------

RE: Importing CSV

michaeloneil — Tue, 08 Mar 2022 13:58:00 GMT

Hi Erjola

This is likely because the rows/columns of these values are being auto formatted to numbers and the zeros are being removed by excel. You would probably have to add in an action after the import to format the columns/rows you need to correct. In the excel extended object there is an action called Format Cell which you can use to update these, just add in the range and set the format to number e.g. "0"

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Importing CSV

ewilson — Tue, 08 Mar 2022 14:05:00 GMT

@ErjolaMema,

If you look at the Code stage of the Import CSV action you'll see that it's using the Excel QueryTables collection to build a new QueryTable instance using the CSV data. There is a property that you can set on the QueryTable object instance that allows you to specify the data type of each column in the data that's being imported. By default, all columns are set to xlGeneral. So you could enhance the code in the Code stage to include the definition of your CSV data's column data types.

You can find out more about the property (TextFileColumnDataTypes) here. As @Michael ONeil pointed out, it may be easier to just perform an update on the column after you import the data. Especially if you have a large number of columns.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------