topic Re: Get Collection as CSV without header in Product Forum

Get Collection as CSV without header

NiklasFranke — Mon, 21 Nov 2022 15:22:37 GMT

Hello,

i tried to insert my collection into Excel. The Problem is that the collection contains 3k rows and 82 columns and the process tooked too long. So i solved it with "Get Collection as CSV" -> "Write Text File" -> "Import CSV" what tooks like 1 Second.

The Problem is, that i have to delete the old Header from the the Textdata "Collection CSV as Text" or insert the Collection without the header. I tried to replace "Column ([0-99]+)" with "" an then Trim the start but that does not work.

Otherwhise the CSV has a row too many.

I hope you can help me.

Re: Get Collection as CSV without header

ewilson — Mon, 21 Nov 2022 16:18:30 GMT

@NiklasFranke,

Which action did you use to write the collection to Excel originally? Was it Write Collection or Write Collection (Fast) on the Excel VBO?

UPDATED

You could edit the Excel VBO's Import CSV action so that it starts parsing the text file on row #2 instead of #1 (which is the header row). To do that, open the Code stage within the VBO and edit the following item:

Change the value to 2. That should skip the header row in your CSV. Just be aware that this will also impact any other CSV that you import using that action too. You could make it more flexible by creating an optional input parameter of type Flag. Give it a name like Include Header Row or something. Set the default value to True, and then adjust the code in the Code stage to set the value of .TextFileStartRow to either 1 (if flag is True) or 2 (if the flag is False).

Cheers,
Eric

Re: Get Collection as CSV without header

NiklasFranke — Tue, 22 Nov 2022 09:08:07 GMT

Hello @ewilson

I dont know why i haven't seen the Fast Verison of the VBO..

Both of your solutions work perfect.

As usual thanks a lot!

Re: Get Collection as CSV without header

NiklasFranke — Tue, 22 Nov 2022 13:33:31 GMT

Hello @ewilson

my problem now is that the CSV Data ist getting corrupted when saved. Numbers like 19,54 are now 1954 etc.

To solve this im made a copy of the VBO and put the save function on another site so the different Excel VBOs dont create an Error.

I changed the Fileformat to 6 and 62

But now im getting the following Error:

I think this i because i opened the Data with the normal VBO and trying to save it with the new one.

Re: Get Collection as CSV without header

ewilson — Tue, 22 Nov 2022 14:06:41 GMT

Hi @NiklasFranke,

Yes, I can see where opening the workbook/CSV with one VBO and then trying to save it with another would cause an issue as the later VBO wouldn't have a link to the specific instance unless you tried attaching to it.

Cheers,
Eric

Re: Get Collection as CSV without header

NiklasFranke — Tue, 22 Nov 2022 14:10:12 GMT

Hello @ewilson

is there any way to save the opened CSV without getting a Data corruption? I mean, i have to use "Save workbook as" or am i wrong?

I dont want to make duplicates in the Main VBOs.
Also if i want to attach it i would need to use the Application Modeller just to save it. That feels a little bit strange..

Re: Get Collection as CSV without header

ewilson — Tue, 22 Nov 2022 14:25:46 GMT

@NiklasFranke,

Let me make sure I'm following you. You have data in a Blue Prism Collection that you're loading into Microsoft Excel but then you want to save the file as a .CSV instead of a .XLSX, correct? Part of the problem here may be that if you have embedded commas in your data and then try to write that data to a .CSV file (which is comma-delimited by default) it could confuse Excel.

Cheers,
Eric

Re: Get Collection as CSV without header

NiklasFranke — Tue, 22 Nov 2022 14:49:03 GMT

@ewilson its pretty complicated.

Why i open the CVS data, wich i am working with, the data looks a normal Excel. Why BP opens it, it looks like a typical CSV and so i cant really work with it. To solve that, i correct the CSV data in which you helped me like one week ago (Encoding was ANSI Latin, dont know why)

Through this convertig i dont need to save the data as xlsx and let it be CSV.

