10 Replies Latest reply on Dec 5, 2014 2:09 PM by AramTokatian

    Help Importing and Merging a CSV

    AramTokatian

      Title

      Help Importing and Merging a CSV

      Post

      Good morning all. Kind of still a newbie on FM13. If you can assist, I would deeply appreciate it. I currently use quickbooks in my business, but the version I have does not allow you to import pictures and associate them with inventory items. I have decided to write a simple flat database in which I can import a XLS file generated by QB into FM.

      Well it imports very nicely. I then drop pictures into my container fields associated with any inventory part/ per record within FM.

      My problem is when I go to import the file again, it overwrites my pictures even though mapping is set not to receive anything for the picture field. I also want it not to duplicate records every time it imports from then on. I don't know if it will do that or not yet.

      Once it is complete, I should be able to simply find inventory parts visually and make it quicker to order parts, instead of memorizing numbers. 

      Any assistance would be deeply appreciated.

       

      Aram

        • 1. Re: Help Importing and Merging a CSV
          philmodjunk

          What options are you selecting when you import this data? It sounds like you are importing a completely new set of records into your table. If so, the new records won't have any images in their container fields but if you do a show all records and navigate back to one of the original records, you'll find your images still in the container fields of those original records. But you might also have set up a "recurring import" that deletes all your records just before importing new data. So we need to know some things:

          What are the exact options that you selected when importing the data?

          Is there a column in this data to be imported that has a value that uniquely identifies each record and that matches to the same records in your FileMaker file?

          • 2. Re: Help Importing and Merging a CSV
            AramTokatian

            Thank you:

            I am not selecting any options, because I don't think they apply (or not sure which to select if they do). And yes, I am importing a new set as if I was importing from scratch again, and again. Attached is the window. Please take a look. Also yes, the ITEM number has a distinct number for each record.

             

            Aram

            • 3. Re: Help Importing and Merging a CSV
              philmodjunk

              You can't import without having options. You've used the default "add new records" option with a custom field matching that you apparently selected manually.

              But that leaves my second question unanswered. sad

              • 4. Re: Help Importing and Merging a CSV
                AramTokatian

                Thank you Philmod:

                 

                I think I see what you are saying after reviewing the book. Let me try to clarify:

                1) I import my spreadsheet into FM for the first time only; all recurring imports (done manually) from then on --i do not want to duplicate and add into FM. So I think selecting "Add new records" is incorrect the second time and so forth.

                2) For the second importing of records, i will select "Update records in found set". This will overwrite all records. I am assuming the container fields will not be overwritten since I am not importing those specific fields. Correct?

                The ITEM field can be used as a reference "distinct number". I think that answered the second question. If not, than I am not clear what the second question is.

                 

                • 5. Re: Help Importing and Merging a CSV
                  philmodjunk

                  Yes, you cannot use the update matching records in found set, unless you have a way to match records. If the Item field uniquely identifies each record, then you can:

                  Show All Records in your target table. (Since we will be matching to the found set, we need all records in that found set.)

                  Select Import Records and select the Matching Records in Found Set option

                  Align your columns to fields as needed and click the arrow between the Item column and Item field to turn it into an = sign to designate this field as the match field.

                  If there is any chance that you might have a new record added to the source table that will then not have a matching record in the target table, also select the "add remaining data as new records" option.

                  A DO NOT try to use FileMaker's built in recurring import tool. This creates a script that deletes all records from your table just before importing a new set of data. If you want to automate this process, you will need to create your own script to perform the above steps.

                  • 6. Re: Help Importing and Merging a CSV
                    AramTokatian

                    Let me give this a shot, and report back. I think it is working!!

                     

                    Thank you sir.smiley

                    • 7. Re: Help Importing and Merging a CSV
                      AramTokatian

                      Well when "Update existing records in found set" was checked. The photos were not overwrtitten, and no additional set of records doubled the database size. That is good.

                      I went back and added another line to the spreadsheet (essentially creating another record in the import file itself), and then imported the same way as above. An error displayed: "There are more records in the data source than in the target table. Not all records were imported." So that didn't work.

                      I then went to try my last option: "Update matching records in found set" which resulted in "At least one pair of matching fields must be specified for the "Update matching records option" would this do what I need anyway?

                       

                      Aram

                      • 8. Re: Help Importing and Merging a CSV
                        AramTokatian

                        I guess what do I do at this point?

                        • 9. Re: Help Importing and Merging a CSV
                          AramTokatian

                          Okay Got it. Simple as checking the "Add remaining data as new records..." box.. let's see.

                           

                          Aram

                           

                          • 10. Re: Help Importing and Merging a CSV
                            AramTokatian

                            Philmodjunk. I changed the equal sign to match the fields (missed that part), and now everything seems to work perfectly. Thank you so much for your help! You're a good guy!

                             

                            Aramyes