Loading Excel File into Data Lake

Hi All,

1. I am trying to Load Excel file into Data Lake. As Mike Kalinowski suggested, I have registered my "EXCEL" as "ANY" document type in Data Catalog, but there is no schema properties available for "EXCEL". How to achieve this.

Can anyone suggest on this.

Thanks in Advance.

Regards,

Rajini

Find more posts tagged with

Infor Data Fabric

Accepted answers

Kevin Heiman

You can flow the object as it is into data lake as you have defined here. It will be a binary file with no schema definition needed, as its schema is ANY.

You can retrieve the file using APIs or use a Push flow, and you will get back the binary excel file. You could also consider using IDM for this use case. For what you have stated, this is as you described above will do that.

If you want the file data value, you will need to transform it to NDJSON or DSV formats and then you would have a schema (single row format only) and you would write to data lake using that schema.

If the file is really a DSV file, and not a excel binary, then you can use the Data Catalog to generate the schema, and then flow it to data as that schema.

unknown

Yep, that's correct. While you can store Excel (.xls, .xlsx) files in Data Lake as raw files, they're not eligible to be queried today via Compass. We do have general long-term plans to incorporate desktop processing formats for querying via Compass but this isn't something we have short term visibility on.

If there's interest in seeing this feature, please submit an Enhancement Request. This is great for exposing the request to other users in the community and helps us identify what might be useful to focus on for shorter-term needs.

In the meantime, I would recommend processing the spreadsheet through a Python script into individual CSV files for delivery or looking at some other means of pre-processing the data prior to putting it into Data Lake if you need to query it.

All comments

unknown

I don't think ION have a parse to understand excel files. See if there are options available in script.

Kevin Heiman

You can flow the object as it is into data lake as you have defined here. It will be a binary file with no schema definition needed, as its schema is ANY.

If you want the file data value, you will need to transform it to NDJSON or DSV formats and then you would have a schema (single row format only) and you would write to data lake using that schema.

If the file is really a DSV file, and not a excel binary, then you can use the Data Catalog to generate the schema, and then flow it to data as that schema.

Rene Bottern

Why not create a .Net application that executes on a schedule that reads Excel files dropped into a specific Folder on one of your Network drives that will automatically convert your spreadsheet to a JSON File and save it in a place where it will be automatically picked up by your Data Lake instance?
That way you will allow your users to do this themselves without any intervention from you.
Sure, there is a bit of coding, but it shouldn't be too bad depending on what type of Spreadsheet formats you're trying to convert.

unknown

Hey Rajini,

What kind of use case are you building here? Are you looking to query via Compass SQL or simply use Data Lake as a file storage for things like batch-based integrations via ION?

unknown

Hi Kevin,

Thank you for the response.

As per your answer, no need to define schema for ANY formats. I used data flow to load the excel file into data lake(file connector to data lake) but throwing an error and I am not familiar with IDM.

We can achieve this by using data flow right?

Please correct me if I am wrong.

unknown

Hi Mike,

Thank you for the reply.

I used data flow to load the excel file into data lake(file connector to data lake) and as per my understanding, we cannot query in compass for excel files.

Kevin Heiman

Your requirement is to vague so we are struggling to help you. ION is capable of the scenario to you described as "... trying to Load Excel file into Data Lake..."

That can be interpreted literally for what it states, and the binary excel file can be loaded into Data Lake as an object of type ANY. ANY implies it has not defined structure so that it can be interpreted as data. ION is very capable of this.

What do you intend to do with the excel files in Data Lake? Just pull back binaries upon request through the APIs? Maybe associate a Property to some other Data point so there is a relationship?

I don't understand your reference to IDM. Where is IDM involved in this?

What error are you getting?

Other scenarios would be around a requirement of needing the excel data in a schema format that can be interpreted as data. The mentions by myself and others of converting to ND JSON or CSV before loading, or using a ION Data Flow Script to convert the excel file to NDJSON, or if the file is a excel output as CSV.

We offer training in Infor OS for ION and for Data Lake. You may want to consider those helping you.

unknown

Raghavender Hariharan

Hi,

Rather than loading a whole excel file into Datalake, you can now upload the data from your EXCEL file into Datalake using the below plugin which is published in Microsoft store by Infor Labs.

An Excel Add-In has been officially published in Microsoft AppStore. It has been published with the name "Infor OS Add-In for Excel". It can be accessed from - Find the right app | Microsoft AppSource . Attaching a few screenshots of the same for your reference.

Raghavender Hariharan

The listing is now in Marketplace. https://market.infor.com/en-US/apps/406377/infor-os-add-in-for-microsoft-excel/ . Please go through the UserGuide and the Guided Walkthrough from Marketplace. Thank you.

Ryan Gartman

This might not be the place to ask for help, but after clicking Authenticate in the add-in, selecting my .ionapi file, logging in, and granting permissions, the authentication window closes and the Add-In panel says "Failed to Sign In" and the Authenticate button turns red.

I tried to debug the authentication popup window and pause when it hits an exception.
The exception was: Unexpected end of JSON input for excel-win32-16.01.js (Line 25, Column 11456), which looks like it's something about trying to parse from window name. And the window name is "Infor OS Add-In for Excel - https://excel-addin-prd.azurewebsites.net/commands?code=[codehere]"

