export as tab seperated value or excell format
2 of 2 people found this helpful
There are a couple ways that come to mind.
1. loop through found records and set a global text field how you would like (commas, no quotes as needed) and then export field contents to a text file and save it with a "csv" extension.
2. create a calculation field that formats all fields how you would like (commas, no quotes as needed) on a single line, then export only that one field as tab format, but name it with a "csv" extension
Those are the first two that come to mind at least. Hope that helps.
#2 is my preference! Export Field Contents can have problems in other systems (BOM and UTF-16).
Bevery, that is true, and what exactly gets exported can change depending on the version of FM (I think the latest exports UTF-16 LE, if that works for you). I've had more luck with excel compatibility by setting it to UTF-16 BE.
I used to have some applescript to set it, but now I use the excellent Base Elements plugin, which uses the same lib (iconv) to set the text encoding. BE_SetTextEncoding – BaseElements Plugin Help Centre
Yes, plug-in helps (for Server scripts or FMGo or Web Direct, this must be on the server).
Thanks for the additional possible solutions, Mike!
With Beverly in the room I am surprised no one mentioned XML + XSLT yet. This way you end up with a nice UTF-8 file.
If you export CSV without quotes you have to be sure that your data does not have Carriage Returns or it will mess up your export.
LOL. starting with step one: calculated field, export as .tab (change extension to .cvs)
does it do what you want?
Yes. ok. good you are done
No. ok. what more do you need?
If you need some formulas, if you need some formatting, if you need to create a pivot table, ....
THEN try XML with XSLT.
And good call on the returns-in-field. The other is the "," in a field. CSV with just comma between fields? well, you can guess what happens if there's one 'in-field'!
I used the second process. "create a calculation field that formats all fields how you would like (commas, no quotes as needed) on a single line, then export only that one field as tab format, but name it with a "csv" extension"
That worked well. Thank you!
1 of 1 people found this helpful
I prefer the field to be unstored if used only for exporting.
This should be simple to do. Should be able to export data to the temp folder, then import to a variable or a field, then use:
Set Field [ Table :: Field ; Substitute ( Table :: Field ; Char ( 34 ) ; "" )
That removes the quotes, Then export field to desired location.
Also Should be able to export data to the temp folder, as Tab separated, then import to a variable or a field, then use:
Set Field [ Table :: Field ; Substitute ( Table :: Field ; Char ( 9 ) ; "," )
This replaces the Tabs with commas, Then export to desired location with .csv appended to file name.
These have same limitation that the result become UTF-16. (I didn't test it using container field instead of text)
And you need take care of field value don't have Char(34) or Char(9).
Hope this helps:
I use a script like below (for OS X and iOS). And use insert from URL from the TemporaryPath and filename with .txt to a Text Field. Then Export the Text Field. When I export the Field content to my MAC, TexWrangler reports that it is UTF-8, with BOM, Unix (LF).
# Data Table 1 with many fields and records
Go To Layout [ Data Records 1 ]
Set Variable [ $Path ; Value: “file:” & Get ( TemporaryPath ) & “Coord.txt” ]
# Use File Type: Comma-Separated Text - and set Field Export Order
Export Records [ With dialog: Off ; “$Path” ; Unicode (UTF-8) ]
# Data Table 2 with many fields and 1 record
Go To Layout [ Data Records 2 ]
Set Variable [ $Path ; Value: Get ( TemporaryPath ) & “Coord.txt” ]
# Use This for OS X and iOS
Insert from URL [ With dialog: Off ; Data Records 2 :: Field ; “file:/“ & $Path ]
# Remove double quotes
If [ PatternCount ( Data Records 2 :: Field ; Char ( 34 ) ) ]
Set Field [ Data Records 2 :: Field ; Substitute ( Data Records 2 :: Field ; Char ( 34 ) ; “” ) ]
Export Field Contents [ Data Records 2 :: Field ]
Note, For Windows I use:
Insert from URL [ With dialog: Off ; Data Records 2 :: Field ; “file://“ & $Path ]
On OS X you get same result as Windows, the files are UTF-16; Only on FM Go the result is UTF-8.