7 Replies Latest reply on Mar 10, 2017 2:23 PM by philmodjunk

    Filemaker merge csv files-db structure

    farmertx

      Hi

       

      I'm a filemaker newbie and have been successful making simple solutions for basic data entry and reporting.  I have an actual problem I'd like to tackle that is most likely above my pay grade.  Figure it will serve as a nice long frustrating way learn more about filemaker.

       

      We have two applications that output product ordered totals, one is a sql server db, the other visual fox pro.  Connecting directly to them is not an option due to vendors but I can export the reports to csv files.  Ideally I would like to import the files into filemaker pro and have it combine them and produce a report.  It would help cut down on mistakes as people transcribe these #'s daily and perform math on their own.

       

      One issue each database uses it's own item_id's and names due to limitations or auto naming conventions.  So it seems like filemaker would need to import the data, associate the imported item and it's id with a separate db to rename the import.  Basically, the final output would look like a report like the table below.

       

      ProductWholesaleRetail
      HouseTotal
      widget-11113

       

      I'm not sure whether filemaker is overkill for this, would excel be an easier less involved method?  I'm having trouble getting my head around what the table structure would look like.

       

      I assume a table for master item list of all the items with names use for the report.  Each item would have it's own unique ID and on import there would be some method (calculation?) to match the corresponding id from csv import.

       

      It gets difficult for me to understand the structure that follows after the import.  If the imported items get renamed based on lookup match of the first table would each csv import be put in a separate table?  And then another table to pull the imported data together for the combined report?  Filemaker would also have to know which csv file corresponded to the appropriate column i.e. wholesale, retail, house.  Thanks in advance for any help.

        • 1. Re: Filemaker merge csv files-db structure
          Jaymo

          There has to be some value that connects the two files to the same product records. If there is no product ID the name of the product is exactly the same or there is a SKU or something. If you can find something like this, the product records from both systems can be reported on together.

          • 2. Re: Filemaker merge csv files-db structure
            philmodjunk

            You've shown the output but not the input. What does the data in the CSV files look like? What data is in common between the data in the two files that might be used to merge data from a row in one with data in a specific row of the other?

             

            And I may not even be understanding what you want here?

            • 3. Re: Filemaker merge csv files-db structure
              farmertx

              Thanks for the replies.  Apologies if I wasn't clear.

               

              Both programs output item_id's and item_names but they are different and cannot be changed in the respective application to match.  That is why I mentioned the idea of a table in filemaker that would serve as a lookup of sorts.

               

              Here is first row of csv output of 1st application

               

              1     item count     display description     menu item id     sundry item total

              2     1                    carrot  14"                            1232             

               

              Here is first row of same item from the second application

               

              1     prodnum     description                    quantity

              2     cakecar14"     cake carrot 14"               2

               

              So they use different item #/prodnum numbers which can't be changed.  Descriptions work differently due naming conventions.  There is no common field name to use.  So what I would like to accomplish using the example above is:

               

              Product                    Wholesale          Retail          Total

              Carrot Cake 14"          2                         1                   3

               

              Obviously there would be a lot more products listed on a daily basis.

              • 4. Re: Filemaker merge csv files-db structure
                philmodjunk

                And you want the two samples above to produce a single record combining data from both?

                 

                Or would these be two different records after import?

                 

                Looks like you need to build a table where one field is "menu item id" and the other "prodnum". I am assuming that those are the fields that uniquely identify the product in each system. Once you have records in place that match these two values for a given product, you could use it to automate the process of linking this records. But note that you would have to set up the data in this table pretty much by hand as I see no way to set up the data in that table automatically.

                • 5. Re: Filemaker merge csv files-db structure
                  farmertx

                  philmodjunk

                   

                  philmodjunk wrote:

                   

                  And you want the two samples above to produce a single record combining data from both?

                  I assume a single record i.e. the final report would combine the imports.  That brings up another point of confusion.  The single record you mention would be a production report for a specified date.  That record would consist of multiple field entries comprised of the imported entries from the csv's.  That combined with a match/lookup table is what is eluding me.  It seems like there would be some relational tables etc., having trouble getting my head around it but it seems like it would be similar to line item entries in an invoice database.

                   

                  philmodjunk wrote:

                  But note that you would have to set up the data in this table pretty much by hand as I see no way to set up the data in that table automatically.

                  Meaning the data would have to be manipulated manually each time?

                  • 6. Re: Filemaker merge csv files-db structure
                    beverly

                    Yes, but not "lookup" as much as a JOIN table.

                    That way you can put the primary key fields from both into the same record. However that depends on you being able to tell what these would be for the "same" product.

                     

                    CSV1::id1_pk = join_table::firstID_fk

                                   join_table::secondID_fk = CSV2::id2_pk

                     

                    making that would need to be something that is manually done, as you say you have nothing that equates in the two files.

                     

                    This related "join_table" could show the matching records from the other two. It can have other fields as well, such as your "Total".

                     

                    is there any way for you to get better data?

                    beverly

                    • 7. Re: Filemaker merge csv files-db structure
                      philmodjunk

                      Still need to get this clear.

                      You are importing data from two sources in two formats.

                      What does one record (row of data) represent in each of these two files of data to be imported?

                       

                      Taking another look at your data, it looks like each row of data is like the "line item" of an invoice, purchase order, or production report. Each row specifies a qty and a product. Putting the two together would report that you have 3 14" carrot cakes produced, correct? (2 from the one import and 1 from the other).

                      Meaning the data would have to be manipulated manually each time?

                      No. You would set this up as a "Conversion table" that matches each product ID from one file of import data with the corresponding product ID from the other file. You'd only manually enter (or at least match) this data once for each product. This is to enable an automatic matching of data from one source with data from the other. This might be to merge the data into the same row, but I suspect that this is not actually what you need to do here, but that this should produce two rows of imported data.

                      That record would consist of multiple field entries comprised of the imported entries from the csv's.

                      I recommend against that. It can be a single row of data in a report if a single summary total for a given date is needed, but no real need to make it a single record--least not during the import of this data.