Importing a text file with quote marks ( " ) as delimiters

[Fred in Seattle]

I have developed a database in FileMaker that dates back to the first versions of the software on the PC.  I am now using FM12 and would like to automate the manual file importing that I have been doing for years!

 

I use the database in my own company (we provide loan examinations to commercial banks).  The database contains all the loan and borrower information in the client bank's trial balance.  Over the last 20 years I have obtained the raw data from my client banks in ASCII format from their various different service providers.  Since the data varies widely from bank to bank I have always manually parsed the ascii data in Excel, then built my two related tables in Excel, and ONLY THEN imported the data to FM12.

 

Finally, over the last few years, the FDIC (the federal agency that insures bank deposits and also examines my client banks) has forced the banks to provide a uniform ASCII file.  So now there is no variation from bank to bank, and I can finally feel confident that I can automate the import.

 

The file is a standard ASCII file where each RECORD is delimited with quote marks, the individual fields are not.  When parsing in Excel, the RECORDS are properly listed, and the fields are identified by location.  For example, the "Borrower name" will start at character #95 for 50 characters in length.  "Loan Balance" will start at #146 for 12 characters.  Of course there are no headers or field names in the ASCII file.  Somehow Excel seems to be able to deal with the location for parsing.  I have no idea how.  Looking at the ASCII file in a non-wrapped text editor shows no clue as to how the FIELDS are divided.

 

I have a map of the required format, so in Excel it is easy to parse out a table of all the data since the map shows where one field starts and ends, and where the next field starts, etc.

 

When I attempt to IMPORT the raw ASCII file in FM12, I always end up with one field filled with the entire data content - no fields delineations, no record delineations.  I have tried all kinds of file TYPE imports, same thing.  I would really like to use an import script to at least get the darned thing into a table - I can script my way through the rest of the process, but I need to start somewhere.

 

If anyone is kind enough to offer suggestions I would be very grateful.  FM has never let me down before, and I have used it for maybe 15 years or so.  I've tried all the others, and nothing comes even close in my view.  I hope I can work my way through this one.

[Lee Smith]

Have you read about the file formats that FileMaker can import and export?

 

I believe that you can use the csv import format.

[doughemi]

if each record is delimited with quotation marks only, you could import the data into a field in a utility table with one record .  Then you could run a script that looks something like

Substitute[importField; """"; "¶"] // separate records into lines
Substitute[importField; """; ""] // drop initial and final quotes
Set Variable[$count; ValueCount(importField)
Set Variable [$loopcounter]; 1]
Loop
Set Variable[$currentline; GetValue(importField; $loopcounter)]
Go to Layout[<layout for your parsed data>]
New Record/Request
# now parse the line into individual fields and populate the fields with 
# Middle($currentline; x; y) and Set Field() script steps
# using your field map
Go to Layout[Original Layout]
Set Variable[$loopcounter; $loopcounter + 1]
Exit Loop If [$loopcounter > $count]
End Loop

EDIT:  be sure to use TrimAll() in your parsing steps to get rid of the padding spaces in the fixed-width input format

[No_access]

Usually for something like this I would just open notepad and do a find and replace.  then import to FM. However as an option,If you want filemaker to do the whole thing.  I have loaded text files into the webviewer, copied all the data to a text field, did a find replace  and then save as  yourfile.csv.  now you can import. this can all be done in one small script.

I like this technique because I find I always need to manually adjust a few lines that messes up the whole thing if not fixed prior to import.

[Fred in Seattle]

Have you read about the file formats that FileMaker can import and export?

 

I believe that you can use the csv import format.

Oh, yes - thank you.  I have read every help file, just about every forum entry, and the latest book on FM12.  You can use the CSV file type for import, but the results are far from acceptable in my case!  I find it odd that Excel imports the same file without a hitch.  I'm just trying to automate the process so there is no hands-on intervention. 

 

In fact very first thing I did was load the file into a text editor and did a search and replace of the quote marks.  I replaced with paragraph marks first and saved as CSV file, at least with that extender.  I tried a number of variations (I spent a couple of hours back and forth) changing record delimiters and changing extenders on the resulting file, and got the same result each time: everything loads into the first field of the FM12 file.  One giant string in that field.

 

If I can get it once, then I can automate the mapping, etc. into the tables (I hope).

Fred

[comment]

I find it odd that Excel imports the same file without a hitch. 

 

Doesn't Excel ask you to confirm the delimiters, before opening the file? Or perhaps it remembers the last choice?

 

In any case, Excel has a built-in guessing mechanism, and I presume that it interprets multiple spaces as column delimiters. I also presume that this might fail if values take up all the allotted column width, esp if they are in the first row. But then, IIRC, Excel has another mechanism for opening fixed-width tables.

[Fred in Seattle]

Doesn't Excel ask you to confirm the delimiters, before opening the file? Or perhaps it remembers the last choice?

 

In any case, Excel has a built-in guessing mechanism, and I presume that it interprets multiple spaces as column delimiters. I also presume that this might fail if values take up all the allotted column width, esp if they are in the first row. But then, IIRC, Excel has another mechanism for opening fixed-width tables.

Excel first asks (in the parsing mode) for the beginning line of the data. In the case of the FDIC text file, there are 6 lines of headers, etc before the actual data begins.  I think Excel uses the delimiters for the 7th line, and goes from there.  You can change delimiters or select "fixed width".  There is no limit on the size of the line.  In the FDIC file the lines are 600+ characters long.

Fred

[comment]

There is no limit on the size of the line.

 

Well, it depends on how you look at it - because in that file a "line" is 'a string that ends with a quotation mark', and it has an exact, predefined length.  Though record delimiters are not really required in a fixed-width table; you could have an unbroken string of data (padded with spaces as required), with no delimiters whatsoever.

 

In any case, if you are able to load the whole thing into a text field (preferably a global field), you should have no problem taking it from there. I believe Doug has outlined the path of least resistance in his reply above.

 

 

---

BTW, are the "records" enclosed in quotes, or is there only one quote mark separating two records?