Re: Get Collection as CSV without header

NiklasFranke — Tue, 22 Nov 2022 14:49:17 GMT

While working with it, i get the values in a collection, add some values and finaly i insert the collection back in the Data and want to save it as CSV. But when i do that, the numbers etc. are getting corrupten through the "Save wokbook as" function. Even when i save it in a new workbook.

i changed the Main VBOs just for testing and 62 isnt working at all

File Format 6 is working but the number values are also in a wrong Format (Last Row)

Re: Get Collection as CSV without header

ewilson — Tue, 22 Nov 2022 15:02:57 GMT

@NiklasFranke,

Ok, so this is a case of using commas as the delimiter as well as having them included in the data I assume based on your locale. There is an input parameter in the Import CSV action called Source Text Qualifier. The idea is that if you have data in the CSV that includes the delimiter value in it, that data should be enclosed in something. In your case, it looks like you have those fields enclosed in double quotes. If that is correct, go ahead and set the value of Source Text Qualifier equal to " (a single double quote) and see how that works for you.

Cheers,
Eric

Re: Get Collection as CSV without header

NiklasFranke — Tue, 22 Nov 2022 15:03:42 GMT

Probably Write Collection fast i causing the Problem. Even if i convert the CSV in XLSX and the just use the "Save Workbook" Function, the numbers are not right. The Document is not corruptet, but the numbers are.

Collection:
Excel:

Re: Get Collection as CSV without header

NiklasFranke — Tue, 22 Nov 2022 15:49:20 GMT

@ewilson i know what you mean. The first step, the converting, works perfect with the numbers. BUT when i want to insert the new Collection with "Write Collection fast" the new input values from the collection are getting corrupted.

Even before "saving as".
I just cant use the Function "Write collection fast" to insert the Collection..

Also tryed the Write collection fast function with a complete new Workbook but its still the same. It looks, that the Function isnt working properly.

I noticed that the other importet numbers are correct.

It looks that the 5 numbers after the comma (195,27274) are causing the Problem

Re: Get Collection as CSV without header

NiklasFranke — Wed, 23 Nov 2022 11:45:46 GMT

I think i found the Problem.

My Get Worksheet as Collections sets alle Columns to Datatype Text, so i had to convert my number values to text to insert it into the collection.

I tryed write collection fast with number and text and as you can see, the text value ist not right.

I will Copy my Collection in a second one, where the Data types are correct. A little bit annoying cause of the 82 columns but it will help,

Is there a way to get a collection without each field being a text? I added the collecton without the header and set the number fields to Data Type Number in the Excel data. But Columns are still Text

Re: Get Collection as CSV without header

ewilson — Wed, 23 Nov 2022 15:08:38 GMT

@NiklasFranke,

Are you using the latest version of the MS Excel VBO from the Digital Exchange? I was taking a look at the definition of the Get Worksheet as Collection... actions. It looks like the Fast action was deprecated and simply redirects the request to the Get Worksheet as Collection action.

In looking through the code I can see that the DataTable (i.e. Collection) that's created in the code is defining each column as Text. I think that's because it's possible for cells in the same Excel column to actually have different data types/formats. However, we can't do that with a DataTable, so we settled on making everything Text. I'm just not sure why it's dropping the formatting of your data.

One thing you can try, is making a small change the function in the Global Code that's populating the DataTable. Pictured below is the code of the GetWorkSheetUsedRangeAsDataTable function.

Try changing the highlighted line to this:

Dim value = range(i, j).Text

Cheers,
Eric

Re: Get Collection as CSV without header

NiklasFranke — Thu, 24 Nov 2022 14:00:45 GMT

@ewilson

I upated the Version from 10 to 10.0.2 but i that were just chanings for the Import CSV VBO which was btw. super super helpfull for me. Now i can easely change the Encoding in the VBO. Before i had to duplicate the main VBO and change the code, thanks for that,

I dont want to change the Global code, bcause of my Coworkers but my Soulution with the second predefined Collection worked for me.

Thank you again for all the help!