Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Drag and Drop csv file and import: script steps

Question asked by chuckburr on Nov 13, 2018
Latest reply on Dec 1, 2018 by beverly

Like • Show 0 Likes0
Comment • 17

What are the script steps to drag and drop a csv or xlsx file and then import it as records into fields? I presume the user drags a file into a global container, I have to then use FileMaker decode/encode? script step to convert the bianary file to csv or xlsx file, then import it using an import script step with a saved field import order. See sample file.

Assume:

- works with WebDirect

- the field order of the file matches the import script.

- the file is csv UTF-8 encoded or xlsx to simplify things.

- has to work on FM server cross platform.

- I can control the field import order, when the user chooses an option from a drop down menu specifying the system they are coming from in advance.

- The file is imported into one table where I validate the data before it is imported into the solution. I am not testing encoding in this step, testing encoding is outside the cope of this question.

- These files are from the general lay public (not internal company staff) so the names are random but I hope the suffix stays csv xlsx which I will check for.

- Importing is non recurring, this is a tool for lay users to import their data.

Thank you.

Attachments

NumberTest.csv.zip321 bytes

No one else has this question

Outcomes

17 Replies

eric Nov 13, 2018 11:48 PM
Mark Correct
Correct Answer
I hope I'm not oversimplifying what you're asking:
1. There's not really script the literal act of dragging and dropping a csv or xlsx file.
2. You can, however, script the act of File > Import Records > File...
3. The question says it's about dragging and dropping a csv or xlsx file, so if it's in that form, there would be no need for a global container or a decode/encode script step to convert a binary file to csv or xlsx file.
4. Just import it using an import script step with a saved field import order.
However, if you mean to import a zipped file like the one you attached,
I found something from this discussion:
Unzip file on server
You can use the BaseElements plugin to unzip files: BE_Unzip – BaseElements Plugin Help Centre

By the way, if you mean for those date field to import as dates, FileMaker will only recognize them as text in that format
Like • Show 0 Likes0
Actions
badmonkey842 Nov 14, 2018 5:36 AM
Mark Correct
Correct Answer
My interpretation is that you want the script to trigger automatically when a user drops an file into some type of container field located on a layout. That script then checks to determine whether or not it is csv. or .xlsx and then imports accordingly.
_____________________________

FWIW and may be problematic

I have done something similar with the exception of the cvs vs. xlsx option and only on FMP window installs.

I had a container field, where a user drops the file into the UI layout. Using a trigger, I then prompt the user if they would like to continue with an import. if not escapes the script.

the field must be set up as a reference, so that the script can capture the filePath in a $var . At the same time I suppose you could search that $var for pattern count of string ".csv" vs ".xlsx" or to be even safer to take the Right 5 characters and then pattern count that string (to prevent the possibility of filenameContainsCSV.xlsx).

if the user accepts the show dialog box and confirms import. I would most likely import into a table table and validate the column order by way of header values? May add a second layer of protection since the import file types may be different.

then after, import from the temp table (post clean up) into the final table(s)

extremely generic and shorthanded script

ex
IF PatternCount($var; ".csv") >=1
then importCSV into tempTable using OrderA
ELSE IF PatternCount ($var; ".xlsx") >=1
importXLSX into tempTable. Using OrderB
end if

validate headers
exit script if failed
import from tempTable to Final Table(s)
1 person found this helpful
Like • Show 1 Like1
Actions
chuckburr @ badmonkey842 on Nov 14, 2018 9:08 AM
Mark Correct
Correct Answer
- Maybe more detail to:
"the field must be set up as a reference, so that the script can capture the filePath in a $var"

- Do we have to tell FM if the file is csv or xlsx if the field order is the same either way?

- If the user drags the file to a container, then the file becomes a binary? Are you then actually importing it based on the above path $var and not from the container into a table?
Like • Show 0 Likes0
Actions
eric @ chuckburr on Nov 14, 2018 9:32 AM
Mark Correct
Correct Answer
chuckburr wrote:
"the field must be set up as a reference, so that the script can capture the filePath in a $var"

