5 Replies Latest reply on Dec 12, 2013 8:14 AM by philmodjunk

    Importing advice?

    ultranix

      Title

      Importing advice?

      Post

           I want to import data from .csv file.

           in order not to duplicate records, that already exist, i use "Import action: Update matching records in found set" and "Add remaining data as new records".

           here i encounter a problem: our software, that exports data to .csv, doesn't include client_id into the .csv file, but only names file with the client_id.

           which later becomes a problem for import, as i cannot use "Field mapping = Match records based on this field" (I usually base on client_id and date fields (date field is already in .csv, so it's ok)

           Is there another way to perform such import of records from .csv (Update matching records in found set, Add remaining data as new records, Field mapping = Match records based on this field) getting the filename of .csv as a variable and using it as a source field to match records based on this field, or the only option is to open .csv in Excel and add another column, where i put client_id in all rows and only then import?

        • 1. Re: Importing advice?
          philmodjunk

               It is possible with a bit of sneaky scripting to get the filename, but since the file name will be the same for all rows of data in the csv, I can't see how that will help you match records....

          • 2. Re: Importing advice?
            ultranix

                 It will, because when i match based not only date but also client_id, then it would ignore records, that are already present in database and only import those, which don't exist in db.

            • 3. Re: Importing advice?
              philmodjunk

                   You can use Insert File to open a dialog where the user inserts the csv file with "store a reference" enabled. The script can then extract both the file path and file name from the container field.

                   Import records can use the full file path in a $Path variable to import the data and a calculation can be used to extract the file name from the container field. If this is to combine with data from the CSV before matching you may need to import into a temp file, update a field with the file name text and then do the import matching into your actual target table.

                   See this thread for more on $Path variables and to get a file that contains a calculation for extracting file names from container fields: Exploring the use of a $Path Variable in Scripts

              • 4. Re: Importing advice?
                ultranix
                     

                          Import records can use the full file path in a $Path variable to import the data and a calculation can be used to extract the file name from the container field. If this is to combine with data from the CSV before matching you may need to import into a temp file, update a field with the file name text and then do the import matching into your actual target table.

                     Sound somewhat complex.

                     I did it this way: i created new and very small file with two tables - files and records. I first of all import (Folder) and import filenames. Then i set filename as a variable (as it is the same as client_id) and import that file into records table. Then i use replace field contents script step, and thus i fill empty client_id with filename variable. then script goes back to files table, deletes record and loops the script again (set variable, go to different layout, import data, replace field contents, go back to layout, delete record). once all records from files table are delete, loop exits.

                     So then i am able to import this file to my solution. I couldn't find a better way around myself.

                • 5. Re: Importing advice?
                  philmodjunk

                       It's complex perhaps to set up but actually makes for a user friendly experience for the user. They just click a button and choose the file from which to import the data.

                       From my perspective your approach appears equally complex but hey! whatever gets the job done! wink