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.

[NiklasFranke]

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:

[NiklasFranke]

@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

[NiklasFranke]

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

[ewilson]

@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

​

[NiklasFranke]

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