Importing flat files into FMP
Importing flat files into FMP
- Subject: Importing flat files into FMP
- From: Eric Schult <email@hidden>
- Date: Mon, 25 Nov 2002 14:16:27 +0000
I'm trying to import flat text files into FileMaker Pro, and so far it's
VERY slow going. I favor use of Tanaka's OSAX over Open for Access to read
the files. My experience over the years has been that it's faster and the
syntax is easier.
But I've never tried having a script handle this kind of volume of importing
before. I have 250,000+ flat files to read, translate (using ACME Script
Widgets) and import into FMP. The flat files are all exported from an
archive of news stories (Stauffer Library System), which is soon to go
defunct. This archival system is proprietary, and one can only export
individual records as flat files - otherwise I'd export it in chunks as
tab-delimited text, and that would import into FMP a whole lot faster.
I've been exporting from Stauffer one month of data at a time, which amounts
to about 2,800 records, all to a single, dated target folder. I know having
that many files in one folder is asking for trouble, but I have 12 years of
data to move, and smaller increments than a month is equally unpalatable.
I am running the import script from within FMP's script menu, which tends to
maximize script performance. I'm also keeping the flat files local, so there
aren't any network bottlenecks with which to contend. Still, I'm processing
only a few hundred records per hour, and at that rate, this project is going
to take months. (And I can't even be sure how well FMP Unlimited will
perform with a database of this size. I'll have about two dozen users
accessing it, fairly infrequently.)
Can anybody guide me about speeding up this process, and tell me whether I'm
kidding myself about FMP handling this?
My flat file format looks something like this:
--------------------------
"DATE: Thu 03-Oct-2002
PUBLICATION: JT
CATEGORY: SP
AUTHOR: WIRE
LOCATION: D3
FULL TEXT:
Giant step
By PAUL NEWBERRY
Associated Press
ATLANTA Barry Bonds didn9t have to come up big for the San Francisco
Giants to get a jump on the Atlanta Braves.
The rest of the San Francisco hitters knocked around Atlanta9s heralded
pitching staff while Russ Ortiz threw seven strong innings, carrying the
Giants to an 8-5 victory Wednesday in Game 1 of the NL division playoffs.
3Obviously, Barry ..."
--------------------------
My script reads like this:
set masterFolder to "Eric's HD:Stauffer Exports:Convert these ...:"
tell application "Finder"
set subFolderList to (every folder in folder masterFolder) as list
if subFolderList is not {} then
repeat with i from 1 to count of items in subFolderList
set currentFolder to item i in subFolderList
set fileCount to (count of items in currentFolder)
tell application "FileMaker Pro"
activate
display dialog ("Reading " & fileCount as string) & ,
" files in folder \"" & (name of currentFolder) & "\"." & ,
return & return & "Stand by ..." giving up after 5
end tell
repeat with j from 1 to fileCount
if (keys pressed) is {"Option"} then -- for checking progress
tell application "FileMaker Pro"
activate
display dialog "Writing record " & (j as string) & ,
" of " & (fileCount as string) & ,
"." with icon caution giving up after 5
end tell
end if
set currentFile to item j in currentFolder
set fileName to name of currentFile
set fileContents to (readFromFile currentFile) -- tanaka's
set dateLine to (pickUpFromData fileContents startOf "DATE: " ,
endOf return) as string
set dateLine to trim "DATE: " off dateLine from front side -- ACME
set dateLine to trim return off dateLine from back side
set pubLine to (pickUpFromData fileContents startOf "PUBLICATION: " ,
endOf return) as string
set pubLine to trim "PUBLICATION: " off pubLine from front side
set pubLine to trim return off pubLine from back side
set catLine to (pickUpFromData fileContents startOf "CATEGORY: " ,
endOf return) as string
set catLine to trim "CATEGORY: " off catLine from front side
set catLine to trim return off catLine from back side
set byLine to (pickUpFromData fileContents startOf "AUTHOR: " ,
endOf return) as string
set byLine to trim "AUTHOR: " off byLine from front side
set byLine to trim return off byLine from back side
set onPageLine to (pickUpFromData fileContents startOf "LOCATION: " ,
endOf return) as string
set onPageLine to trim "LOCATION: " off onPageLine from front side
set onPageLine to trim return off onPageLine from back side
set quickWordsStart to offset of "QUICK WORDS:" in fileContents
set quickWordsEnd to offset of "FULL TEXT:" in fileContents
set quickWords to (characters (quickWordsStart + 12) thru ,
(quickWordsEnd - 1) in fileContents) as string
set quickWordsList to tokenize quickWords with delimiters return
set newQuickWordsList to {}
repeat with k from 1 to count of items in quickWordsList
if item k in quickWordsList is not "" then
copy item k in quickWordsList to end of newQuickWordsList
end if
end repeat
set keywords to join list newQuickWordsList with delimiters " "
set trimFields to (characters 1 thru (quickWordsEnd + 10) ,
of fileContents) as string
set bodyCopy to trim trimFields off fileContents from front side
set extraReturns to true
repeat until extraReturns is false
set bodyCopy to trim return off bodyCopy from both sides
if character 1 of bodyCopy is not return then
set extraReturns to false
exit repeat
end if
end repeat
tell application "FileMaker Pro"
tell database 1
create new record with data ,
{fileName, dateLine, "", pubLine, catLine, byLine, ,
onPageLine, keywords, bodyCopy}
end tell
end tell
end repeat
end repeat
end if
end tell
I'd appreciate any help that might be out there!
- wes
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.