Excel Get Data From Active Directory

[Tarja Hempton]

Iam new to Acumatica. I am trying to use Active Directory user to refresh the excel file generated by a Generic Inquiry. The authentication fails. It only woks with a local user. Has anyone had this issue and is there a solution for this?

An alternative is to use Velixo -- it has full support for AD and authentification methods supported by Acumatica, and includes capabilities designed to make it easier and faster to work with Acumatica inquiries and Acumatica data from Excel.

Thank you @Gabriel Michaud! I reached out to Velixo. We are using Jamis Prime ERP (it used Acumatica). My hope is that Velixo solution would work. From their website, it looks like it has great capabilities in addition to just refreshing data using AD or Azure AD.

Thank you @BenjaminCrisman! I tried connecting via Organizational account and I am getting the error that it is not supported. I am using a cloud version so the connection is via Azure AD. Thanks again.

Hello and thanks for having me on my first post. I am having difficulty writing what seems to be a simple script but proving a lot harder. HR simply updates an XLSX sheet (She prefers it to be in Excel) so I found the PS command that can grab and import it (import-excel) - and it takes each object and I can put it into a variable and view it like below with only those fields... Ultimately what i'm trying to accomplish is to use just the "business email" and update the manager using the "Manager Electronic Address" field.

I am trying to automate everything from the XLSX and schedule it to run every week so that we can simply dump the XLSX file into a directory and have it update the manager field automatically. The script below is a simple way of how I wanted to accomplish it but it is not working.

I finally was able to get this working... I'm sure there are a lot more ways of doing this shorter, but this way works for me... Import-Excel works really well but the problem was manipulating that email address attribute and converting it to SamAccountName.

Yet, if you are saying the HR folks are just dumping the file, and you want a process to run the above script, then set a FileSystemWatcher or a scheduled task to monitor the folder that will trigger the script execution.

The secretary at my work uses a spreadsheet that lists names, phone numbers, and locations of employees to look up info if someone asks. All this info is located in AD, but the problem is that we are a rapidly growing company, and people change locations, job titles, and sometimes phone numbers quite often, so her spreadsheet is frequently out of date. I'm a low level tech, but my boss suggested to me that I should try and put together a batch script of some sort that she can run from her computer that goes into AD, pulls this info into a csv, and then throws that into an excel document.

I'm not too familiar with scripting (did a bit of python in school, but not a huge amount), so I guess I'm just looking for some pointers in the right direction. I pretty much get exactly what the script needs to do, I'm just not sure what commands are out there to interact with AD and pull info from it. Is it even possible to do this with a batch script? And would she need certain access into AD to run it from her computer?

I can tell you how I solved a similar issue at a former employer. We were using Windows only to host AD and Exchange, and all the other servers in the company ran FreeBSD, so I developed our export automation using an open source tool called adtool which acts as an Active Directory client, and spits out lists or individual attributes. With adtool, after you've configured its access to your AD server per its documentation, you'd pull the basic list with a shell command like:

This bash script is intended to send data to stdout, so it can be redirected to a file that's on a server or gets copied to your staff's workstation for import into excel. Note that it's not perfect. As it stands, it doesn't escape double quotes that might exist within field names. You can account for that yourself if it's a risk for you.

I'm afraid I don't have an AD server to test this with, so I can't hand you a complete solution, but if you decide to go this route and build your automation in Linux or Unix, I'd be happy to help you with the necessary text processing once I see the output of the basic adtool commands whose data you're parsing.

N.B.: You can probably also pull any data you want out of Active Directory using any LDAP client (like OpenLDAP which is available for almost every distro of linux and BSD). Adtool is just a convenience.

If the secretary has Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone she can use Excel Power Query to pull that information directly into Excel from Active Directory. From the Excel Power Query menu option you choose "From Other Sources" and then choose "From Active Directory". The data that is required can be selected from the Power Query editor to create a report that would be refreshable at any time. No additional rights should be required.

This tutorial explains how to export data to excel from Active Directory using LDAP Admin Tool; To export data first connect to the active directory server (To create a new connection please see Connecting to Active Directory remotely )

To connect:

If you check this box the export process will automatically format the dateTime to local time zone date time. The default format is EEE MMM dd yyyy HH:mm:ss 'GMT'Z for e.g ( Sate Apr 12 2008 20:32:52 GMT -0500) . You can modify the date time format by clicking the DateTimeFormat Link

In today's interconnected business environment, integrating Active Directory with Excel has become a cornerstone for enhancing productivity and security within an organization. By leveraging the powerful capabilities of Active Directory, users can streamline connections, manage resources with greater efficiency, and bolster security measures. This integration facilitates the setting of precise user permissions and authenticates users upon login, ensuring that sensitive data is accessed responsibly. On this comprehensive page, we'll delve into the myriad of reasons why integrating Active Directory with Excel is pivotal, the essential components needed for setup, step-by-step instructions on how to marry these two powerful tools, and diverse use cases that demonstrate the practical benefits of this integration. Furthermore, we'll provide guidance for troubleshooting common issues and answer frequently asked questions to ensure a seamless experience with Active Directory integration in Excel.

Whether you are looking to import Active Directory data into spreadsheets with ease, connect to SQL servers for advanced data management or utilize other tools like LDAP and SSO, the integration of Active Directory with Excel opens up a world of possibilities. As we explore this topic, we will highlight the use of the CData ODBC driver for Active Directory, which plays a crucial role in linking Excel with other applications and executing queries with parameters. Our goal is to empower you with the knowledge to harness the full potential of Active Directory within the familiar environment of Excel, paving the way for a more secure, efficient, and integrated data management system.

Active Directory integration with Excel can be achieved through a couple of distinct methods. These methods enhance the capabilities of Excel by allowing users to connect to, query, and incorporate data from Active Directory. Depending on the version of Excel you are using and your specific needs, you may choose one method over another for optimal results.

The first method involves using a query to directly pull data from Active Directory into Excel. This can be done in Excel 2016, which provides a 'From Active Directory' command that may not be available in all editions. For users of Excel 2010 and Excel 2013, the Microsoft Power Query plug-in for Excel can be downloaded and used for this purpose. Once the data is imported, Excel stores the extraction, transformation, and loading steps as an M query within the workbook, and the data can be configured to refresh automatically.

The second method is utilizing the Get & Transform feature, also known as Power Query. This tool allows for the importation of Active Directory data into Excel and Access. It also provides the capability to manage and restrict the ability to query Active Directory, which can be controlled through the use of a security group. This feature is particularly useful for managing data transformations and automating the data integration process.

When integrating Active Directory with Sourcetable, organizations can leverage the power of live data synchronization from a variety of apps and databases. This real-time data pulling capability ensures that directory information is always current and accurate, offering a significant advantage over the static nature of Excel spreadsheets. The automated data collection process minimizes manual entry errors and saves valuable time.

Sourcetable's user-friendly spreadsheet interface allows for intuitive querying and manipulation of data, making it accessible for users familiar with traditional spreadsheets. However, unlike Excel, Sourcetable enhances business intelligence by providing automation features that streamline complex workflows. This integration not only boosts productivity but also enables more informed decision-making by providing dynamic, up-to-date insights into directory operations.

Active Directory automation aids network administrators and IT technicians with their day-to-day activities by automating a variety of simple to elaborate AD-related tasks, such as bulk creation, management and deletion of user accounts and other AD objects.

List object path

Retrieves a list of the paths for all Active Directory objects such as computers, users and groups, and then populate a dataset with the results and values. The results can be filtered by object, type or a filter string.

3a8082e126