Importing an Excel File into a Data Structure?

Unknown

Importing an Excel File into a Data Structure?

Unknown

Hi all,

Im trying to add an entire excel file into a Decisions data structure and Im currently able to send one object through by using the import excel or csv step. I feel like I should use a looping step here to get all the values in, but is there a better way to get my excel data into the data structure? Thanks in advance!

Unknown

Hi there,

Youre correct in that you should be utilizing a loop, but for this situation we have a specific step for excel/csv files, For Each Excel Or CSV step (located in Integration > Excel and CSV > For Each Excel Or CSV Row)

In order to utilize this step, youll send in the excel file data and it will send out the values for the first row. You can then use a "Create Data Structure" step and select "Build Data" in the properties tab in order to map the values of the respective columns (known as Fields) to the fields of your data structure.

For example:

• My data ExcelObjects_DB data structure has two fields, a User_Name and User_Age.
• My CSV file contains the names in the first column and the age in the second.
• Ill go to the Create ExcelObjects_DB step and select "Build Data" for the Item to Create.
• Ill set the Users_Name to CurrentRow.Field01 and Users_Age to CurrentRow.Field02.
• If you run this flow, it will now go through the entire excel file and add it to the data structure.

Unknown

I have a related question, but with some added layers of complexity...

[ul][li]First, I have 1700 excel files with the same database layout in each, but with different data. So I need to import all of them in a batch process instead of having to run the process 1700 times.[/li][li]Second, each is stored as an attachment to an email, so each needs to be pulled from a .msg file[/li][li]Third, the emails are no longer stored in Exchange, but rather are stored as .msg files on a hard drive.[/li][li]Fourth, one of the columns stores cells as hyperlinks. I just want to pull in the Link Text (not the underlying URL). Is this possible? I would even be ok with pulling in the entire cell contents, then I could at least parse it, but when I try to pull in the hyperlink formula now, it just imports "0".[/li][/ul]
So I need to pull in 1700 .msg files, pull the excel attachment from each (fortunately its the only attachment to each email), and import the data into a single common database. Bonus points for importing the hyperlink text. Any thoughts on a good way to do this? If so, youre my hero. Thanks!

P.S. I have a flow that imports 1 file at a time. That part I have working, except for the hyperlink issue noted above.
[i]edited by Ross on 3/6/2020[/i]

Unknown

Hello Ross,

Currently, there isnt a way for us to process the msg files if they are saved locally, without converting the files beforehand. As a workaround, we would need to use this tool below:
[url=https://labs.rebex.net/mail-converter]https://labs.rebex.net/mail-converter[/url] to convert the msg files into a mime format. Afterwards, we could then retrieve the files stored in the directory. Once the files are in Decisions, then we will be able to process the excel files from there. For future reference, Ive submitted a feature request in order for us to retrieve attachments from email/msg files if they are saved locally.
[i]edited by anthony.ben@decisions.com on 3/9/2020[/i]