Write dictionary to excel

Help
1

Hello -

I’m reading a data table, creating a dictionary for all non-null fields, then writing a workbook using the dictionary.

I’m having two problems. For one, my dictionary is not skipping null fields. I wasn’t having this problem yesterday and I can’t quite seem to figure it out. I’ve tried “isNot Nothing” and does not “Equal(”“)”, but neither are working now.

Also, when I’m writing the new workbook from the dictionary, I’d like to write it like a normal table. So write A1 as the key, with A2 as the value, then continue down the rows with more key and value pairs. At the moment, I can only get it to write the key and value in the same cell with brackets around them.

WriteDictionary2.zip (20.8 KB)

Any assistance is much appreciated!

Thanks

1 Like
2

Hi there.

I have not looked at your sample, but can suggest an approach you could take.

Build Data Table // with 2 columns to add the dictionary to
For each item In dict.Where(Function(x) x.Value.ToString.Trim<>"").ToArray
    Add Data Row // in RowArray property use { item.key.ToString, item.Value.ToString }

Write Range // with datatable

Hope this is helpful.

Regards.

1 Like
3

Hi Clayton -

Thanks for the help! I tried it out but I’m struggling with the Add Data Row. Any suggestions?

image
image1301×597 78.1 KB
WriteDictionary2.zip (20.8 KB)

4

I don’t think your attachment has the correct .xaml file.

But judging by where the validation error is, make sure you have the correct number of columns in the Build data table and that you have the right TypeArgument for the For each. Seeing what the error says would help solve it though.

Regards.

5

I’m really sorry about that. This should have it.

WriteDictionary3.zip (11.2 KB)

6

Yeah, I’m not sure, cause my brain isn’t working right now lol. However, I switched it to using the .Keys as an array and got it to work.

Here are the changes you’ll need:
You are using a dictionary inside a dictionary so I included your other key, in_CIF

image

Need to change the Type of the For each to String cause the keys are strings.
Used out_Dict(in_CIF.ToString)(key).ToString as the value

image

Regards.

1 Like