14 Replies Latest reply on Mar 15, 2014 2:48 PM by erolst

    Excel import w/ complicated spreadsheet

    chronister

      Hello all, I am trying to import data into my tables from an excel spreadsheet. I am new with Filemaker, but am loving it already and will be purchasing the full version soon for 2 different organizations.

       

      Here is my predicament. The spreadsheet is from S&S Activewear and it is their catalog in XLS format. It is set up like this...

       

      SNS CodeWovensCatalog Page #Color CategorySize
      Range
      Pieces per CaseCase PriceDozen PricePiece Price
      46289Calvin Klein Non-Iron Dobby Pindot Shirt - 13CK0298ColorsS - XL24$25.74$29.60$33.46
      2XL24$26.42$30.38$34.35
      3XL24$28.56$32.84$37.13
      46389Calvin Klein Ladies' Non-Iron Dobby Pindot Shirt - 13CK0308ColorsS - XL24$24.31$27.96$31.60
      2XL24$24.78$28.50$32.21
      3XL24$26.19$30.12$34.05

       


      This is 2 products of the over 8000 products. The line that says Woven is actually the category Woven. There are others as well. There are multiple sizes for each item and muliple prices for each size.

       

      Does anyone have any ideas on how this could be imported or scripted?

       

       

       

      I have attached the excel file to this post for example. I am wanting to have a method to just import this and update all information so I can tie into this with the prcing and quoting modules I am building in Filemaker.

       

      Thanks,

       

      Nathan

        • 1. Re: Excel import w/ complicated spreadsheet
          PSI

          Nathan,

           

          I would setup a temp table and import the data there to mirror the spreadsheet.

           

          Script the record creation in the product table like this. (shorthand version)

           

          Got to Record/Request [ First ]

          Loop

          If [ not IsEmpty ( SNSCode )]

          Set Variable [ $Code ; SNSCode ]

          Set Variable [ $Wovens ; Wovens ]

          Set Variable [ $PageNbr ; CataloguePageNum ]

          Set Variable [ $ColorCat ; ColorCatagory ]

          End If

          "Script steps to Create Record In product Table"

          Go to Record/Request [  next ; exit after last ]

          End Loop

           

          I recommend using a method I call "create related records without leaving home", i know a bit verbose. you can download an example here.

           

          http://www.pueblo-systems.com/CreateRecords.zip

           

          John

          • 2. Re: Excel import w/ complicated spreadsheet
            keywords

            I reckon I would start by simply opening the spreadsheet file in FileMaker, thus converting it into a FM database. To my way of thinking it is easier to do that and then do some field renaming, deleting useless records if any (eg. ones that contain column names, etc). You can name fields to make them easier to recognise and then map correctly when you do the import into your real DB.

            • 3. Re: Excel import w/ complicated spreadsheet
              chronister

              I need this to end up being a completely automated solution aside from opening in Excel, and converting to CSV. If I could work with it in Excel format, then that would be great, but it needs to be automated once filemaker gets ahold of it so fiddling and renaming and such is not an option.

               

              thanks though

              • 4. Re: Excel import w/ complicated spreadsheet
                chronister

                This is kind of the route I am thinking... I want to import the file and loop through the records. I get what your saying and will check it out more. I may be back with some more questions though if that is cool with you. I don't want to have it written for me, but I certainly need some hand holding for this one.

                 

                It is my first major task for Filemaker. I have the basics down and am familiar with PHP and MySQL (tryinga a PHP solution as we type). Thanks for the help folks.

                • 5. Re: Excel import w/ complicated spreadsheet

                  Hi  Nathan,

                   

                  To my way of thinking, one critical part is whether, or not, you want a nornalised structure for your product table(s). ie. Do you want a related table of Colours and sizes for each entry in the Wovens table, or is ALL the data in your spreadsheet needed in one table. Either way is workable. It depends upon whether or not you are a purist for a fully nomalised data structure.

                   

                  If that latter option is your choice, then you have to find a way of replicating the data for each of the Woven entries for all of the rows where that data is missing but obvious in a speadsheet format from the preceding rows. It would be a clean import if you went through the hassle of dragging the Woven and Colour data into their following rows. If, however, you grasped the subtleties of the GetNthRecord function, then I think you could automate that after importing the existing spreadsheet into a Temporary FM table, so that the empty fields were filled in with data that was in the preceding rows.

                   

                  What's also obvious is that your spreadsheet lacks a UID for each row. You will likely need that going forward. Make sure you have a field for that entity when you do the initial import with an AutoEntry for a Serial Number.

                   

                  Maybe these suggestions can help to get you moving forward,

                   

                  Regards,

                   

                  John

                   


                  • 6. Re: Excel import w/ complicated spreadsheet
                    PSI

                    Nathan,

                     

                    You don't need to convert the file to csv. FMP has no trouble importing an Excel file. Your import script step will ask for the location of the file. The user will select it and the records will be imported...

                     

                    To setup the Temp Import file you can drop the Excel file on the FMP icon it will create a database. Select the option to create the field names based on the first record. If you don't want to use add a separate file to the solution just once this file is created just import the table into one of the solution files.

                     

                    Don't sweat the first record with field names. Setup a boolean field to flag that record. something like F_FieldNameRecord [ case ( SNS Code = SnS Code ; 1 ; 0 ) ]

                    once the records are imported find F_FieldNameRecord = 1 and delete.Then run the script to create the records in the product table.

                     

                    A couple of question come up. How many times is this file imported. Is it a one time setup function or are their updates?

                     

                    John

                    Pueblo Systems, Inc.

                    • 7. Re: Excel import w/ complicated spreadsheet
                      PSI

                      Nathan,

                       

                      I omitted an import issue. You can't create product records with the same SKU. it has to be different to reference the different sizes. If you are planning on using the SNS Code as the Sku you could append the Size to it when creating the records or create a calculation field that combines them. I would probably go with the latter.

                       

                      something like SKU [ SNS Code & Substitute ( SizeRange ; " " ; "" ) ] - the substitute just removes the spaces.

                       

                      John

                      • 8. Re: Excel import w/ complicated spreadsheet
                        chronister

                        Thank you all for your replies.

                         

                        I am shooting for a normalized structure in the DB. I have created 3 tables (Suppliers, Supplier Products and Supplier Product Prices) with the proper links between them for a nomalized structure. That is my end goal for all this data. It is just the process of getting it there that is the challenge.

                         

                        I am aiming to have an automated process that will be stored and run whenever the supplier updates thier prices. This is our primary one, but we do have others. I have some work to make the DB structure more generic and normalized so that I can have all suppliers operating off the same structures. But this one is very important so I MUST come up with a solution in order to build our quoting and workorders systems as they will rely on these products heavily.

                         

                         

                        There are some great suggestions here on this page and I thank you folks for responding. I will start putting something together and will likely be back for assistance.

                         

                         

                         

                        Thanks,

                         

                        Nathan    

                        • 9. Re: Excel import w/ complicated spreadsheet
                          PSI

                          it would have nice to have all this info in the first post. I am on my phone now so I can change the sample script to accommodate the new detail.

                           

                          I wouldn't use 3 tables. It creates unnecessary complications with out much benefit.

                           

                          John

                          PSI

                          • 10. Re: Excel import w/ complicated spreadsheet

                            Hi Nathan,

                             

                            John Morina's script in his first post is a simpler way of populating the empty fields than my suggestion of the GetNthRecord function.

                             

                            What I suspect he's missed is the fact that your ColourCat value sometimes changes within each Woven category. That will mandate another conditional:

                             

                            If (not IsEmpty (ColourCatagory) )

                                 Set Variable [ $ColorCat ; ColorCatagory ]

                            End If

                             

                            to populate the Colour data into the records that immediately follow each colour change.

                             

                            I agree that you will be better served by keeping all that data in one table, with supplier name as an additional field. I'd also be adding a serial number to each record, plus Timestamp fields for Creation and Modification.

                             

                            You'll then have to think ahead as to how you are going to handle price variations. Do you want to retain historical changes? If not, then you will be overwriting an existing price. The alternative is to add each new import as additional records then have a way to search for the latest price when you generate each catalog. FileMaker does make it easy to slice and dice your data!

                             

                            Cheers,

                             

                            John

                            • 11. Re: Excel import w/ complicated spreadsheet
                              chronister

                              I have something basic working at the moment..here is a link to the whole thing.

                               

                              http://static.plattsprinting.com/FM/sns_import_test.fmp12

                               

                              Feel free to tweak or suggest changes to it.

                               

                              This is my sample db and it will be moved into my main project once I have it doing exactly what I want.

                               

                              I have 1 main suppliers table that can be reused, but then I have 2 tables that are specifically for S&S. As I get other suppliers I will see if I can make a general layout for them. I may get this more streamlined down the road, but for now it works and I can let my employees search the S&S catalog on our site with the latest prices.

                               

                              Here is a printout of the script itself.

                               

                              sns_code.png

                              • 12. Re: Excel import w/ complicated spreadsheet
                                erolst

                                Something to watch out for. Your third If[] reads …

                                 

                                If [ not IsEmpty ( $category ) & $category ≠ … ]

                                 

                                This will not work as you expect: the Boolean operator in FM is 'and', there is no && equivalent as in some other languages, and & alone is a string operator only.

                                 

                                (Early FM lesson: learn to distinguish between +, & and AND … )

                                 

                                Thus, the result will be the string 00, 10, 01 or 11, depending on the results of the sub-expressions. Therefore, & effectively acts as a Boolean OR, since the string result will be considered True if it contains a "1" substring coming from any of the sub-expressions, i.e. any of them is True.

                                • 13. Re: Excel import w/ complicated spreadsheet
                                  chronister

                                  Thank you everyone for all your help.

                                   

                                  When I change the & to and then the fields get nothing.

                                   

                                  On that line I am trying to determine if the $category var is already set to the ssImport::Category field and update the $category var if need be.

                                   

                                  Suggestions?  Here is the latest code...

                                   

                                   

                                  1.png2.png3.png4.png

                                  • 14. Re: Excel import w/ complicated spreadsheet
                                    erolst

                                    chronister wrote:

                                    On that line I am trying to determine if the $category var is already set to the ssImport::Category field and update the $category var if need be.

                                    … AND you're testing if $category is not empty – and if you want to perform the variable step in the If[] block only if both expressions are true, than this line

                                     

                                    If [ not IsEmpty ( $category ) & $$importTable::category ≠ $category ]

                                     

                                    must read:

                                     

                                    If [ not IsEmpty ( $category ) and $$importTable::category ≠ $category ]

                                     

                                    But maybe I misread your script …

                                     

                                    PS: Please post editable PDFs, not images.