Exporting Data to a CSV Text file

Exporting Data to a CSV Text file

am 30.09.2007 21:51:38 von JayBee

Hi

My first posting to this group, hopefully the first of many. This
could be a really helpful source for me :-)

I am creating a script to make a Filemaker 7 Pro database export data
to a CSV file, most of the information will come from existing field
values.

However I would like to know if it is possible to create 'static'
information within the CSV text file, and hopefully some pointers to
how this can be acheived.

Basically 4 lines of the text file will always need to be the same but
do not exist as individual fields in the database.

Re: Exporting Data to a CSV Text file

am 30.09.2007 22:25:46 von bill

In article <1191181898.192596.156390@o80g2000hse.googlegroups.com>,
JayBee wrote:

> Hi
>
> My first posting to this group, hopefully the first of many. This
> could be a really helpful source for me :-)
>
> I am creating a script to make a Filemaker 7 Pro database export data
> to a CSV file, most of the information will come from existing field
> values.
>
> However I would like to know if it is possible to create 'static'
> information within the CSV text file, and hopefully some pointers to
> how this can be acheived.
>
> Basically 4 lines of the text file will always need to be the same but
> do not exist as individual fields in the database.

One approach would be to create a global field which holds the static
text, then export that field along with the others you want to export.

--
For email, change to
Bill Collins

Re: Exporting Data to a CSV Text file

am 30.09.2007 22:40:28 von JayBee

On Sep 30, 9:25 pm, Bill wrote:
> In article <1191181898.192596.156...@o80g2000hse.googlegroups.com>,
>
>
>
>
>
> JayBee wrote:
> > Hi
>
> > My first posting to this group, hopefully the first of many. This
> > could be a really helpful source for me :-)
>
> > I am creating a script to make a Filemaker 7 Pro database export data
> > to a CSV file, most of the information will come from existing field
> > values.
>
> > However I would like to know if it is possible to create 'static'
> > information within the CSV text file, and hopefully some pointers to
> > how this can be acheived.
>
> > Basically 4 lines of the text file will always need to be the same but
> > do not exist as individual fields in the database.
>
> One approach would be to create a global field which holds the static
> text, then export that field along with the others you want to export.
>
> --
> For email, change to
> Bill Collins- Hide quoted text -
>
> - Show quoted text -

O K Thanks Bill,

So to do this I would select the menu options: File, Define Database,
then add a field?

Usually you select form a dropdown list the table that you would like
to create the field in; Will it be obvious where to go from here?

It sounds ideal, because it would then be stored information that can
be transferred but it doesn't have to be visible in the user entry
interface.

Re: Exporting Data to a CSV Text file

am 01.10.2007 02:14:12 von bill

In article <1191184828.332547.42030@22g2000hsm.googlegroups.com>,
JayBee wrote:

> On Sep 30, 9:25 pm, Bill wrote:
> > In article <1191181898.192596.156...@o80g2000hse.googlegroups.com>,
> >
> >
> >
> >
> >
> > JayBee wrote:
> > > Hi
> >
> > > My first posting to this group, hopefully the first of many. This
> > > could be a really helpful source for me :-)
> >
> > > I am creating a script to make a Filemaker 7 Pro database export data
> > > to a CSV file, most of the information will come from existing field
> > > values.
> >
> > > However I would like to know if it is possible to create 'static'
> > > information within the CSV text file, and hopefully some pointers to
> > > how this can be acheived.
> >
> > > Basically 4 lines of the text file will always need to be the same but
> > > do not exist as individual fields in the database.
> >
> > One approach would be to create a global field which holds the static
> > text, then export that field along with the others you want to export.
> >
> > --
> > For email, change to
> > Bill Collins- Hide quoted text -
> >
> > - Show quoted text -
>
> O K Thanks Bill,
>
> So to do this I would select the menu options: File, Define Database,
> then add a field?
>
> Usually you select form a dropdown list the table that you would like
> to create the field in; Will it be obvious where to go from here?
>
> It sounds ideal, because it would then be stored information that can
> be transferred but it doesn't have to be visible in the user entry
> interface.

