Pipe separated values

[Ian Murphy]

Everything that I've found concerning pipe-separated values seems to be a couple of years old, so I'm sticking my head up in case anything's changed (including in FMP 11, which I don't own).

At the local public library, the electronic catalog spits out a report of requested items (for staff to pull from the shelves for patrons) in a very ugly format. Huge text on the page, too much information, choppy. So my goal is to write a standalone solution that they can pop the data into that will pop the results out as a report that's more ergonomically practical. Every time it's opened it prompts the user for the file to import, then opens a print dialog; every time it's closed it erases all its records. Very simple stuff.

Getting the data out of the catalog is easy, and offers no customization. There's a single menu item marked 'export,' and it saves a .csv file after the user browses for a location. The data it contains looks like this:

NONFICTION|811GIB|Gibran Kahlil 1883-1931.|The prophet|32821009222044

NONFICTION|959.704 HAL|Halberstam David.|The making of a quagmire : America and Vietnam during the Kennedy era|32821008213879

It's already not great that this file is calling itself a .csv, because as you can see in the sample data above, a comma is a part of the live data (e.g.

Gibran, Kahlil, 1883-1931

is becoming

Gibran Kahlil 1883-1931

. What I need is a user-friendly (i.e. automated, or one-button) way to whip this data into shape so FMP will see it. I'd rather not keep doing what I did today (changing file extension to .txt, importing into Excel as pipe delimited, saving as .xlsx), because it's too many steps and outside the comfort zone of the library staffers who'll eventually be doing this every morning.

Thanks in advance, as always, for your input.

[comment]

If the import is scripted, the extension of the file is not important. You can import the entire row into a single field, by specifying 'Tab-Separated Text Files' as the file type in the Import Records[] script step (assuming, of course, the text doesn't contain any tab characters). Then parse out the imported data into 5 fields.

[Lee Smith]

Hi Ian,

You can also import your raw file into Excel, If you have it, export it out as either a tab or CSV delimited file, and then import that file Into FileMaker.

BTW, you will need to choose the pipe character as the delimiter when you import the raw file into Excel.

Unfortunately, my Microsoft office is only usable in Snow Leopard and below, and I can't use it in Lion, so I can't test your text against it.

[Ian Murphy]

@Lee

My hope was to avoid moving through Excel at all . . . for the sake of usability, I'm hoping to minimize the number of steps for the staff in this daily routine.

@comment

So I think you're saying I'll have six fields - Author, title, barcode, category, call number, and a sixth one I'd call OMNIBUS . . . which would, per record, split its contents out to the five remaining fields? Would I write a formula that worked like grep and took the contents between the first two pipes and inserted it into the first field, then the contents between the second and third pipes and inserted it into the second field, and so on?

[comment]

Yes, and the formula could be as simple as =

Let (

v = Substitute ( OMNIBUS ; "|" ; ¶ )

;

GetValue ( v ; n )

)

with n being 1 for the first field, 2 for the second, etc.

[Ian Murphy]

Solved!

Thanks, @consultant. Elegant. And once again, a reminder that if I mastered formulas, I'd be much better off.