Formatting CSV files

[Jalz]

Hello Team,

I have a calculated field which the calculated result is text.

A simplified version of the calculated field is "SR" & "," & "Account Reference" & "Net".

When I export that field, the result that comes out as a CSV file is :

"SR,ABCDE,100". I would like to format the field so that the export of each field has speech marks in between each value i.e. "SR","ABCDE","100".

Is this possible?

Thanking all in advance.

Jalz

[EddyB]

Hi Jalz,

This should do it:

"""SR""" & ",""" & Account Reference & """" & ",""" & Net & """"

Ed.

[Jalz]

Thanks Eddy for your quick reply,

I've tried your method but doesnt work, its producing output like """SR"",""ABCD"",""1000""".

I feel so close yet so far. Maybe I can try exporting the quotations using ascii characters???

Jalz

[EddyB]

Jalz, is the output in your calculation field correct and it is just the export file that shows incorrectly?

Ed.

[Jalz]

Hi Eddy,

The field on the layout looks perfect, just as you've described. However when I export the field, the "extra" quotations marks appear around the fields.

Any more help would be greatly appreciated

Jalz

[EddyB]

I tried this and exported my calculated field into a csv file and the quotation marks were as they were in FM. Which version of FM, and what platform are you using?

Ed.

[Vaughan]

It sounds to me like you want to export to a file in CSV format, and you're trying to build the CSV format using a calculation field. The quote character is reserved for the CSV format, which is causing your problem.

Best bet is to export through XML to create a CSV format file. Unfortunately I know little of XML to explain how to do it, though others may be able to help.

[Jalz]

Thanks guys,

I think I'm going to have another attempt at it today.

Eddy, can you attach the file that you created on this forum for me to analyse? as if you've got it to work at your end then I must be doing something awfully wrong.

Im working with FM Pro 6 on a Windows based machine.

Vaughan, I thank you for your suggestion, I might have to look into XML export if Eddy's method doesn't work.

Thanks

Jalz

[EddyB]

Hi Jalz,

I've had another look at this.

After exporting the calc field I had been opening the exported file in Microsoft Excel, all was well.

I tried opening the file in Notepad and I received the result you described. I guess this is what you are doing.

Excel must be formatting the exported file and displaying it correctly.

When you export out and open the file in Notepad it adds it's own quotation marks.

I created a field called quotation and in this I entered "

I then exported this field and opened it in Notepad and I see " " " "

What it is doing is firstly putting quotations around the whole string, so for example if you export the words FileMaker Pro in Notepad you would see "FileMaker Pro".

Secondly, whenever it finds an unmatched quotation it matches it, so if you exported a field which contained " " " in notepad you would see

" " " " " "

There must be someway around this, I'll have a play with it and get back to you...

Ed.

[Ugo DI LUCA]

In Excel, the exports look fine for me as long as the SR part is a constant string in your calc. If SR was to be SY, it would result SR at the end. I don't have Notepad, but I don't understand why it doesn't work in that format.

[itsisnofun]

jaliz

change the calc field to this format

"SR"" & "," & ""Account Reference"" & "," & ""Net"

Your calc result should look like this

sr","account reference","net

A csv file export will always format the data in a field with "" front and back has this is the delimiters for a csv file. The , goes between each feild of data also added by the export routine and you can't change this.

Mike

[spb]

I don't know if this will help you or not, but I've had a lot of trouble getting .CSV exports to be just right. A workaround I've been using is to set up a calc field that strings together the appropriate fields, delimiters & quote marks exactly the way the resulting text file should be. Then I export this field only in tab-delimited format. This format does not try to add or subtract quote marks. As there is only one field, there are no tab characters to confuse the issue. The resulting text file can be renamed to file.csv, if needed, though naming it the more generic file.txt works quite well.