Yes, File>>Define Database>>Fields
Select the table you want to put it in.
Type the name of the field, make it Text (which is the default), then
Create, then Options >> Storage >> Use Global Storage

Helpful harry pointed out a difficulty with this approach, which is that
when you do the export, the contents of the global field will export
with each of the records. He suggested a different approach, which you
might want to think about. See his posting in this thread.

--
For email, change to
Bill Collins

Re: Exporting Data to a CSV Text file

am 01.10.2007 02:48:44 von Helpful Harry

In article
, Bill
wrote:

> In article <1191181898.192596.156390@o80g2000hse.googlegroups.com>,
> JayBee wrote:
>
> > Hi
> >
> > My first posting to this group, hopefully the first of many. This
> > could be a really helpful source for me :-)
> >
> > I am creating a script to make a Filemaker 7 Pro database export data
> > to a CSV file, most of the information will come from existing field
> > values.
> >
> > However I would like to know if it is possible to create 'static'
> > information within the CSV text file, and hopefully some pointers to
> > how this can be acheived.
> >
> > Basically 4 lines of the text file will always need to be the same but
> > do not exist as individual fields in the database.
>
> One approach would be to create a global field which holds the static
> text, then export that field along with the others you want to export.

Unfortunately including a Global field in an export will mean that data
is exported with EVERY record, not just once at the start of the file
.... or at least it does in my older version of FileMaker.

What you would need to do is create a new record to hold the extra
data, and make sure it gets sorted to the top before exporting to the
file.

OR, depending on what / how you're trying to export, you might be able
to create one Calculation field (or script) in a new table that
concatenates together everything you need and then exports just one
record with this field to a Tab separated file.

OR, you might need to invest in a text file manipulation plug-in.


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

Re: Exporting Data to a CSV Text file

am 01.10.2007 06:13:21 von d-42

On Sep 30, 5:48 pm, Helpful Harry
wrote:
> In article
> , Bill
>
>
>
> wrote:
> > In article <1191181898.192596.156...@o80g2000hse.googlegroups.com>,
> > JayBee wrote:
>
> > > Hi
>
> > > My first posting to this group, hopefully the first of many. This
> > > could be a really helpful source for me :-)
>
> > > I am creating a script to make a Filemaker 7 Pro database export data
> > > to a CSV file, most of the information will come from existing field
> > > values.
>
> > > However I would like to know if it is possible to create 'static'
> > > information within the CSV text file, and hopefully some pointers to
> > > how this can be acheived.
>
> > > Basically 4 lines of the text file will always need to be the same but
> > > do not exist as individual fields in the database.
>
> > One approach would be to create a global field which holds the static
> > text, then export that field along with the others you want to export.
>
> Unfortunately including a Global field in an export will mean that data
> is exported with EVERY record, not just once at the start of the file
> ... or at least it does in my older version of FileMaker.
>
> What you would need to do is create a new record to hold the extra
> data, and make sure it gets sorted to the top before exporting to the
> file.

Works well, unless you need the 'header' to look exactly a certain
way; with e.g. less 'fields' than the main body.

> OR, depending on what / how you're trying to export, you might be able
> to create one Calculation field (or script) in a new table that
> concatenates together everything you need and then exports just one
> record with this field to a Tab separated file.

Yep, could do, and will work, but its its fairly clumsy.

> OR, you might need to invest in a text file manipulation plug-in.

Or just use what comes with the OS, which is more than enough for the
task at hand. If you need to pre-pend a static header to the output
file for example, generate the export file then in windows

In windows: copy header.txt + export.csv joinedfile.abc will get the
job done.
In OS X: cat the files, and redirect the output to a file will do it.

You can automate this from within Filemaker; have the script pause a
couple seconds before calling the shell command though.

