{"id":3964,"date":"2022-01-27T00:02:19","date_gmt":"2022-01-27T08:02:19","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3964"},"modified":"2022-01-27T00:02:24","modified_gmt":"2022-01-27T08:02:24","slug":"import-data-from-a-folder-with-multiple-files-excel","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3964","title":{"rendered":"Import Data From a Folder with Multiple Files (Excel)"},"content":{"rendered":"\n<p>Excel for Microsoft 365&nbsp;Excel 2021&nbsp;Excel 2019&nbsp;Excel 2016&nbsp;Excel 2013&nbsp;Excel 2010<\/p>\n\n\n\n<p>Use Power Query to combine multiple files with the same schema stored in a single folder into one table. For example, each month you want to combine budget workbooks from multiple departments, where the columns are the same, but the number of rows and values differ in each workbook. Once you set it up, you can apply additional transformations as you would with any single imported data source and\u00a0then\u00a0refresh the data\u00a0to see results for each month.\u00a0\u00a0<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/3286ef6d-051e-4404-b216-57835c922dcb.png\" alt=\"A conceptual overview of Combining folder files\"\/><\/figure>\n\n\n\n<p><strong>Note<\/strong>\u00a0\u00a0\u00a0 This topic shows how to combine files from a folder. You can also combine files stored in SharePoint, Azure Blob Storage, and Azure Data Lake Storage. The process is similar.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"before-you-begin\">Before you begin<\/h2>\n\n\n\n<p>Keep it simple:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Ensure that all the files you want to combine are contained in a dedicated folder without extraneous files. Otherwise, all files in the folder and any subfolders you select are included in the data to be combined.<\/li><li>Each file should have the same schema with consistent column headers, data types, and number of columns. The columns do not have to be in the same order as the matching is done by column names.<\/li><li>If possible, avoid unrelated data objects for data sources that can have more than one data object, such as a JSON file, an Excel workbook, or Access database.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"import-from-text-csv-or-xml-files\">Import from text, CSV, or XML files<\/h2>\n\n\n\n<p>Each of these files follow&nbsp;a simple pattern, only one table of data in each file.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>Select\u00a0<strong>Data<\/strong>\u00a0>\u00a0<strong>Get Data<\/strong>\u00a0>\u00a0<strong>From File<\/strong>\u00a0><strong>\u00a0From Folder<\/strong>. The\u00a0<strong>Browse\u00a0<\/strong>dialog box appears.<\/li><li>Locate the folder containing the files you want to combine.<\/li><li>A list of the files in the folder appears in the &lt;Folder path> dialog box.\u00a0Verify that all the files you want are listed.<br><br><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/f8c5277f-e521-4820-8a54-14182af7249c.png\" alt=\"An example text import dialog box\"><\/li><li>Select one of the commands at the bottom of the dialog box, for example\u00a0\u00a0<strong>Combine\u00a0<\/strong>>\u00a0<strong>Combine &amp; Load<\/strong>.  <\/li><li>If you select any\u00a0<strong>Combine<\/strong>\u00a0command, The\u00a0<strong>Combine Files\u00a0<\/strong>dialog box appears. To change file settings, select each file from the\u00a0<strong>Sample File<\/strong>\u00a0box, set the\u00a0<strong>File Origin<\/strong>,\u00a0<strong>Delimiter<\/strong>, and\u00a0<strong>Data Type Detection<\/strong>\u00a0as desired. You can also select or clear the\u00a0<strong>Skip files with errors<\/strong>\u00a0checkbox at the bottom of the dialog box.<\/li><li>Select\u00a0<strong>OK<\/strong>.<\/li><\/ol>\n\n\n\n<p><strong>Result<\/strong><\/p>\n\n\n\n<p>Power Query automatically creates queries\u00a0to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"import-from-json\">Import from JSON<\/h2>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>Select\u00a0<strong>Data<\/strong>\u00a0>\u00a0<strong>Get Data<\/strong>\u00a0>\u00a0<strong>From File<\/strong>\u00a0><strong>\u00a0From Folder<\/strong>. The\u00a0<strong>Browse\u00a0<\/strong>dialog box appears.<\/li><li>Locate the folder containing the files you want to combine.<\/li><li>A list of the files in the folder appears in the &lt;Folder path> dialog box.\u00a0Verify that all the files you want are listed.<\/li><li>Select one of the commands at the bottom of the dialog box, for example\u00a0\u00a0<strong>Combine\u00a0<\/strong>>\u00a0<strong>Combine &amp; Transform<\/strong>.  <br><br>The Power Query Editor appears.<\/li><li>The Value column is a structured\u00a0<strong>List\u00a0<\/strong>column. Select the\u00a0<strong>Expand\u00a0<\/strong>\u00a0<img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/aac6522c-3652-47ae-9fe7-c564db55b74e.png\" alt=\"Expand column icon\">\u00a0icon, and then select\u00a0<strong>Expand to New rows.<\/strong>\u00a0<br><br><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/94e7f07e-0357-4f1c-bbcf-822f59f25fdd.png\" alt=\"Expanding a JSON List\"><\/li><li>The Value\u00a0column is now a structured\u00a0<strong>Record\u00a0<\/strong>column.\u00a0Select the\u00a0<strong>Expand\u00a0<\/strong>\u00a0<img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/aac6522c-3652-47ae-9fe7-c564db55b74e.png\" alt=\"Expand column icon\">\u00a0icon. A drop-down dialog box appears.<br><br><img decoding=\"async\" src=\"https:\/\/support.content.office.net\/en-us\/media\/2749a2cf-8712-456d-9084-601cf7de3bdb.png\" alt=\"Expanding a JSON Record\"><\/li><li>Keep all the columns selected. You may want to clear the\u00a0<strong>Use original column name as a prefix<\/strong>\u00a0check box. Select\u00a0<strong>OK<\/strong>.<\/li><li>Select all the columns that contain data values.\u00a0Select\u00a0<strong>Home<\/strong>, the arrow next to\u00a0<strong>Remove Columns<\/strong>, and then select\u00a0<strong>Remove Other Columns<\/strong>.<\/li><li>Select\u00a0<strong>Home\u00a0<\/strong>>\u00a0<strong>Close &amp; Load<\/strong>.<\/li><\/ol>\n\n\n\n<p><strong>Result<\/strong><\/p>\n\n\n\n<p>Power Query automatically creates queries\u00a0to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose.\u00a0<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"import-from-excel-or-access\">Import from Excel or Access<\/h2>\n\n\n\n<p>Each of these data sources can have more than one object to import. An Excel workbook can have multiple worksheets, Excel tables, or named ranges. An Access database can have multiple tables and queries.&nbsp;<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>Select\u00a0<strong>Data<\/strong>\u00a0>\u00a0<strong>Get Data<\/strong>\u00a0>\u00a0<strong>From File<\/strong>\u00a0><strong>\u00a0From Folder<\/strong>. The\u00a0<strong>Browse\u00a0<\/strong>dialog box appears.<\/li><li>Locate the folder containing the files you want to combine.<\/li><li>A list of the files in the folder appears in the &lt;Folder path> dialog box.\u00a0Verify that all the files you want are listed.<\/li><li>Select one of the commands at the bottom of the dialog box, for example\u00a0\u00a0<strong>Combine\u00a0<\/strong>>\u00a0<strong>Combine &amp; Load<\/strong>.  <\/li><li>In the\u00a0<strong>Combine Files<\/strong>\u00a0dialog box:<ul><li>In the\u00a0<strong>Sample File<\/strong>\u00a0box, select a file to use as sample data used to create the queries. You can either not select an object or select just one object. But,\u00a0you can&#8217;t select more than one.<\/li><li>If you have many objects, use the\u00a0<strong>Search<\/strong>\u00a0box to locate an object or the\u00a0<strong>Display Options\u00a0<\/strong>along with the\u00a0<strong>Refresh\u00a0<\/strong>button to filter the list.<\/li><li>Select or clear the\u00a0<strong>Skip files with errors<\/strong>\u00a0checkbox at the bottom of the dialog box.<\/li><\/ul><\/li><li>Select\u00a0<strong>OK<\/strong>.<\/li><\/ol>\n\n\n\n<p><strong>Result<\/strong><\/p>\n\n\n\n<p>Power Query automatically creates a query to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose.\u00a0<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"use-the-combine-files-command\">Use the Combine Files command<\/h2>\n\n\n\n<p>For more flexibility, you can explicitly combine files in the Power Query Editor by using the&nbsp;<strong>Combine Files<\/strong>&nbsp;command. Let\u2019s say the source folder has a mixture of file types and subfolders, and you want to target specific files with the same file type and schema but not others. This can improve performance and help simplify your transformations.<\/p>\n\n\n\n<ol class=\"wp-block-list\" type=\"1\"><li>Select&nbsp;<strong>Data&nbsp;<\/strong>&gt;&nbsp;<strong>Get Data<\/strong>&nbsp;&gt;&nbsp;<strong>From File<\/strong>&nbsp;&gt;&nbsp;<strong>From Folder<\/strong>. The&nbsp;<strong>Browse&nbsp;<\/strong>dialog box appears.<\/li><li>Locate the folder containing the files you want to combine, and then select&nbsp;<strong>Open<\/strong>.<\/li><li>A list of all the files in the folder and subfolders appears in the&nbsp;<strong>&lt;Folder path&gt;<\/strong>&nbsp;dialog box. Verify that all the files you want are listed.<\/li><li>Select&nbsp;<strong>Transform Data<\/strong>&nbsp;at the bottom. The Power Query Editor opens and displays all the files in the folder and any subfolders.<\/li><li>To select the files you want, filter columns, such as Extension or Folder Path.<\/li><li>To combine the files into single table, select the&nbsp;<strong>Content<\/strong>&nbsp;column that contains each&nbsp;<strong>Binary&nbsp;<\/strong>(usually the first column), and then select&nbsp;<strong>Home<\/strong>&nbsp;&gt;&nbsp;<strong>Combine Files<\/strong>. The&nbsp;<strong>Combine Files<\/strong>&nbsp;dialog box appears.<\/li><li>Power Query analyzes an example file, by default the first file in the list, to use the correct connector and identify matching columns.<br><br>To use a different file for the example file, select it from the&nbsp;<strong>Sample File<\/strong>&nbsp;drop-down list.<\/li><li>Optionally, at the bottom, select&nbsp;<strong>Skip files with error<\/strong>s to exclude those files from the result.<\/li><li>Select&nbsp;<strong>OK<\/strong>.<\/li><\/ol>\n\n\n\n<p><strong>Result<\/strong><\/p>\n\n\n\n<p>Power Query automatically creates a queries\u00a0to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose.<\/p>\n\n\n\n<p>Ref: https:\/\/support.microsoft.com\/en-us\/office\/import-data-from-a-folder-with-multiple-files-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel for Microsoft 365&nbsp;Excel 2021&nbsp;Excel 2019&nbsp;Excel 2016&nbsp;Excel 2013&nbsp;Excel 2010 Use Power Query to combine multiple files with the same schema stored in a single folder into one table. For example, each month you want to combine budget workbooks from multiple departments, where the columns are the same, but the number of rows and values differ <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3964\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[1078,1079],"class_list":["post-3964","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-import-data-from-a-folder","tag-import-data-from-multiple-files"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3964","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3964"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3964\/revisions"}],"predecessor-version":[{"id":3965,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3964\/revisions\/3965"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3964"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3964"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3964"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}