10 Replies Latest reply: May 15, 2014 10:05 AM by mdyoung _ RSS

    Help combining two data rows

    mdyoung _

      Hello, Gurus.


      I have an Excel spreadsheet that I'm using for my input file which contains stock items on hand. Each product's detail is listed on two rows in the input file. The first line contains product number[/I], internal product number[/I], product name[/I], and vendor[/I]. The second line contains the quantity[/I], unit of measure[/I], unit price[/I], and extended price[/I]. To mimic my input data, just copy the following into an Excel spreadsheet and open with Monarch (I'm using v9 Pro). NOTE: /BThe following text block contains tab characters, so be sure to use the "Paste Special" feature and select "Text" so the data falls in the proper columns.


      ProductNumber     InternalProductNumber     ProductName     Vendor     Qty     UnitOfMeasure     UnitPrice     ExtendedPrice

      02.001.300     30021     Test product 1     1st Vendor                    

                          5     EA     123.45     432.1

      01.221.332S     2001123     Test product 2     2nd Vendor                    

                          14     CS     12.22     15.12

      11.223.002     11223233     Test product 3     1st Vendor                    

                          3     EA     637.87     798.33





      I want to be able to combine the two rows of each product's detail data into one row, but I'm not sure how to accomplish this task. If it makes any difference, I do plan on using DataPump (v9) to export the data into a SQL Server 2005 database; in addition, the process will be setup to use DataPump's file monitoring feature. Please note that I have absolutely  no control over the Excel input file's format because it is supplied to me from a 3rd-party vendor.



      The expected outcome would be as follows:


      ProductNumber   InternalProductNumber   ProductName       Vendor       Qty     UnitOfMeasure   UnitPrice   ExtendedPrice

      02.001.300        30021                 Test product 1    1st Vendor     5     EA                 123.45          432.10

      01.221.332S     2001123                 Test product 2    2nd Vendor    14     CS                  12.22           15.12

      11.223.002      11223233                Test product 3    1st Vendor     3     EA                 637.87          798.33



      Any help on this will be greatly appreciated.


      Many thanks ,



        • Help combining two data rows
          Data Kruncher

          Does every record have two rows? And when I pasted per your instructions, the Quantity, UnitOfMeasure, UnitPrice and ExtendedPrice values appear in the proper columns. Is that the correct layout?


          Rather than attempt some potentially tricky Monarch manipulations, is it feasible to instead manually "prep" the Excel file by just cutting the "off by a row" columns, and pasting the values up one row, so that they're all in line at the start of the Monarch/DP work?


          Then, upon opening the Excel file as a database source in Monarch, set the filter expression to:

          .Not. IsNull()[/code]to skip the empty rows in the Excel file.


          Though it does take a minimum of manual work instead of a fully automated solution, it would be quick work. How often do you receive the file?

            • Help combining two data rows
              mdyoung _

              Yes. Each product record does have two rows. If the ProductNumber, InternalProductNumber, ProductName, and Vendor appear on the first row with the Quantity, UnitOfMeasure, UnitPrice and ExtendedPrice on the second row in their respective column, yes the layout is correct.


              This file is received on random days at random times. No real set schedule. This is why I wanted it setup to process once the file was received. I'm not always notified as to when it's available.





                • Help combining two data rows
                  Joe Berry

                  If the file was a text file it could be done easily.  Trap the second line as the detail and the first line as the append.  That will get all data on one record and allow you to export to whatever you wish.


                  It can be done in Excel by deleting the data in the cells E2..H2 using the option to shift cells up.  If the file is always in the exact same format, this would be the best solution.

                    • Help combining two data rows
                      Olly Bond

                      Hello everyone,


                      Rather than introduce an extra stage of complexity by prepping the file in Excel, I'd simply open the file in Monarch as a Database, with all fields set to type Character to avoid any difficulties with Nulls, then export the table as fixed width text.


                      Then follow Joe's approach and work with this text input together with either a detail on the second line and an append on the 1st, or a detail on the first and a footer on the second, or a two-line detail template. If you choose the last option, it might be worth just checking the Page Break settings to ensure you don't have a nasty break.


                      Using the Batch File Generator from www.********************[/url] it would be easy to automate the process so that when the file arrived it could be processed with two clicks.


                      Best wishes,



                      • Help combining two data rows
                        Data Kruncher

                        A fully automated solution is possible with only two Monarch project files and as Joe mentions, it involves converting the Excel sheet to a text file.


                        In the first project, open the xls file as a database. Under the View options, clear any text used for null values. Now define a project export to create a fixed length text file from the Table window and save the model and project files.


                        Next, open the new text file and create a two line detail template to capture all of the fields in a single template. You can now export this as required for your final process. Again save your model and project files.


                        I'm certain that this process can now be duplicated in Data Pump.




                          • Help combining two data rows
                            Chickenman _

                            OP says, "in addition, the process will be setup to use DataPump's file monitoring feature"


                            I may be missing something fundamental here, but to my knowledge monitoring is not available for database files.


                            Would appreciate any feedback as we have created numerous workarounds to defeat this restriction.



                              • Help combining two data rows

                                You can have an input distribution when access/excel is used as database input in Data Pump (at least in V9). This allows the database to be monitored.

                                  • Help combining two data rows
                                    Chickenman _

                                    We have V9, so apparently the documentation is incorrect. :confused:



                                      • Help combining two data rows
                                        mdyoung _

                                        Thanks, everyone, for your replies. I applied Joe, Olly, and Kruncher's suggestions to resolve my issue. For those interested, I created two models. The first exported the Excel data to a fixed-width text file, and the other model extracted the data needed from the text file. I then setup two different DataPump processes in which both used the file monitoring feature to automate the tasks. I did run into a problem, at first, where the second process started while the first process was still creating the text file. Prerequisite errors occurred. I quickly resolved the issue by having the first process create the text file named as CREATING_TextFile.txt and then using the Move distribution to rename it to the file name that the second process is monitoring. All is working beautifully and without any human intervention at all..... at least in the filing\exporting processes. Downloading, however, is still manual..... but that's expected.


                                        Thanks again to all.



                          • Help combining two data rows
                            Grant Perkins



                            That all seems very strange to me, for an Excel spreadsheet file. Are you sure it is not a corrupt file?


                            Not being an Excel person (beyond basics) I would be tempted to export from Excel, using a fixed font to retain the format, to a formatted text file and then run a 'standard' Monarch model with 2 lines per detail record. Or some variation on that idea.


                            I'll leave it for Kruncher/Nick/Olly/Nigel and others to offer more Excel centric ideas. I don't think you are going to be to create the record in one pass doing a database read, but I could be wrong. Further, if what you are doing (it seems) is making one line from two, in effect, and sending the result to a database system with which Excel can interact directly where is the value on a Monarch/Data Pump solution over a direct transfer?





                            ETA: Beaten to the first response!