exporting text files

[christoff]

hi,

If someone has asked me to supply them with a .txt file, is that basically the same as a .tab or .csv file?

[comment]

When you export as a .tab or .csv, you can specify the target file's name - including the extension.

[christoff]

Oh awesome! thanks comment. You've helped me quite a few times, cheers. The instructions for the file I'm meant to export is as follows and I'm wondering if this is do-able in FM: "This file format is essentially a flat file containing 4 record types. All records and fields therein are fixed length. Alphanumeric fields are to be left-aligned and padded with trailing spaces as required. Numeric fields are to be right-aligned and padded with leading zeros. Alphabetic data may be in either upper- or lower-case, or mixed."

[comment]

OK, this is something else. The best way to do a fixed width export is through XML - see:

http://fmforums.com/forum/showpost.php?post/337938/

If you only have a few fields/records to export, you could probably do this through a script that assembles the exported data (padded) in a global text field, then exports the field contents. But the result of that will be encoded as UTF-16, which some applications cannot handle. This can be circumvented by exporting a single record (and a single field) as tab-delimited. Altogether a lot of work for something that XML handles most elegantly.

[christoff]

I looked at the link and I'm sure I could do this if the intended recipient's IT guy took me thru it, but somehow I don't think that is gonna happen.

They sent me instructions on what to do but I don't understand them as they are just general instructions for everyone, regardless of the software they are using.

I understand how to import and use txt files in FM but not how to export/create a txt file that is usable by someone else. I guess I will have to have another look at their instructions.

Just in case there is someone in music publishing in Australia reading this...I'm trying to prepare a file in APRA's proprietary file format for bulk electronic registration of song titles.

I wish they'd just accept excel files, now that would be easy!

[comment]

General instructions would be sufficient - but the part you quoted is missing at least one crucial piece: the required length of each field. Without this, you have no hope of meeting the standard, regardless of the method.

I am also not sure what the "4 record types" refer to. And I would try and verify that are indeed no record delimiters in this format.

[christoff]

I just got off the phone to them and they've sent me a sample file, which I've attached.

Each record should have all fields in 1 line only, with all fields padded to certain lengths, even if a field is optional, then it's still padded out with spaces.

So the 1st record (or line) will have the Publisher info...

The 2nd record has all the info to do with the song title...

The 3rd record is the 1st songwriter of the song...

The 4th record is either the 2nd sharer of the songwriting royalties...or, all the info to do with the next song title and so on.

I'll try exporting the info with a txt extention and send it to them. Do you recommend I use PadCharacters?

thiet_pop_singer.txt

[comment]

I still don't see the full picture here. I am not sure what you're starting with, and cannot make out the "mapping" of that to the result. Your sample has 13 lines - that doesn't rhyme with 4 lines per record.

[christoff]

When I imported the sample I got 12 records (lines) which is made up of:

1 "P" line (for the Publisher)

1 "W" line (for the work/song title)

4 "S" lines (for the sharers of the song)

then

1 "W" line (for the next work / song)

5 "S" lines

I think only the Mandatory fields have been filled, leaving spaces where the Optional fields have been padded.

[comment]

I don't see how importing the sample (into Filemaker?) helps. I thought you needed to export data FROM Filemaker into this format. What's needed here is a simple map in the form of:

Take Field A (Text) and pad it to n characters, then Field B (Number), padded to m characters, and Field C (Text) padded to k characters.

Open up a new line.

Take Field (Text) D and pad it to x characters, followed by Field E (Number) padded to y characters.

Open up a new line.

...

At the end of the record, write "xyz". Repeat for each record.

---

(I am assuming this is coming from a flat table - if there is a parent-child structure, then one needs instructions for that as well).

Edited May 3, 2010 by Guest

[christoff]

Oh, I imported the sample into FM so I could have a look at what was in it, but I probably should've just used notepad.

I'm using that custom function which someone did which pads out fields but just having trouble with something...

How do I turn the percentage 12.5 into 0125000

I used:

Pad ( $WriterPerformancePercentage/100 ; "0" ; 7 ; "Left" )

but it ends up as .125000

[comment]

How do I turn the percentage 12.5 into 0125000

If it's a number (12.5), multiply by 10000, then pad.

If it's a percentage (12.5%), multiply by 1000000 and pad.

[christoff]

You're quite right, I was sort of doing the opposite to what I should've been doing. Multiplying by 10000 and padding to the right does the job.

Thanks again comment!

[christoff]

Hi Again.

I exported the file to the recipients (by email) and they said that I did in 1 email what it takes others sometimes 2 weeks to get right.

I just had another question for you. if on export I want to slightly change a title eg

The White Sky (le Ciel Blanc)

In this example the Titlecase function isn't working properly as there is no gap between the "(" and the "l"

Is this something i can fix with the replace function? Coz I'd like it to look like...

The White Sky ( Le Ciel Blanc )

[comment]

the Titlecase function

Ahm... there is no Titlecase function. But the Proper() function will return "The White Sky (Le Ciel Blanc)".

[Lee Smith]

I believe they are referring to Title Case, the Layout option for formatting the field.

[comment]

I thought this was about exporting. Title Case applied on the layout level will not export.

[Lee Smith]

Sorry comment. I was not being critical or anything, I was just pointing out that the OP was mixing up the names of the two functions.

Edited May 17, 2010 by Guest

[christoff]

Hi,

In my last post the 2nd time I used the word "export" I meant to copy some data from one table to another. so I possibly used the word incorrectly, but...

I used TextStyleAdd ( text ; Titlecase ) and it sort of worked but I will also try the Proper() function as it might work better...

If I'm trying to export an Excel file, how do I make good column headers along the top row. At the moment I either have the field names as the headers or if I deselect that box I have no headers at all.

How do I have good, readable headers?

EG if I include the field names as the column headers i'm left with "PeriodEnding" as a column header instead of something more readable like "Period Ending"