Data Mining out of an Excel Spreadsheet
Altair EmployeeHello,
I am importing an Excel Spreadsheet in monarch so I can get the information into the correct order so it can be uploaded into another system. However, many of the spreadsheets that I am getting has a "header" section much like a pdf would and I need to pull so information out of that to put in all of the detail lines. For example Rows 1 - 4 is the header information while rows 5 & 6 are the column headers and then everything after that contains the detail information that I need. What I would need to do is pull the information from rows 1 & 4 and add that to every detail line. Does anyone have a good way of doing that with converting the file to a pdf as I will be getting hundreds of these files a month.
Answers
-
Hello Brett,
You may open your excel file as database in Monarch Classic then export it as fixed text. Before exporting, you may want to adjust your column field lengths to account for differences in column widths of the different files. Then you can save model and project after creating an export. Once you have a project export, you can repeat this using different excel files as a source via Automator or Monarch Command lines.
You may now use the exported text file as a report input where you can use the Page Header template.
This is a 2-step solution though. Still wondering if you can do this in a single step...
HTH
Mela
0 -
Brett, if you're using Data Prep Studio, what you can do is create Calculated Fields using If() statements for the header data that you need from the top of the table, then use the "Ditto" function to fill in the values below, which ends up working like Append templates.
Once you've created all the columns you need and dittoed those values to the details of the spreadsheet, you can filter to remove the top rows.
0