Support for Excel extractor in Data Platform

[#1]

The 5.5.66 Data Platform patch release includes an Excel extractor that can extract data from a predefined input spreadsheet and convert it into a BDNA consumable zip file. 

• Entering your data into Input_Spreadsheet.xlsx
• Running the Excel Extractor in Admin Console

IMPORTANT: If the running platform is missing Microsoft Office, the 64-bit Microsoft Access Database Engine driver must be installed on the machine before running this extractor. You can download the driver here: Microsoft Access Database Engine 2016 Redistributable.

Entering your data into Input_Spreadsheet.xlsx

Download the Excel Extractor ZIP file (ExcelExtractor-Document.zip) attached to this article, and extract the Excel spreadsheet included in that ZIP file. Then, use the spreadsheet (Input_Spreadsheet.xlsx) to add your raw data dollowing these rules:

• Do not change the sheet and column names. The predefined sheet names are System-OS, Software, and Hardware.
• There should not be blank rows between rows containing data.
• The mandatory attribute for all predefined sheets is Resource ID.

The table below provides additional guidance:

	System-OS	Software	Hardware
Required data	System data Operating system data	Add/remove data	Computer system data CPU data
Recommended attributes	Host name Operating System Name Operating System Caption	Display Name Publisher	Device Manufacturer Device Model
Resource IDs	Must not have duplicate Resource IDs	The same as those in System-OS	The same as those in System-OS
Other	System-OS should not be empty At minimum, the Resource ID column should be populated	If Software is empty, then no software data will be published	If Hardware is empty, then no hardware data will be published

After entering your data into the spreadsheet, follow the steps below to run the Excel Extractor.

Running the Excel Extractor in Admin Console

1. Click Create Process on the top right of the UI.

2. Click IT Discovery Tool, then select Next.

   

3. Click Other: Normalize Zip File, then click Next.

4. Click UPLOAD ZIP to select your edited Input_Spreadsheet.xlsx file.

5. Click Next to complete the upload.

[goody612]

Is there an excel template that goes along with this?  I see the document references one, but didn't see it within the zip file.

[JdeGuzman]

Hi @goody612,

We do apologize about the confusion there if it was a little bit unclear. However, when we mentioned a "predefined input spreadsheet", I have confirmed that this is actually a spreadsheet that Customers have to create on their own.

If you do need some guidance, I believe there is details about the requirements within the Word document attached to the Knowledge Base article.

Many thanks,
Andrew

[JdeGuzman]

Hi @goody612,

Our Knowledge Base article has now been updated.

This should now have the input spreadsheet attached to it.

Apologies for the confusion!

Many thanks,
Andrew

[juanr1vargas]

Hello, 

Which template should we use for a csv file ?

Thank you.

[DSood1]

@juanr1vargas  please use the Input_Spreadsheet file in the attached zip as a template.

[juanr1vargas]

Thank you @DSood1,.

I just wanted to be clear since the KB title reference support for CSV extractor, but the template is an excel spreadsheet. The excel spreadsheet contain multiple sheets which it is not possible with a csv file so it may be misleading to say there's support for CSV when that may not be the case. 

Can you clarify for me?

Thank you.

[GopalRaghvani]

Hello @JdeGuzman 

 This is a great feature. I use this to import Network devices in Data Platform.

How can I update field "Is Active?" i.e. when a Network device has been decommissioned

Thanks

[goody612]

What IT Discovery Tool do we use for this?  "Other Normalize Zip"?  Or is there something else?

[GopalRaghvani]

I use "Other Normalize Zip" as a way to import Network inventory. I have not integrated Network Tools to Data Platform, due to complexity. Extracting Network Data to an CSV file is an easy way to get the data imported.

[goody612]

Did you have to zip the file first?

[GopalRaghvani]

You use a csv files template.

_______________________________________________________________________________
Unencrypted electronic mail is not secure and may not be authentic.
If you have any doubts as to the contents please telephone to confirm.

This electronic transmission including any attachments is intended only
for those to whom it is addressed. It may contain copyright material or
information that is confidential, privileged or exempt from disclosure by law.
Any claim to privilege is not waived or lost by reason of mistaken transmission
of this information. If you are not the intended recipient you must not
distribute or copy this transmission and should please notify the sender.
Your costs for doing this will be reimbursed by the sender.

We do not accept liability in connection with computer virus, data corruption,
delay, interruption, unauthorised access or unauthorised amendment.
_______________________________________________________________________________

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

[goody612]

Interesting it prompts me to use a zip or .xlsx file.  If I give it the .xlsx file it errors out with "Spreadsheet Data Validation Fail".  Trying to just test import one system, and only filling out the first tab with the system id and system name.  Unless there are other mandatory fields, but seemed like I could leave everything else blank.  

[GopalRaghvani]

@goody612 Download ExcelExtractor-Document.zip, listed in main post. Do not change the headers.

Note: The yellow header columns must have a value. Resource ID is the Unique identifier.

[goody612]

@GopalRaghvani Does that apply for software as well?  I just want to upload a system without any of the software and hardware data.  According to the documentation it says to just leave them blank if there is no data.  Still getting validation errors even with data in all of the yellow highlighted fields, but I'm sure I just missing something silly.  Thanks for the assistance.

[GopalRaghvani]

Hi, I don’t update the software. Try with 1 hardware, update both tabs. Also try leaving the sample data in. Do not change any headers.

Get Outlook for iOS<>

_______________________________________________________________________________
Unencrypted electronic mail is not secure and may not be authentic.
If you have any doubts as to the contents please telephone to confirm.

This electronic transmission including any attachments is intended only
for those to whom it is addressed. It may contain copyright material or
information that is confidential, privileged or exempt from disclosure by law.
Any claim to privilege is not waived or lost by reason of mistaken transmission
of this information. If you are not the intended recipient you must not
distribute or copy this transmission and should please notify the sender.
Your costs for doing this will be reimbursed by the sender.

We do not accept liability in connection with computer virus, data corruption,
delay, interruption, unauthorised access or unauthorised amendment.
_______________________________________________________________________________

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

[goody612]

Can this be used to import items such as printers, phones, etc?