exporting in csv format

[marco b]

Hello,

I need to export data from filemaker to a pervaisive sql database.

They need csv format.

I ve expoted with filemaker 6 under windows selected csv and windows ansi

Filemaker puts "" around every field and uses , instead of ; and doesnt use a , or ; after the last record.

does anybody know how to export from fm to in standard csv ?

thx

Marco

[EddyB]

Hi Marco,

Welcome to the forum!

Using a , instead of ; seems correct to me being CSV - comma seperated values.

Also after the last record, there is nothing to seperate it from so I would not expect a comma to appear here.

Ed.

[marco b]

Hi thx for your reply

fm also puts every exported field between " " according to pervaisive people this is not possible to import.

Marco

[EddyB]

Hi Marco,

You may get a better result if you have a look at XML export. I have not done this personally but apparantly you can get a cleaner export for your needs this way.

If you have a look in your FM folder, there shoud be an Examples Folder. In there is an example of doing an XML export.

I hope this helps

Regards

Ed

[BobWeaver]

Filemaker puts quotes around the field data just in case the data contains a comma. A comma would, of course, confuse the importing application into thinking it was a field delimiter. Properly designed software that can import csv files should be able to deal with the quotes. Honestly, csv is a horrible file format to work with. I don't understand why everyone wants to use it.

[DanBrill]

This is the only way I've ever seen csv work. It puts quotes around any field with a comma in it so that it knows its not a new field. If a field contains a quote it puts that field in quotes too, and replaces the field's quote with two quotes.

So

I said "throw me the ball, Joe."

Is exported as

"I said ""throw me the ball, Joe."""

I tried this with both excel and filemaker. The only difference I can see is that FM will put quotes around every field. Excel only puts them there if there is a comma or quotes in the field. But since csv can have quotes around fields, the fact the FM does it every time shouldn't be a problem. A little overkill maybe, but nothing fatal to be sure.

[Vaughan]

Also make sure that paragraphs are stripped form all field data. Paragraphs are end-of-row delimiters.

I've tried XML export, but I ended up having to make a calc field that did all the double-quote and quotes-around-commas business, as well as strip out paragraph marks.

It's still not working for me. ;(

[BobWeaver]

That's surprising. I haven't done anything with XML yet, but I had assumed that Filemaker would convert the special characters to the appropriate meta-characters. Oh well.

Then again, maybe it's not surprising.

[Vaughan]

I've been having a lot of trouble, partly due to my learning curve, and partly due to the dumb-as file format required for the "big iron" system: it's a CSV file but the first line as to be "FORMAT OFFER STANDARD ," which means it isn't a standard CSV file!!! Arrgh. So I need to learn XML.

And to make matters worse the tech person I deal with opens the files up in Excel which changes them before they read them, or so I suspect.

I just managed to solve one problem though.

When exporting XML the export step allows you to specify the path (or url) to the XLS file in a field. I used the Status(CurrentFilePath) function to generate the string to the XLS file (the XLS file and the FMP converter file are in the same folder on the user's hard disk). Well, it works in MacOSX but broke in Windows. Turns out the string that Status(CurrentFilePath) function creates is "file://C:/whatever/" where the "file://" text and forward slashes are choking the system. It's generating the string in standard URL format which is admirable but doesn't seem to work for FMP Windows. A quick Substitute() or two got rid of the "file://" text and converted the / to so it now says "C:whatever" and it works.

Moving ahead, slowly.

[Fenton]

This collection has a simple "Custom Line Ending" XSL file. You'd still need to do the first line on its own. I'm afraid I can't do it right off, it's late and my brain has gone into "fuzzy" mode. But I think it would not be too hard. Then you can give whatever the heck they want. They don't like "Merge" .mer files (which are like .csv, but without the quotes in the first line)?

http://transfer.filemaker.com/collection/XMLExamples.zip

[Christian Coppe]

I had the same problem, as my client wanted csv files. I solved it by creating a concatenated field with my data to export, like : data1&","&data2&","&data3... It works well. Never open it with Excel.

Christian

[marco b]

I frw the information you gave here about the "" in the csv format to the helpdesk of the perv db and talk with progr dep and no it is ok they can import

with the "".

However they still have a problem with the comma seperater they want a ; instead of a , and a ; at the end of the file. they recomende to use a macro to change the , to a ;

does anyone know where to find example macro scrips for windows text files

thx

Marco

[marco b]

Hi Christian

I also thought about that option but with arge export isnt ther a limit to the field data and also perv app needs path to file to import and this is harder to script wit a field

[spb]

There is a 64,000 character limit for a single text field. If your data will fit within that, then you can concatenate into a single field for export as described above. Doing it this way gives you control over the delimiter. You can set the comma to ; in your calculation. You can also avoid quotes if it helps. I would export the single field result as tab-delimited. With only one field, there will not be any inserted tab characters. And tab export will not place more quotes around the result:

data1&";"&data2&";"&data3

I'm not sure about how or why you need to export the path. When I do this sort of thing, my script exports to the same location every time, with the same filename, overwriting the previous day's export. So the target application is always looking in the same place.

Steve Brown

[Fenton]

This is easy to do in XML. But there is a question of what exactly you want. At the very end, do they want just ";", or "Windows line ending;", or are they happy with "Unix line ending;"? I can post the code either way, but it's slightly different.

I'm assuming there are no ";" in the text itself. If so then I guess the quotes would be needed between fields.

Also, if XSL file and the FileMaker file are in the same relative locations, is it really necessary to use a calculated file path? Doesn't FileMaker save the relative location in the Export script? (I don't know for sure, as I'm only on one computer, but the downloaded examples certainly work, which they wouldn't if the Export didn't save the location.)

[marco b]

Yes your right fm automatcall uses the same locations.

with the one calc field and tab export it should work

what is the diffirence when exp in xml file ? I get then a different format how can you change it in csv looks?

thx

[Fenton]

When you export an XML file, you usually use another file to do the XSL transformation of the XML into whatever you want. The XSL file is a text file, in XML language (XSL being a subset of XML). The final result is a text file, but can be XML, text or html. In this case text.

XML is not a "format," it is an "extensible" (that's what the X stands for) language. So the result can be pretty much whatever you can get from and/or add to the data in FileMaker.

I can't really show what my question was asking. But I need to know exactly what the ending of the file should be. The line endings would be Windows I assume, ie., 


Quotes can be added, but would only be needed if there are ";" in the text somewhere; otherwise it sounds like they're optional.

Example1, ";" but no line ending

McBadden;Heather;Blue;99

Williams;Ann;Red;303

Morrison;Albert;Red;201;

Example2, line ending then ";"

McBadden;Heather;Blue;99

Williams;Ann;Red;303

Morrison;Albert;Red;201

;

[Oldfogey]

Vaughan,

You are dead right - Excel does change things before it opens them. I was using Excel to check some files exported from FMP and it changed them to not what I wanted! One of my customers' procedures has a note "Do not be tempted to open this file with Excel. Excel will wreck it."

Back to the main issue - CSV files. For many years I was under the misapprehension that CSV meant 'CSV'. It doesn't. CSV means 'what I think CSV means'. In short, Marco, you need to talk to your 'pervaisive sql database' techo and find out what it thinks CSV means.