Ignoring the header question for the moment, I'm curious why you didn't just export it as a merge file (or other format), with the fields arranged in the export sequence you wanted?
You could export from a different calculation field with:
List ( "#" ; "# Header: keys to import" & "#" ; "First-name,Last-name,Address,City,State" ; "# Records: values to import" ; "#" ; Yourexisting FieldListingValuesGoesHere )
Phil - does that not suggest that James will be building the data from many records into one big field? At first I thought he was looping down the records building up one huge variable, but then he said he was giving an example of 2 exported records.
I think I'm confused.
All we know is that the data, as exported produces multiple records in the system that imports this data. We have no clue in this thread exactly how that data is assembled--could be from any number of different records from any number of tables or just fields from a single record.
That's why I suggested something that leaves the existing field unmodified.
The reason I can't use the merge is because as I said the export has to compensate for many fields that are NOT in my database. The header file defines these, and it's unfortunately immutable.
As a result the header/export looks more like this:
# Header: keys to import
# Records: values to import
"John","Doe","1313 Mockingbird Lane","Tuscaloosa","Alabama",,,,
The extra commas are basically trapping out the extra data that the template requires, plus the header field names are completely long and bizarre and I can't rename my database fields just to accommodate. So that's why I can't use a Merge.
The calc I am doing compiles all of this info for ONE record, and when exported in a found set of 20 records it will have 20 lines.
Would just like to have the first line to have the header info.
make it a calculation field with an If or case function that only prepends the additional data when get (RecordNumber) = 1.
create a dummy record where this field only contains the header data.
Hmmm, how are you exporting this data? If you export as CSV, you'll get quotes where you don't want the as Filemaker attempts to keep the commas in your data from being interpreted as field delimitters. If it's one line of text per field, I think a Tab export will work here...
Phil, I tried what you posted and it appears on every line in the export.
If ( Get (RecordNumber ) = 1 ; "headerInfohere" & ¶ ) & Yourexpression here
should only include the header info for the very first record in your found set.
List ( If ( Get (RecordNumber ) = 1 ; "headerInfohere" ) ; Yourexpression here )
should also work.
You've got some stray quotes/parentheses etc...but I did workaround it and have it working except for the line breaks are not correct in the resulting csv file. As a result the import is failing.
I can manually hit carriage return in the resulting file and it works but isn't there a way to include an ASCII code in a Filemaker calc that works?
I should also add that I am exporting as Tab delimited but renaming to .csv, and creating any needed quotes inside the calculation. Stupid thing works as I said, I just need the header.
¶ inserts a carriage return. The values returned from List () are also separated by ¶ everytime you see a ;, but any missing items of a list also omit the ¶ following it to keep the list without any empty values.
I would think you just need to include a ¶ in the place where you need that line break.
¶ does not insert a carriage return in the resulting export file. I have them all over the place within quotes. It looks that way in the field in FileMaker, but it doesn't get exported that way.
I've been trying char(10) and char(13) as well with no luck.
I've got all of my .csv files set to open in TextWrangler, and the app I am importing into (DeployStudio) won't work without the carriage returns.
I will be able to spend a little more time on this tomorrow, thanks for your help.
Oh yeah, the return get's converted into a different value during export and there no simple way with a text export, as far as I know that get's around this.
You might look into xml exports with an XSLT "grammar" designed to format the data as needed. I think that will work here.