-cheers,
Dave

-cheers,
Dave

Re: Exporting Data to a CSV Text file

am 01.10.2007 19:12:51 von FastWolf

On Sun, 30 Sep 2007 12:51:38 -0700, JayBee
wrote:

>Hi
>
>My first posting to this group, hopefully the first of many. This
>could be a really helpful source for me :-)
>
>I am creating a script to make a Filemaker 7 Pro database export data
>to a CSV file, most of the information will come from existing field
>values.
>
>However I would like to know if it is possible to create 'static'
>information within the CSV text file, and hopefully some pointers to
>how this can be acheived.
>
>Basically 4 lines of the text file will always need to be the same but
>do not exist as individual fields in the database.

The way I would do this is, I would create a field for your export
that uses a calculation to combine the static data and the dynamic
data. This field doesn't have to appear on the layout unless or until
you want it to do. You can stick the static data right in the
calculation formula of the field:

t_export = "Static data "&t_dynamic_data&" more static data"

.... where t_dynamic_data is the field[s] you want to export along with
your static data, and t_export is the calc field that puts it all
together. At export time, just select t_export and that's that.

I use this trick all the time to create usable SQL queries from
FileMaker databases.

hope this helps

--
FW

FileMaker Pro 8.5 under Win XP Pro, and
FileMaker Server under Win 2003 Server

Re: Exporting Data to a CSV Text file

am 01.10.2007 22:34:38 von JayBee

On Oct 1, 1:14 am, Bill wrote:
> In article <1191184828.332547.42...@22g2000hsm.googlegroups.com>,
>
>
>
>
>
> JayBee wrote:
> > On Sep 30, 9:25 pm, Bill wrote:
> > > In article <1191181898.192596.156...@o80g2000hse.googlegroups.com>,
>
> > > JayBee wrote:
> > > > Hi
>
> > > > My first posting to this group, hopefully the first of many. This
> > > > could be a really helpful source for me :-)
>
> > > > I am creating a script to make a Filemaker 7 Pro database export data
> > > > to a CSV file, most of the information will come from existing field
> > > > values.
>
> > > > However I would like to know if it is possible to create 'static'
> > > > information within the CSV text file, and hopefully some pointers to
> > > > how this can be acheived.
>
> > > > Basically 4 lines of the text file will always need to be the same but
> > > > do not exist as individual fields in the database.
>
> > > One approach would be to create a global field which holds the static
> > > text, then export that field along with the others you want to export.
>
> > > --
> > > For email, change to
> > > Bill Collins- Hide quoted text -
>
> > > - Show quoted text -
>
> > O K Thanks Bill,
>
> > So to do this I would select the menu options: File, Define Database,
> > then add a field?
>
> > Usually you select form a dropdown list the table that you would like
> > to create the field in; Will it be obvious where to go from here?
>
> > It sounds ideal, because it would then be stored information that can
> > be transferred but it doesn't have to be visible in the user entry
> > interface.
>
> Yes, File>>Define Database>>Fields
> Select the table you want to put it in.
> Type the name of the field, make it Text (which is the default), then
> Create, then Options >> Storage >> Use Global Storage
>
> Helpful harry pointed out a difficulty with this approach, which is that
> when you do the export, the contents of the global field will export
> with each of the records. He suggested a different approach, which you
> might want to think about. See his posting in this thread.
>
> --
> For email, change to
> Bill Collins- Hide quoted text -
>
> - Show quoted text -

Hi Bill

I replied this morning but it doesn't seem to be showing up. It is
not actually a problem for me that the static information will be
exported with every record, in fact this is exactly what I want.

I implemented it this morning and I couldn't see that it was working
at all well. Then I discovered that my static data will be auto
entered into this field upon record creation - which is helpful for
the system that we are implementing; minor hitch is that it obviously
hasn't stored it for any existing records.

Great advice though, it is exactly the approach I needed and will work
well in the future for what I am ultimately trying to acheive!