2 Replies Latest reply on May 13, 2009 5:03 PM by davidr_1

    How to create a field outside of "Manage Database"

    davidr_1

      Title

      How to create a field outside of "Manage Database"

      Post

      Can I do this by importing an Excel/Numbers file, or with Applescript, or SQL?

      Thanks for any guidance on this.

      David 

        • 1. Re: How to create a field outside of "Manage Database"
          TSGal

          davidr:

           

          Thank you for your post.

           

          When you import an Excel file, the top of the right side of the Import Field Mapping dialog box is a pop-up.  This allows you to import into the current table, or you can select it to import into a new table, and the fields will be created.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: How to create a field outside of "Manage Database"
            davidr_1
              

            Thanks for the hint. I used this method to import the csv files into new tables.

             

            Here's the Perl script I ended up with.

            Extracts the field names I need from csv files exported by Numbers from the original Excel tables. The field names are in the second column, beginning just below a cell saying "CALPADS Field Names". The output files are one line csv files, with the field names in order.

             

            (Save your work before running this...) (and make backups)

             

            #!/usr/bin/perl

            #usage: eg. "perl script.pl *.csv" creates output files in a new subdirectory named "transpose"

            #By the way, you need to use cpan to download the "Text::CSV_XS" module.

             

            use strict;

            use warnings;

            use Text::CSV_XS;

             

            my $outPutDir = 'transpose'; #Makes a subfolder of the script's directory to store the transposed output csv files.

            mkdir $outPutDir;

             

            foreach my $file (@ARGV){  #expects a glob of input files on the commandline, such as "perl <thisScript.pl> *.csv"

            open my $io, "<", $file or die "$file: $!";

            open (TRANS, '>', "$outPutDir/$file") or die $!;

             

             

            my $csv = Text::CSV_XS->new ({ binary => 1, eol => $/ }); #I hope to read csv files correctly, even with embedded newlines etc.

             

            my $virgin=1; #Leave lines alone until we see the column header "CALPADS Element Name" in column two. Subsequent rows will contain actual fieldnames in this column.

            my $preFieldSeparator=''; #When writing csv records, you don't need a comma before the initial field.

            while (my $row = $csv->getline ($io)) {# This Text::CSV_XS method purportedly deals with embedded special characters better.

            my @fields = @$row;

            if ($virgin==1){ #Um, we want to refrain from printing until we see the headline where the "Element Names" begin.

            if ($fields[2] eq "CALPADS Element Name"){

            $virgin = 0;

            next; #We skip the printing line below up to and including the row which includes the "Element Names" headline.

            } else { #We print the second field of each row we see _after_ the row with the "..Elements" headline.

            print(TRANS $preFieldSeparator, '"', $fields[2], '"'); #This prints all fields quoted, and includes

            $preFieldSeparator=','; #..a comma before all but the first field.

            }

            }

            print(TRANS "\n"); #A newline for the end of the output record. Note there's only one record per file, containing just the fieldnames parsed from the table of field descriptions.

             

            close $io; #Close the input and output files.

            close TRANS;

            }