Jump to content

exporting in csv format


marco b
 Share

This topic is 6534 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. ;(

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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

;

Link to comment
Share on other sites

  • 3 weeks later...

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.

Link to comment
Share on other sites

This topic is 6534 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.