topic As long as none of the dataâ€¦ in Product Forum

What is the best way to paste collection into a csv file?

pranavred — Tue, 12 Feb 2019 19:52:00 GMT

What is the best way to paste collection into a csv file? Should I use the Word VBO?

I would have sworn one of…

AmiBarrett — Tue, 12 Feb 2019 20:48:00 GMT

I would have sworn one of the default utility object had a basic action for this, but I'm not finding it. Below is the version I wrote to accept custom delimiters using c#.

Inputs:
Collection - Collection
Delimiter - Text
Target - Text

StringBuilder sb = new StringBuilder();
string[] columnNames = Collection.Columns.Cast().Select(column => column.ColumnName).ToArray();
sb.AppendLine(string.Join(Delimiter, columnNames));
foreach (DataRow row in Collection.Rows)
{
	string[] fields = row.ItemArray.Select(field => field.ToString()).ToArray();
	sb.AppendLine(string.Join(Delimiter, fields));
}
File.WriteAllText(Target, sb.ToString());

You talking about the…

david.l.morris — Tue, 12 Feb 2019 22:52:00 GMT

You talking about the Utility - Strings action 'Collection to CSV'?

That sounds right - I…

AmiBarrett — Wed, 13 Feb 2019 00:29:00 GMT

That sounds right - I changed divisions at my company a while back, which also meant changing environments. This one doesn't seem to have that action for some reason. (Not a huge loss I suppose, when we already have an alternative.)

When you can write code like…

david.l.morris — Wed, 13 Feb 2019 02:08:00 GMT

When you can write code like you do @amibarrett, I wouldn't feel at a loss at all not having that action. Hahah!

Hahah @david.l.morris. That…

pranavred — Thu, 14 Feb 2019 01:08:00 GMT

Hahah @david.l.morris. That's exactly what I was thinking. @amibarrett has code solutions for every problem (which is amazing) I figured later this question was pretty dumb as I just wanted to paste a collection in csv file. I used Excel VBO to create instance, opened workbook, pasted the collection and closed the instance. I could use the code if there's no excel on the machine, but it worked out.

Hi and nice code! I addedâ€¦

MoDCore — Fri, 03 May 2019 15:28:00 GMT

Hi and nice code! I added this to the code object setup the inputs. When "checking" th code, it comes up with a handful of errors. Â Any ideas on this one? As would like to give this ago for a problem we have. Thanks MoD

As long as none of the dataâ€¦

AndreyKudinov — Sun, 05 May 2019 20:36:00 GMT

As long as none of the data contains delimiters/quotes or nulls (should not apply to bp)Â - that would do.

Did you add this to anâ€¦

AmiBarrett — Tue, 07 May 2019 19:13:00 GMT

Did you add this to an existing object? If so, is that object set up to run VB instead of C#? The above snippet is writtenÂ in C#.

I have a new object setup inâ€¦

MoDCore — Wed, 08 May 2019 12:01:00 GMT

I have a new object setup in C# Copied and pasted the snippet into a new code block, I'm recieving 5 errors currently when checking the code. Type: Error Action: Validate Description: Compiler error at line 1: The type or namespace name 'StringBuilder' could not be found (are you missing a using directive or an assembly reference?) Type: Error Action: Validate Description: Compiler error at line 1: The type or namespace name 'StringBuilder' could not be found (are you missing a using directive or an assembly reference?) Type: Error Action: Validate Description: Compiler error at line 7: 'System.Array' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type 'System.Array' could be found (are you missing a using directive or an assembly reference?) Type: Error Action: Validate Description: Compiler error at line 2: 'System.Data.DataColumnCollection' does not contain a definition for 'Cast' and no extension method 'Cast' accepting a first argument of type 'System.Data.DataColumnCollection' could be found (are you missing a using directive or an assembly reference?) Type: Error Action: Validate Description: Compiler error at line 10: The name 'File' does not exist in the current context Â Any thoughts on these? Â

I have now resolved thisâ€¦

MoDCore — Wed, 08 May 2019 18:01:00 GMT

I have now resolved this with adding in a selection of References and Namespace Imports. Â

FYI I've previously foundâ€¦

John__Carter — Thu, 09 May 2019 14:57:00 GMT

FYI I've previously found this kind technique (collection -> object array -> range) to be very effective for writing large collections into Excel. Dim xl, wb, ws As Object wb = Microsoft.VisualBasic.GetObject(ExcelFilePathInput) xl = wb.parent ws = wb.WorkSheets("Sheet1") Dim arr As Object(,) = New Object(CollectionInput.Rows.Count - 1, CollectionInput.Columns.Count - 1) {} For r As Integer = 0 To CollectionInput.Rows.Count - 1 Â Â Â Dim dr As DataRow = CollectionInput.Rows(r) Â Â Â For c As Integer = 0 To CollectionInput.Columns.Count - 1 Â Â Â Â Â Â arr(r, c) = dr(c) Â Â Â Next Next Dim c1 As Object = ws.Cells(2, 1) Dim c2 As Object = ws.Cells(CollectionInput.Rows.Count + 1, CollectionInput.Columns.Count) Dim rgÂ As Object = ws.Range(c1, c2) rg.Value = arr