- Do we have to tell FM if the file is csv or xlsx if the field order is the same either way?
Are you saying that the file has already been dragged-and-dropped into a container?
You can specify the file type in the script or if you leave dialog on, they can change specifications.

- If the user drags the file to a container, then the file becomes a binary? Are you then actually importing it based on the above path $var and not from the container into a table?
The file keeps its format in the container. When you Export Field Contents, it most certainly will be in the same format before it went in.
Like • Show 0 Likes0
Actions
chuckburr @ eric on Nov 14, 2018 9:44 AM
Mark Correct
Correct Answer
To back up, the source of the files are from the public so the type will vary, not controlled internal staff users. I am looking for the best way to do this from the start, just developing the solution. Want to make importing files from lay users as easy as possible for them.

I did not understand, "field must be set up as a reference" one small thing. Thanks
Like • Show 0 Likes0
Actions
jrenfrew @ chuckburr on Nov 14, 2018 9:53 AM
Mark Correct
Correct Answer
There are some more questions about what you 'know' first

Are there more than one set of column headers in the files?? to be imported into more than one table?
How are the files named? Is the content of the csv and xlxs the same or different?

Assuming this is ONE file (multiple times) into ONE field

Drag onto a container, on exit script trigger
Export file to know location with known name
Recurring import - do it manually once to test the logic, then add an import script step pointing to the file then the import will match up.
Save script then change file reference to a variable so you can export the dragged file anywhere and it still works X platform
Like • Show 0 Likes0
Actions
chuckburr @ jrenfrew on Nov 14, 2018 9:59 AM
Mark Correct
Correct Answer
- I can control the field import order, they choose that from a drop down menu in advance.
- The file is imported into one table where I validate the data before it is imported into the solution. I am not texting encoding in this step, testing encoding is outside the cope of this question.
- These files are from the public so the names are random but I hope the suffix stays csv xlsx which I will check for.
- Importing is random and non recurring, this is a tool for users to import their data.
Like • Show 0 Likes0
Actions
eric @ chuckburr on Nov 14, 2018 10:03 AM
Mark Correct
Correct Answer
chuckburr wrote:

I did not understand, "field must be set up as a reference" one small thing. Thanks
I'm not sure either, but I think badmonkey842 means the container should be a reference rather than stored.
FileMaker Pro 17 Advanced Help: Using Containers
I probably wouldn't do it that way, especially for WebDirect compatibility, etc.
Like • Show 0 Likes0
Actions
jrenfrew @ chuckburr on Nov 14, 2018 10:16 AM
Mark Correct
Correct Answer
>>These files are from the public

What do you mean by this???
They can create a file with random header names or are creating them from some known starting point??
Like • Show 0 Likes0
Actions
chuckburr @ jrenfrew on Nov 14, 2018 10:28 AM
Mark Correct
Correct Answer
The general public. They will choose from a drop down list to determine what system they car coming from which will lock down the import field order. But how they name the files and sometimes encoding varies haha.
Like • Show 0 Likes0
Actions
badmonkey842 @ chuckburr on Nov 14, 2018 10:41 AM
Mark Correct
Correct Answer
sure, I am a beginner and was able to find "how -to", from a variety of ppl. So i cant take credit... FWIW I am doing this from memory as I head out the door. Im sure it is full of mistakes and others can chime in as needed to correct the plethora of mistakes

___________________

1) I have a global table with a single record that holds global fields/data. Within that table I have a field: type container set up. This is a multi purpose field I use for a variety of file imports. Because it is field field storage option is marked as "global" it can be accessed from added to any layout without context.

2) I place the field onto my desired layout

3) Created a Script trigger (OnObjectModify) or an "upload" button (your choice)

4) User Drags & Drop into container field and script is ran via that trigger or upload button

5) Script contains something to the nature of...

set var $fileName ; get

/*allows user to abort */
Show Custom Dialog (" You Are about to import file $filename. Do you want to continue")
if get(lastmessageChoice) = 2   /* cancel button */
      set field [ globalContainer ; "" ]
     exit script
end if

/* Since it is drag and drop, you cant insert as reference. Need to get set a temp location for export and then and re-insert the file into the same globalContainer but this time as a reference path */

