+ Reply to Thread
Results 1 to 4 of 4

update table from csv file

  1. update table from csv file

    In my database is a table with fields id and kerngroep and a few other
    fields. I want to update the field kerngroep in this table from a
    csv file. The csv file has only the fields id en kerngroep (from
    another datasource). Sometimes the Id in the csv file is not in the
    table.
    I tried to write a delphi program to do this. It was impossible (?)
    to update the FM table with ODBC and SQL in FM (Select * from table
    worked fine but update table .... not)
    The update is a weekly job.

    Is it possible to do the following procedure with a script?
    create a temp table,
    import the csv in the temp table,
    create a relation on table.id=temp.id.
    copy temp.kerngroep to table.kerngroep
    delete relation
    delete temptable

    I cannot find commands to create a table, define fields... but I'am
    quite new to FM.


    Any ideas?

    Greeting
    Kees



  2. Re: update table from csv file

    In article , Kees
    wrote:

    > In my database is a table with fields id and kerngroep and a few other
    > fields. I want to update the field kerngroep in this table from a
    > csv file. The csv file has only the fields id en kerngroep (from
    > another datasource). Sometimes the Id in the csv file is not in the
    > table.
    > I tried to write a delphi program to do this. It was impossible (?)
    > to update the FM table with ODBC and SQL in FM (Select * from table
    > worked fine but update table .... not)
    > The update is a weekly job.
    >
    > Is it possible to do the following procedure with a script?
    > create a temp table,
    > import the csv in the temp table,
    > create a relation on table.id=temp.id.
    > copy temp.kerngroep to table.kerngroep
    > delete relation
    > delete temptable
    >
    > I cannot find commands to create a table, define fields... but I'am
    > quite new to FM.
    >
    >
    > Any ideas?


    I'm not sure what commands you mean at the end there - ODBC, SQL or
    FileMaker's ScriptMaker.

    I don't really know anything about OBDC or SQL, but there are no
    commands in FileMaker's ScriptMaker to create / modify / delete the
    database's structure on the fly (eg. things like Tables, Fields,
    Relationships, Layouts, etc.). These things have to be edited manually.

    Luckily you don't need to change the structure anyway because this
    whole process can be done from within FileMaker using one simple
    command: Import.

    If you need to have it done by OBDC or SQL then maybe someone else can
    help (FileMaker can Import directly from an OBDC source and there is an
    Update SQL command) and you can stop reading my reply here.


    The basic process in FileMaker is:

    - open the database file and find all the records
    (Show All Records from the Records menu)

    - choose Import Records from the File menu and then
    choose the appropriate CSV file

    - FileMaker will display a window with two lists.
    On the left is the first record from the CSV file
    and on the right is the list of Fields in the
    database

    - at the bottom of this window there are some Import
    Action options, one of which is "Update matching
    records in current Found Set" - turn this option
    on and also turn on the "Add remaing records"
    sub-option

    - in the two lists, make sure your database fields
    line up with the correct CSV data

    - in-between the two lists is a small column where
    you can click to toggle the import symbol through
    three options.
    - for the ID field this option should be
    "Match fields" (the double-ended arrow)
    because you want the ID to match when
    updating the other field
    - for the Kerngroep field it should be
    "Import data" (single-ended arrow)
    - all the other database fields should be
    set to "Don't import" (the small o with
    a slash through it)
    Note: You don't say what version of FileMaker you
    are using. Newer versions than my FileMaker
    5.5 may have more options here.

    - Click on the Import button

    FileMaker will automatically import the CSV file, replacing the
    Kerngroep field with the CSV's data for matching ID records, and where
    there is no matching ID it will create new records.

    After it has finished, FileMaker will display a Found Set of records -
    these are the records that have been updated or created. Any records
    not in this Found Set did not have a matching ID in the CSV file. If
    you want to delete these not-updated records you can choose Show
    Omitted from the Records menu (which swaps the display from the Found
    records to Unfound records, ie. those that weren't in the CSV file) and
    then choose Delete Found Records from the Records menu.


    After you have done this process once manually you can create a
    FileMaker Script to do it for you. The basic Script would be just two
    lines something like ...

    Show All Records
    Import Records [Restore, No dialog]

    FileMaker will store the options from the last manually performed
    Import with this Script so that it can Restore it when running the
    Import command. In newer versions of FileMaker you can set more options
    when writing the Script, but in older versions you're stuck with this
    method.

    You can also set the CSV filename as part of the Import command, as
    long as the filename is always the same and the CSV file is always
    saved in the same directory / folder on your computer.


    If you want the Script to automatically delete the records that were
    NOT updated / created, then you can add a few more lines to the end:

    If [Get(CurrentFoundCount) = 0]
    Beep
    Show Custom Message ["No records where updated / added"]
    Else
    Show Omitted
    Delete All Records
    End If

    *BUT*
    you should always be extremely careful when dealing with Scripts that
    delete data!! Make a backup of the database BEFORE running the script
    in case it deletes everything.


    Helpful Harry
    Hopefully helping harassed humans happily handle handiwork hardships ;o)

  3. Re: update table from csv file

    On Thu, 31 Jan 2008 12:31:05 +1300, Helpful Harry
    wrote:

    >In article , Kees
    >wrote:
    >
    >> In my database is a table with fields id and kerngroep and a few other
    >> fields. I want to update the field kerngroep in this table from a
    >> csv file. The csv file has only the fields id en kerngroep (from
    >> another datasource). Sometimes the Id in the csv file is not in the
    >> table.
    >> I tried to write a delphi program to do this. It was impossible (?)
    >> to update the FM table with ODBC and SQL in FM (Select * from table
    >> worked fine but update table .... not)
    >> The update is a weekly job.
    >>
    >> Is it possible to do the following procedure with a script?
    >> create a temp table,
    >> import the csv in the temp table,
    >> create a relation on table.id=temp.id.
    >> copy temp.kerngroep to table.kerngroep
    >> delete relation
    >> delete temptable
    >>
    >> I cannot find commands to create a table, define fields... but I'am
    >> quite new to FM.
    >>
    >>
    >> Any ideas?

    >
    >I'm not sure what commands you mean at the end there - ODBC, SQL or
    >FileMaker's ScriptMaker.


    I was thinking about Filemaker script commands.

    >
    >I don't really know anything about OBDC or SQL, but there are no
    >commands in FileMaker's ScriptMaker to create / modify / delete the
    >database's structure on the fly (eg. things like Tables, Fields,
    >Relationships, Layouts, etc.). These things have to be edited manually.


    :-(

    >
    >Luckily you don't need to change the structure anyway because this
    >whole process can be done from within FileMaker using one simple
    >command: Import.


    Thank you for your solutions. It helps a lot.

    >If you need to have it done by OBDC or SQL then maybe someone else can
    >help (FileMaker can Import directly from an OBDC source and there is an
    >Update SQL command) and you can stop reading my reply here.


    I can still use some help on this subject. It would be nice to update
    the FM 7 database with SQL from an other application.

    snip ... a thorough explanation on how to add or update a FM table
    from a csv file.....
    >
    >Helpful Harry
    >Hopefully helping harassed humans happily handle handiwork hardships ;o)


    Thanks again,

    Kees


  4. Re: update table from csv file

    On Wed, 30 Jan 2008 09:16:52 +0100, Kees wrote:

    >In my database is a table with fields id and kerngroep and a few other
    >fields. I want to update the field kerngroep in this table from a
    >csv file. The csv file has only the fields id en kerngroep (from
    >another datasource). Sometimes the Id in the csv file is not in the
    >table.
    >I tried to write a delphi program to do this. It was impossible (?)
    >to update the FM table with ODBC and SQL in FM (Select * from table
    >worked fine but update table .... not)
    >The update is a weekly job.
    >
    >Is it possible to do the following procedure with a script?
    >create a temp table,
    > import the csv in the temp table,
    >create a relation on table.id=temp.id.
    >copy temp.kerngroep to table.kerngroep
    >delete relation
    >delete temptable
    >
    >I cannot find commands to create a table, define fields... but I'am
    >quite new to FM.
    >
    >
    >Any ideas?
    >
    >Greeting
    >Kees


    I'm not completely clear on everything you're trying to do here.
    Properly configured, FileMaker can definitely update records from an
    ODBC connection to SQL. What I don't understand is why you need to
    create a temporary table.

    A few things to check: make sure the correct ODBC system driver is
    installed and working; test the SQL credentials by themselves to make
    sure the ODBC data source can connect to SQL; make sure your import
    script has the SQL credentials saved, so it can connect on its own;
    and verify the field mapping of the import script so that records are
    updated from SQL (or CSV for that matter) by matching id and replacing
    kerngroep.

    On the import mapping screen, when you're "updating matching record",
    there's a checkbox labeled "Add remaining data as new records". This
    is for when there are unique values in the source match field (id)
    that aren't present in the destination. If the box is unchecked,
    those records in the source are ignored. If the box is checked, those
    records in the source are added to the destination as new records,
    according to the import mapping of course.

    What I believe you're trying to do is something I do all the time. I
    know your post is from back in January but if you're still stumped
    then drop me a line, maybe we can clear things up.

    hope this helps

    --
    FW

    FileMaker Pro 8.5 on Windows XP Pro SP2
    FileMaker Server 8.0 on Windows 2003 Server


+ Reply to Thread