Write data from Array of string to Column in data table

[HimanshuSonawane]

Hi Everyone ,

Need your small help ,I am having three array of string and that i need to write it in column of excel files . The length of array may not be same everytime .

Consider Below Example : These are the array of string which i want to write in excel column

Input :-

Expected data table

[Pratik_Wavhal]

Hi @HimanshuSonawane

Below is the workflow for the same :-
MainPratik.xaml (10.8 KB)
New Microsoft Excel Worksheet.xlsx (8.6 KB)

Output :-

Mark as solution and like it

Happy Automation

Best Regards
Er Pratik Wavhal

[ppr (Peter Preuss)]

@HimanshuSonawane
with the help of LINQ we can do it in a very dynamic way.

lets bring all arrays together: arrSet ={arr1,arr2,arr3}

following statement is creating the row object arrays:

(From i In  Enumerable.Range(0,arrSet.Max(Function (x) x.Length)).toList
Let a =  Enumerable.Range(0,arrSet.length).toArray
Let ra = a.Select(Function (x) arrSet(x).ElementAtOrDefault(i)).Cast(Of Object).toarray
Select ra).toList 

• Line 1: looking for the longest array length and creating a sequence of indexes (0,1,2,…) for iteration
• Line 2: creating a sequence of the different indexes for the array members in arrSet
• Line 3: catching the array item if present or taking null for the different arrays in arrSet on the position i from current loop, creating an Array of Object (row Object array)
• line 4: return all row arrays as a list of Object()

And with a small modification we can fill up the result datatable as well:

(From i In  Enumerable.Range(0,arrSet.Max(Function (x) x.Length)).toList
Let a =  Enumerable.Range(0,arrSet.length).toArray
Let ra = a.Select(Function (x) arrSet(x).ElementAtOrDefault(i)).toarray
Select dtResult.Rows.Add(ra)).CopyToDataTable

Find demo XAML here:
CombineArrays_differentLengths.xaml (7.8 KB)

[HimanshuSonawane]

@Pratik_Wavhal ,Thank you so much but it will be great help if we can add this data to datatable instead of using write cell .Can u please suggest me how we can add this data to Datatable and then we write entire database once ?

[HimanshuSonawane]

@ppr Thank You so much bro.