Go Back
Join Multiple Excel Spreadsheets and Output Excel
Publisher
: Lingk
Run In Lingk
Description
This recipe joins and aggregates multiple Excel worksheets / spreadsheets and ouput a single Excel spreadsheet. To use this recipe, upload the file(s) into the Local File connector for your Environment
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - CSV FILE READER # Recipe ID - # Recipe URL - https://app.lingk.io/a/10932/tf/17961 # Description - # This recipe joins and aggregates multiple Excel worksheets / spreadsheets and ouput a single Excel spreadsheet # To use this recipe, upload the file(s) into the Local File connector for your Environment # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - # Connectors - LocalFile # Data Flows - Single Direction # Connection Type - Excel # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure LocalFile credentials in your Environment before running this recipe # LocalFile Setup - https://help.lingk.io/en/articles/126-local-file-connector-reference ###### Start - LocalFile connectors ###### - name: excel1 type: localFileReader format: excelFormat1 properties: fileName: multiple_worksheets.xlsx - name: excel2 type: localFileReader format: excelFormat2 properties: fileName: rows_66k.xlsx - name: excelOutput type: localFileWriter format: excelFormatOutput properties: fileName: carsDatasetFinal.xlsx ###### End - LocalFile connectors ###### # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ # FORMATS specify how files should be processed as they are being read or written to formats: - name: excelFormat1 type: excel properties: worksheets: 'Subaru,Ford' useHeader: 'true' - name: excelFormat2 type: excel properties: worksheets: data useHeader: 'true' - name: excelFormatOutput type: excel properties: worksheets: final data useHeader: 'true' # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # STATEMENTS specify how the data should be processed while in memory statements: # - statement: (data) => select * from excel1 # - statement: print data # - statement: (dataCount) => select count(*) from excel1 # - statement: print dataCount # - statement: (data) => select * from excel2 # - statement: print data # - statement: (dataCount) => select count(*) from excel2 # - statement: print dataCount - statement: | (joinedData) => SELECT ucase(excel1.car) Manufacturer, excel1.model Model, excel1.year Year, count(excel1.car) `Number of Cars` FROM excel1 INNER JOIN excel2 ON excel1.car = excel2.car AND excel1.model = excel2.car_model AND excel1.year = excel2.car_year GROUP BY excel1.car, excel1.model, excel1.year - statement: print joinedData - statement: (dataCount) => select count(*) from joinedData - statement: print dataCount - statement: INSERT joinedData INTO excelOutput # Add more statements to convert, join, aggregrate, transform, and integrate your data
Union Multiple Excel Worksheets
Excel to JSON Conversion