Does that give any indication what in the process I might've messed up?

steve mcenany

I have it working on my tenant, for me it was in the TokenAPI end point that caused it to accept the tenant sign in and then turn red. The Token endpoint needs to be "pu" + "ot" from the ionapi file from the BS service.. I had the "iu" + "ot" and it worked to exactly the same point as you. You might take a look at those settings in the API endpoint.

steve mcenany

Thanks for the great new tool for our team!

unknown

cannot wait to try this out!

Jaime Zepeda

Thank you, Raghu, for your diligent efforts to get us started on this new feature. I have been using the Excel AddIn for the last two days and it is faster than Compass for the queries I've executed.

This is a great way to get 2023 started!

Jaime

Raghavender Hariharan

Thanks Jaime for your feedback.. Please rate the tool in Microsoft store along with reviews if possible. That would be a helpful. Thank you...

unknown

Hi everyone.
We made a couple of changes to the Add-In, below are the details related to the same.

We introduced Limit and Offset which basically enables the user to download more than 100,000 records from the DataLake.
We are now checking the security roles of the logged in user and depending on these roles the access to various options (To IMS, To Data Lake, From Data Lake) is given to the user.

Additional details regarding this is available on the Instructions Page in the Add-In. Please feel free to reach out to us here, in case you have any questions/suggestions.

vinayak-tayshetye

Hi Everyone,
Has anyone come across the issue with excel add-in, where it says signed in Successfully, but does not give the 3 options to send data to IMS, Data lake and retrieve data from data lake?
I do have the required security roles as well it works well with other tenants.
Any help will be appreciated.
@rohit-bhrugumalla @raghavender-hariharan

unknown

I believe we need to further investigate this issue, can you please send an email to infor.labs@infor.com .
Thank you.

StefanTaganyi

I have the same problem as Vinayak. How was this fixed? I'll send an email to infor.labs@infor.com as mentioned above. @rohit-bhrugumalla @ragh

unknown

Hello Everyone,

After receiving feedback from various users regarding storage of frequently used queries, we're excited to announce a powerful new feature in the Infor OS EXCEL Add-In: "Add to Favourites." This feature is designed to streamline your workflow and enhance your user experience by allowing you to easily save and access your frequently used queries in the Microsoft EXCEL Add-In for Infor OS.

Key Benefits:

Effortless Access: With the "Add to Favourites" feature, you can now store up to 20 favourite queries
.
Personalized Efficiency: Tailor your workspace to your needs. By adding items to your favourites, you can improve your efficiency by reducing the time spent on writing repetitive queries.

Please feel free to use the new feature.

Additional details regarding this is available on the Instructions Page in the Add-In. Please feel free to reach out to us here, in case you have any questions/suggestions.

unknown

Hello Everyone,

As we try to make our product "Infor OS Excel Add-In" even better and user friendly. we are happy to announce that we have added few additional features in the Add-In.

Automatic Query Generation from Object Schema: The user can choose the required schema and the query is automatically generated. This feature is present in the 'View Favorites' option where the user can find the list of all schemas present in the tenant the user is logged into. The user can simply click on the required schema and the query is generated automatically for the user.
In the 'View Favorites' dialog box we have added an option for the user to give desired name to their query so that they can find the required query instantly without going through the entire list of queries.

Please feel free to use the new feature.

Additional details regarding this is available on the Instructions Page in the Add-In. Please feel free to reach out to us here, in case you have any questions/suggestions. Alternatively, you can mail us on infor.labs@infor.com for any kind of assistance.

unknown

Hello everyone,

First, we want to thank all our users for their continued support and valuable feedback. Based on your suggestions, we’ve implemented several enhancements to improve your experience with the Infor OS Excel Add-In. Here are the key updates:

Initial Load Feature: Users can now mark up to five queries as "Initial Load," which will be automatically executed upon login. This new feature, accessible under 'View Favorites,' saves time by eliminating the need to manually execute the same queries every time you log in.
Enhanced Security Role: A new security role has been introduced, giving Admins greater control. Admins can now restrict users from pushing data to the Data Lake and limit them to only viewing data. This helps ensure data integrity and provides more control over sensitive information.
Bulk Retrieval Capability: The Add-In now supports bulk retrieval, allowing users to download up to 10,000,000 records at once. This enhancement dramatically improves the efficiency of data retrieval for large datasets and reducing wait times.

These updates are aimed at improving efficiency, security, and providing a smoother, more intuitive user experience. We hope you enjoy these new features and look forward to your continued feedback!

Please feel free to use the new feature.
Please feel free to reach out to us here, in case you have any questions/suggestions. Alternatively, you can mail us on infor.labs@infor.com for any kind of assistance.

gsingh

Hi, Is the Excel-AddIn IFS role standard in MT or do we need to add it. I have the same issue as reported earlier , I have installed the add-in but do not see the radio buttons, I have restarted Excel and I have the other 2 roles, just not the Excel-AddIn role as yet.

Copyright © 2025 Infor. All rights reserved.