Import Multiple CSV Files By Script

[bstrad]

Hey all,

I have many, 200+, csv files that are updated very often. I am wanting to be able to auto import all of these csv files into a table in filemaker.

So I wrote some code in C# to extract all my file names as follows

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, TMA 377B, SEC 001, TMA, Teacher1.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, SCED 476, SEC 030, TMA, Teacher2.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, MT, F09, SCED 476, SEC 030, TMA, Teacher3.csv¶

I then have put these into an array inside filemaker, all 200+ file directories.

I then set a variable to change what file extension it is defined as, and go through the entire array one by one.

I was wanting to set up a loop where I could import all of these files by using the import function as follows.

Import Records["$path"; Add; Windows ANSI]

$path changes as follows

Set Variable[$path;Value:MiddleValues($directories;$counter;1)]

$counter = $counter + 1

In my data viewer the $path is changing correctly to the next path as it should.

My problem is that when Import Records["$path"; Add; Windows ANSI] is called the second time and then after it seems to just skip the script, so it will only perform the import function once.

I also tried to do multiple calls of the Import Records script and it still did the same thing.

Any ideas on how I could get this to import all my csv files calling the script only once?

[bcooney]

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, MT, F09, SCED 476, SEC 030, TMA, Teacher3.csv

Is not a valid path. You cannot have commas in a path.

"I then have put these into an array inside filemaker, all 200+ file directories." --What do you mean, since FM doesn't have arrays?

It would be helpful to see the script (post a screen capture or print the script to PDF).

Basically, if all the files are imported into the same table, your approach is valid. You would set a $var to the return-delimited paths. Then loop thru using a $counter until GetValueCount ( $paths) = $counter.

[bstrad]

Thanks for the reply.

As far as I know the paths are valid. The commas are only part of the file name.

ex: CPAS, US, W10, TMA 377, SEC 001, TMA, Teacher1.csv is the name of the file in the directory filewin:/C:/Direc/TMA/CPAS/W10

Since FileMaker does not have Arrays I set up a variable as you can see below, that had the pilcrow as a delimiter, which is the default for FM.

I guess one thing I might not have been clear on is that these are not .txt files, they are .csv.

I was making this script go to each .csv file and import it.

Below you can find the script.

I very much appreciate the help!

Set Variable[$counter; Value:1]

Loop

Set Variable[$directories; Value:"filewin:/C:/Direc/TMA/CPAS/W10/CPAS, US, W10, TMA 377, SEC 001, TMA, Teacher1.csv¶

filewin:/C:/Direc/TMA/CPAS/W10/CPAS, US, W10, SCED 476, SEC 015, TMA, Teacher3.csv¶

filewin:/C:/Direc/TMA/CPAS/W10/CPAS, MT, W10, SCED 476, SEC 015, TMA, Teacher4.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, TMA 377C, SEC 001, TMA, Teacher5.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, TMA 377B, SEC 001, TMA, Teacher6.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, SCED 476, SEC 030, TMA, Teacher7.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, MT, F09, SCED 476, SEC 030, TMA, Teacher2.csv¶"]

Set Variable[$foundCount; Value:ValueCount($directories)]

Set Variable[$path; Value:MiddleValues($directories;$counter;1)]

Import Records[No dialog; “$path”; Add; Windows ANSI]

Perform Script[“Merge Records”]

Set Variable[$counter; Value:$counter+1]

Exit Loop If[$counter>$foundCount]

End Loop

[bcooney]

When you Import Records, you must be on a layout based on the desired table occurrence. So, perhaps you are not returning to the correct layout?

I would have set $counter to Valuecount ($directories) and then $path to GetValue($directories, $counter) and then decremented $counter. When $counter = 0, I'd exit loop. But, your way probably is fine.

I don't have a PC right now to test this comma thing. It just looks wrong.

[bstrad]

So I figured it out. The code I wrote in C# needed a very slight modification. I was entering in a new line after the pilcrow.

So instead of looking like this

Set Variable[$directories; Value:"filewin:/C:/Direc/TMA/CPAS/W10/CPAS, US, W10, TMA 377, SEC 001, TMA, Teacher1.csv¶

filewin:/C:/Direc/TMA/CPAS/W10/CPAS, US, W10, SCED 476, SEC 015, TMA, Teacher3.csv¶

filewin:/C:/Direc/TMA/CPAS/W10/CPAS, MT, W10, SCED 476, SEC 015, TMA, Teacher4.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, TMA 377C, SEC 001, TMA, Teacher5.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, TMA 377B, SEC 001, TMA, Teacher6.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, SCED 476, SEC 030, TMA, Teacher7.csv¶

filewin:/C:/Direc/TMA/CPAS/F09/CPAS, MT, F09, SCED 476, SEC 030, TMA, Teacher2.csv¶"]

It now looks like this

Set Variable[$directories; Value:"filewin:/C:/Direc/TMA/CPAS/W10/CPAS, US, W10, TMA 377, SEC 001, TMA, Teacher1.csv¶filewin:/C:/Direc/TMA/CPAS/W10/CPAS, US, W10, SCED 476, SEC 015, TMA, Teacher3.csv¶filewin:/C:/Direc/TMA/CPAS/W10/CPAS, MT, W10, SCED 476, SEC 015, TMA, Teacher4.csv¶filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, TMA 377C, SEC 001, TMA, Teacher5.csv¶filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, TMA 377B, SEC 001, TMA, Teacher6.csv¶filewin:/C:/Direc/TMA/CPAS/F09/CPAS, US, F09, SCED 476, SEC 030, TMA, Teacher7.csv¶filewin:/C:/Direc/TMA/CPAS/F09/CPAS, MT, F09, SCED 476, SEC 030, TMA, Teacher2.csv¶"]

I also put in a go to the layout I'm importing in to make sure it worked properly, and no problems.

Thanks for the help bcooney.

[bcooney]

Ah! Couldn't see that from you post. Glad it's working.