{"id":23813,"date":"2024-02-15T14:53:34","date_gmt":"2024-02-15T22:53:34","guid":{"rendered":"https:\/\/docs.extraview.com\/v25\/book\/merging-data-microsoft-excel-files-1\/"},"modified":"2025-02-06T18:29:20","modified_gmt":"2025-02-07T02:29:20","slug":"merging-data-microsoft-excel-files-1","status":"publish","type":"page","link":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/","title":{"rendered":"Merging Data with Microsoft Excel Files"},"content":{"rendered":"<p>There can be a need to use ExtraView data for reports that cannot be created within ExtraView, but can be created within Microsoft Excel. This feature extends ExtraView so that you can harness all of Microsoft Excel&#8217;s capabilities in conjunction with any subset of ExtraView data. For example, you might want to use ExtraView data within a pivot table, or you may want to generate charts that are outside the scope of ExtraView&#8217;s own charting capabilities.<\/p>\n<p>This feature works with Column reports, Summary reports and Matrix reports.<\/p>\n<p>The procedure to merge ExtraView data with an Excel spreadsheet\/workbook is as follows:<\/p>\n<h3>\n\tCreate the Excel sheet that contains the ExtraView data<\/h3>\n<p>Create an individual sheet which will be used to hold the ExtraView data. This will most often be empty of data, but the columns should be formatted as follows to receive data of the correct type. The columns may also contain Excel formulae. The ExtraView data is merged with the sheet and only overwrites values, not the formulae.<\/p>\n<ul>\n<li>\n\t\tAll ExtraView text type fields do not require any formatting in the sheet<\/li>\n<li>\n\t\tExtraView date and day display types will be placed in columns in the sheet as seconds since the epoch<\/li>\n<li>\n\t\tExtraView currency and numeric display types will be placed in columns in the sheet as plain numbers<\/li>\n<li>\n\t\tIt is not valid to send buttons, image and document display types to the Excel sheet<\/li>\n<li>\n\t\tSave the workbook.&nbsp; It must be saved with an extension with an extension of <strong>.xlsx<\/strong>.&nbsp; The old style <strong>.xls <\/strong>files are not supported<\/li>\n<\/ul>\n<p>The following screenshot show how it might look, <em>after ExtraView populates the data into this sheet.<\/em> Again, the sheet should be empty at this stage:<\/p>\n<p>\n\t<img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ug\/querying_reporting\/excel-data.png\" style=\"width: 90%;\" \/><\/p>\n<p><cite>The Excel data sheet<\/cite><\/p>\n<h3>\n\tCreate the remainder of the Excel workbook<\/h3>\n<p>Create an additional sheet, or sheets, within the same Excel spreadsheet, which contain the reports or other Excel objects. These should source the ExtraView data by referencing a data range within the first sheet you created. An example pivot table might look like this:<\/p>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ug\/querying_reporting\/excel-pivot.png\" style=\"width: 90%;\" \/><\/p>\n<p><cite>The Excel output report<\/cite><\/p>\n<h3>\n\tSave the spreadsheet<\/h3>\n<p><strong>Note that the spreadsheet must be saved as type XLS, not type XLSX. This is due to a limitation in third-party utility software used to manage the Excel spreadsheet within the ExtraView server.<\/strong><\/p>\n<h3>\n\tUpload the spreadsheet to ExtraView<\/h3>\n<p>Now, you upload the saved spreadsheet into ExtraView. Create a new Column report, then select <strong>Microsoft Excel (Merge with uploaded file)<\/strong> as the output format. Most of the report creation is identical to creating other column reports.<\/p>\n<p><img decoding=\"async\" src=\"\/v25\/extraview-media\/images\/ug\/querying_reporting\/excel-upload-template3.png\" style=\"width: 90%;\" \/><\/p>\n<p><cite>Creating the report<\/cite><\/p>\n<ul>\n<li>\n\t\tClick the button to upload the Excel workbook \/ spreadsheet. This takes a copy of the spreadsheet from your local computer and places it in the database on the ExtraView server<\/li>\n<li>\n\t\tThe titles to all the sheets contained within the Excel workbook are displayed in the select list. Choose the sheet that is to be the destination for the ExtraView data<\/li>\n<li>\n\t\tThe default is that the ExtraView data will be placed at the top left-hand corner of the sheet, beginning with cell <strong>A1<\/strong>. You may alter the beginning cell by placing the data at any other row and column in the sheet<\/li>\n<li>\n\t\tUse the checkbox <strong>Include Column Titles in Sheet<\/strong> to indicate whether you want the column titles from the ExtraView data to be placed in the sheet. Note that for most purposes you want the column titles to match up with the titles as they will be used in the other sheets inside the Excel workbook. The matching is case sensitive<\/li>\n<li>\n\t\tThe checkbox <strong>Use Template Format<\/strong> gives control of the data formatting within the spreadsheet that is output. If this box is not checked, then ExtraView&#8217;s field formats are used. If this is checked, then the formats of the data within the spreadsheet are observed and used. This has uses to control items such as the format of dates in the spreadsheet. You might want to use the format mandated by the cell formats in the spreadsheet, or you might want to use the user&#8217;s date format as specified by their personal options.<\/li>\n<li>\n\t\tSelect the columns for the report that are to be used to fill in the Excel data sheet<\/li>\n<li>\n\t\tApply any filters required<\/li>\n<li>\n\t\tSave the report in the normal way.<\/li>\n<\/ul>\n<h3>\n\tRun the report<\/h3>\n<p>When you run this report, ExtraView will take the data returned by the query, and populate the Excel sheet. The report is then downloaded for you, in the form of a standard Excel spreadsheet.<\/p>\n<h3>\n\tTips<\/h3>\n<ul>\n<li>\n\t\tWrapping text within Excel cells &#8211; it is recommended that you highlight all rows within your spreadsheet and set the text wrapping option before saving and uploading to ExtraView.&nbsp; If you do not do this, then you have to double-click within the cell in the downloaded report before the text wrapping works<\/li>\n<li>\n\t\tCustom date formats&nbsp;&#8211; it is not recommended that you set your personal options within ExtraView to use a custom date format.&nbsp; Excel does not understand all the possible custom date masks that can be used<\/li>\n<li>\n\t\tExtraView can summarize on a significantly larger number of rows and \/ or columns (eight) within Matrix and Summary reports than Excel.&nbsp; Excel cannot produce charts with more than 3 summarization levels.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>There can be a need to use ExtraView data for reports that cannot be created within ExtraView, but can be created within Microsoft Excel. This feature extends ExtraView so that you can harness all of Microsoft Excel&#8217;s capabilities in conjunction with any subset of ExtraView data. For example, you might want to use ExtraView data&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":23802,"menu_order":10,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"_lmt_disableupdate":"no","_lmt_disable":"","_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"class_list":["post-23813","page","type-page","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Merging Data with Microsoft Excel Files - Product Documentation<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Merging Data with Microsoft Excel Files - Product Documentation\" \/>\n<meta property=\"og:description\" content=\"There can be a need to use ExtraView data for reports that cannot be created within ExtraView, but can be created within Microsoft Excel. This feature extends ExtraView so that you can harness all of Microsoft Excel&#8217;s capabilities in conjunction with any subset of ExtraView data. For example, you might want to use ExtraView data...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Product Documentation\" \/>\n<meta property=\"article:modified_time\" content=\"2025-02-07T02:29:20+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/\",\"url\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/\",\"name\":\"Merging Data with Microsoft Excel Files - Product Documentation\",\"isPartOf\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\"},\"datePublished\":\"2024-02-15T22:53:34+00:00\",\"dateModified\":\"2025-02-07T02:29:20+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/docs.extraview.com\/v25\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ExtraView 25\",\"item\":\"https:\/\/docs.extraview.com\/v25\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"End User Guide\",\"item\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Reporting\",\"item\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/\"},{\"@type\":\"ListItem\",\"position\":5,\"name\":\"Common Report Functions\",\"item\":\"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/\"},{\"@type\":\"ListItem\",\"position\":6,\"name\":\"Merging Data with Microsoft Excel Files\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"name\":\"ExtraView Product Documentation\",\"description\":\"ExtraView Documentation\",\"publisher\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\",\"name\":\"ExtraView Corporation\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"contentUrl\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"width\":512,\"height\":512,\"caption\":\"ExtraView Corporation\"},\"image\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Merging Data with Microsoft Excel Files - Product Documentation","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/","og_locale":"en_US","og_type":"article","og_title":"Merging Data with Microsoft Excel Files - Product Documentation","og_description":"There can be a need to use ExtraView data for reports that cannot be created within ExtraView, but can be created within Microsoft Excel. This feature extends ExtraView so that you can harness all of Microsoft Excel&#8217;s capabilities in conjunction with any subset of ExtraView data. For example, you might want to use ExtraView data...","og_url":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/","og_site_name":"Product Documentation","article_modified_time":"2025-02-07T02:29:20+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/","url":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/","name":"Merging Data with Microsoft Excel Files - Product Documentation","isPartOf":{"@id":"https:\/\/docs.extraview.com\/v25\/#website"},"datePublished":"2024-02-15T22:53:34+00:00","dateModified":"2025-02-07T02:29:20+00:00","breadcrumb":{"@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/merging-data-microsoft-excel-files-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/docs.extraview.com\/v25\/"},{"@type":"ListItem","position":2,"name":"ExtraView 25","item":"https:\/\/docs.extraview.com\/v25\/"},{"@type":"ListItem","position":3,"name":"End User Guide","item":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/"},{"@type":"ListItem","position":4,"name":"Reporting","item":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/"},{"@type":"ListItem","position":5,"name":"Common Report Functions","item":"https:\/\/docs.extraview.com\/v25\/extraview-25\/end-user-guide-1\/reporting-1\/common-report-functions-1\/"},{"@type":"ListItem","position":6,"name":"Merging Data with Microsoft Excel Files"}]},{"@type":"WebSite","@id":"https:\/\/docs.extraview.com\/v25\/#website","url":"https:\/\/docs.extraview.com\/v25\/","name":"ExtraView Product Documentation","description":"ExtraView Documentation","publisher":{"@id":"https:\/\/docs.extraview.com\/v25\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/docs.extraview.com\/v25\/#organization","name":"ExtraView Corporation","url":"https:\/\/docs.extraview.com\/v25\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/","url":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","contentUrl":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","width":512,"height":512,"caption":"ExtraView Corporation"},"image":{"@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/"}}]}},"taxonomy_info":[],"featured_image_src_large":false,"author_info":{"display_name":"carl.koppel","author_link":"https:\/\/docs.extraview.com\/v25\/author\/carl-koppel\/"},"comment_info":0,"_links":{"self":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/23813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/comments?post=23813"}],"version-history":[{"count":0,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/23813\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/23802"}],"wp:attachment":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/media?parent=23813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}