4 Replies Latest reply on Jun 15, 2016 3:26 PM by swaroop.gantela

    How to create a consolidated table from multiple Excel/CSV/TXT files that have different headers?

    Alex Bojoaga

      How to create a consolidated table from multiple Excel/CSV/TXT files that have different headers; the files have combined millions of rows.

      For example, if File A has 3 columns (A, B, C) with 0.9M rows and File B has 4 columns (A', B', E, F) with 0.5M rows, I would like to obtain a table with 5 columns (A, B, C, D, E) and 1.4M rows.

       

      My current solution is to put the information in one Excel file with multiple sheets (one sheet for each file) and then use a Custom SQL to match columns and append rows (please see the code example below). I want to avoid this option because the file size will become very large.

       

      SELECT [FieldName1A] AS [Region]

      FROM [Table A$]

      UNION ALL

      SELECT [FieldName1B] AS [Region]

      FROM [Table B$]

       

      Thanks,