Get Collection as CSV without header

[NiklasFranke]

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.

[ewilson]

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

[NiklasFranke]

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!

[NiklasFranke]

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

[ewilson]

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
​

[NiklasFranke]

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

[ewilson]

@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

[NiklasFranke]

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

[NiklasFranke]

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)

​

[ewilson]

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