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

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)

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