3 Replies Latest reply on Dec 11, 2013 9:41 AM by philmodjunk

    Working with CSV Files

    dblackmon

      Title

      Working with CSV Files

      Post

           I have a CSV file that is generated by another program that I wish to automate the importing process. 

            

           My issues is the CSV contains two sets of data that belonged within two different tables, with each having there own headers.  The first set of data is the first two lines in the CSV file which will not change, with a space dividing the second set of data. From there it will always be around 30 to 40 lines of information needing to be imported within a different table. Is there a way to script an import routine to read the individual lines in the CSV or is there a better way to automate this process within Filemaker.

           I have been using the product for less than a year so I am hoping there is an easy way to preform this feat or engineering.

            

           I have included a small sample of the data being collected to help describe what i am trying to achieve.

            

                                                                                                                                                                                                                  
                          Start Time                     Track                     Series                     Hosted Session Name
                          2013.12.10 12:39 AM GMT                     Atlanta Motor Speedway - Oval                     Hosted iRacing                     NOBULLMOTORSPORT  Nationwide  Series
           <!--StartFragment-->                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              <!--EndFragment-->      
                          Fin Pos                     Car ID                     Car                     Car Class ID                     Car Class                     Custid                     Driver                     Start Pos                     Car #                     Out ID                     Out                     Interval                     Laps Led                     Average Lap Time                     Fastest Lap Time                     Fast Lap#                     Laps Comp                     Inc                     Club ID                     Club                     Session Name
                          1                     38                     Chevrolet Impala Class B                     0                     Hosted All Cars Class                     88499                     James Ray                     2                     0                     0                     Running                     0                     33                     48.218                     29.859                     60                     80                     0                     22                     Florida                     NOBULLMOTORSPORT  Nationwide  Series
                          2                     51                     Ford Mustang Class B                     0                     Hosted All Cars Class                     111941                     Chuck Sweeting                     29                     62                     0                     Running                     -0.479                     12                     48.224                     29.905                     77                     80                     4                     22                     Florida                     NOBULLMOTORSPORT  Nationwide  Series
                          3                     38                     Chevrolet Impala Class B                     0                     Hosted All Cars Class                     80955                     Kevin Carr                     20                     3                     0                     Running                     -0.958                     0                     48.23                     29.964                     68                     80                     0                     16                     Pennsylvania                     NOBULLMOTORSPORT  Nationwide  Series

        • 1. Re: Working with CSV Files
          philmodjunk

               I think that you may need to do a "two stage" import. Stage 1 imports into a temporary table of text fields and then Stage 2 is where you can selectively import data from the temporary table into your two existing tables used to store this data.

               After importing into the temp table, you can isolate those first two rows in a found set and then use import records to import them into the correct table/fields. Then you isolate the numbered rows of data in another found set and use import records to import that data into the other table.

          • 2. Re: Working with CSV Files
            dblackmon

                 Using your suggested method how do you think it would impact the system in a multiuser environment where more than one person could be importing data possibly at the same time. Is there a way to create individual temp tables per season and then pure the temp tables after use.

            • 3. Re: Working with CSV Files
              philmodjunk

                   When you use Import Records, the action produces a found set of the newly created records. This found set will be specifc to a particular user. Thus two users could import data into the same table at the same time and thus, they won't get in each other's way as much as you might think.

                   You can enable auto-enter options during import and then an added text field can auto-enter the user's account name in each record that they import. That can help you set up scripts that perform finds to pull up the different groups for import from the temp table to their final destination and instead of deleting all records when done, you have the script find all records with the current user's account name and delete them--leaving records still being processed by another user untouched.