Scheduled server import skips fields

[jdu98a]

I have a scheduled script that simply imports a .csv file into a database hosted on FMServer12. The file is located in the 'Documents' folder and the script uses 'Get ( DocumentsPath )' to retreive the file. There are no problems with FMS finding the file. However, there are two fields in the .csv, and FMS will ONLY import the 2nd field, and ONLY IF the 1st field contains data.

 

I can do a manual import of the exact same file with no problems at all. But when FMS performs the import it completely skips the first field. And, if the 1st field is empty (in the imported file), it will get no data for the 2nd field. There are no auto-fills or script triggers on any fields involved in the import. Any ideas?

[Steven H. Blackwell]

Could the first field be failing validation?

 

Steven

[jdu98a]

There are no validation settings on that first field or the second one. The data that should be going into the first field is a string of numbers (a UUID from another system). I have tried switching between defining the first field as 'text' and 'number' neither works.

 

My gut tells me it has something to do with the active server side account being in the wrong table when performing the import. But I've been over the script steps, it's really not a very complicated one.

 

Go To Layout [currently the only layout associated with the target table]

Set Variable [$File; value:Get ( DocumentsPath ) & "fileName.csv"]

Import Records [no dialogue; $File; Add; Windows ANSI]

[jdu98a]

Ok, I've done some further testing. Here is the consistent behavior:

 

1) The source file must contain 1 column of data in addition to the data I want to import and that additional column MUST be the first column and it MUST contain data.

2) No matter what field that first column is mapped to in the import script step NO DATA will be imported into the field.

3) As long as that first column in the source file contains data any following fields will import correctly.

 

Here's a rough illustration to clarify:

 

   Source File (.csv)               Import Step                        Result

Column A - “dataA”             Column A > Field A             Field A - Null

Column B - “dataB”             Column B > Field B             Field B - “dataB”

Column C - “dataC”             Column C > Field C            Field C - “dataC”

 

OR (with no data in column A)

 

Column A - Null                   Column A > Field A             Field A - Null

Column B - “dataB”             Column B > Field B             Field B - Null

Column C - “dataC”             Column C > Field C            Field C - “dataC”

 

This behavior is consistent regardless of the fields to which the data is mapped.

[MartieH]

how is the csv file generated? it sounds to me like there is an issue with the commas - since there are no real 'columns' in a csv file - only fields deliminated by commas -- I am wondering if the end row character is missing or if the commas are misplaced or something along those lines.

 

hth

 

Martie

[jdu98a]

The mis-constructed .csv had entered my mind too. The file is created simply from the export of records from another Filemaker database. I did a test export on only a few records and a quick visual inspection didn't reveal any problems. Also, I can take the same file that has problems in the scheduled import and do an manual import with no issues at all.

 

Good brainstorming though. That's why I brought the issue here. I appreciate it.