How to Import XML Invoice to Excel and Save them all in a General Table
🟢LEARN EXCEL WITH ME IN THE COMPLETE EXCEL COURSE WITH CERTIFICATE: http://excelentejoao.com.br/sejaexcel...
In this free Excel video lesson, we'll learn how to import XML invoices into Excel and save them all in a General Table, automating the import process.
The XML Map feature in Excel is a powerful tool for working with structured data, such as that of an Electronic Invoice (NFe), in a more visual and organized way. It's located in the Developer tab.
The XML Map works like a translator or schema that links the elements of an XML file to the cells of a spreadsheet. Instead of simply opening the XML file as plain text, Excel uses this map to understand the hierarchy and relationships between the data, allowing you to organize it into columns and rows.
The main advantage is standardization. Once you map an XML file, you can reuse that same map to import other files of the same type. In other words, if you create a map for an NFe, it will work for all your other NFes, as they follow the same structure.
The process is divided into two main steps: creating the map and then importing the data.
Open the Developer Tab: If it's not visible, you can activate it in File, Options, Customize Ribbon.
Click Source or XML Source Mappings: On the Developer tab, look for the Source button. It will open a panel on the right side of your spreadsheet.
Add a Map: In the XML Source panel, click XML Mappings... and then click Add.
Select the sample file: Excel will ask you to select a sample XML file. Choose an NFe XML file you have. The program will read the file and generate a tree of elements.
Drag and Drop the Fields: Now, the panel will show the entire XML structure, with its elements and subelements. To create your map, simply drag and drop the elements you want to extract (such as nNF, dhEmi, vProd, vFrete, etc.) into the columns of your spreadsheet.
After creating the map, you can import data from the XML files directly into your spreadsheet.
Click Import: On the Developer tab, in the XML group, click the Import button.
Select File(s): Select the XML file(s) you want to import. You can select one or multiple files at once.
Confirm the Map: Excel will ask if you want to import using the map you just created. Confirm your choice.
Excel will read the files, identify the fields you mapped, and automatically populate the columns in your spreadsheet. If you imported multiple files, Excel will add new rows for each one, consolidating the data from all invoices into a single table.
This process greatly simplifies the analysis of NFes data, eliminating the need to manually copy and paste information from each XML file. Once the structure is mapped, importing new invoices becomes a task of just a few clicks.
#ExcelenteJoão #Excel #Dashboard