set var $temp ; get( temppath)
set var $filename ; getvalue(globalContainer);1)
set var [ $tempFile; $temp & $filename)
export [ globalContainer ; $tempFile ]
insert File [ Reference; Never compress; globalField ; $tempFile]

set var $Path; getValue ( globalFIeld ; 2)
if     PatternCount( $Path ; ".csv" ) >= 1
                   import records ( no dialog; $Path)     /* csv order into empty temp table */
else if PatternCount( $Path ; ".xlsx" ) >= 1
                   import records ( no dialog; $Path) /*imports in correct order for xslx

else
         show dialog ( "Please select a valid csv or xlsx file")
         set field [ globalContainer ; "" ]
         exit script
end if

/* Validate header fields to make sure file imported in correct order
go to table [ tempImport
go to record [ first
set var1 [ temptable:Field1]
set var2 [ temptable:Field2]
...

if $var1 <> "ColumnName1" OR $var2 <> "ColumnName2" .....
       show dialog ( Original File Columns out of order"
      exit script
end if

import from temptable to final tables

____________________________________________
*please note that for import lines to work and have the ability to map the fields you need to create actual dummy file and have the source file set up as. once field mapping is done you change / remove the actual file so that you break the link. Once the link is broken it will evaluate your variable and point to the right file.
e.x.    I set up the import step with this, mapped the field, then deleted the actual .xlsx file but retained the reference in the script step


2 people found this helpful
Like • Show 1 Like1
Actions
badmonkey842 @ chuckburr on Nov 14, 2018 10:44 AM
Mark Correct
Correct Answer
sorry read this too late... didnt realize the contents can vary.. thought it was just a csv vs xlsx difference
1 person found this helpful
Like • Show 1 Like1
Actions
badmonkey842 @ eric on Nov 14, 2018 10:50 AM
Mark Correct
Correct Answer
yes, the insert as a reference in order to get the filepath value for downstream import script step.

As mentioned in my original reply only tested/implemented in a windows dekstop environment and unsure if webDirect or Go can utilize. those environments are not approved at my day job facility. can webdirect and go utilize PSOS to handle the export/ reinsert as reference?

anyway I g2g im late...
Like • Show 0 Likes0
Actions
eric @ badmonkey842 on Nov 30, 2018 7:21 PM
Mark Correct
Correct Answer
badmonkey842 wrote:
can webdirect and go utilize PSOS to handle the export/ reinsert as reference?
Yes, especially useful for Go.
FileMaker Pro 17 Advanced Help
Like • Show 0 Likes0
Actions
chuckburr @ eric on Nov 30, 2018 7:29 PM
Mark Correct
Correct Answer
Note, WebDirect and PSOS cannot directly import records from FM table to FM table like FM desktop can. You do have to go through the export/import shenanigans.
Like • Show 1 Like1
Actions
fmpdude @ chuckburr on Dec 1, 2018 4:27 AM
Mark Correct
Correct Answer
You can always create generic JDBC logic that will automatically read the database structure from one database (not necessarily FMP), using DBMetaData, and sync with another database (again, not necessarily FMP).

A while back, I wrote JDBC logic that sync between MySQL and FMP. It's totally automated. I scheduled that logic to run using Mac scheduler back when I was using it and it ran perfectly on that interval. This technique is even easier than manually doing something in FMP Desktop. Of course, I had to write that logic and that took a little while, but it's done. Automated. Free. Fast.

---

“Don’t document the problem, fix it.”
– Atli Björgvin Oddsson
2 people found this helpful
Like • Show 0 Likes0
Actions
beverly @ badmonkey842 on Dec 1, 2018 4:32 AM
Mark Correct
Correct Answer
badmonkey842
You said in whole or in part:
I am a beginner and was able to find "how -to", from a variety of ppl. So i cant take credit...
LOL. I'm NOT a beginner and I don't know anything but what others have taught me.
Beverly
Like • Show 1 Like1
Actions

Drag and Drop csv file and import: script steps

Attachments

Outcomes

Related Content

Recommended Content