Exporting to DOS

[spb]

In my ongoing project to gradually replace our company's creaking old DOS

system with spanking new FM modules, I've reached the point where my FM

purchase order module is ready for users. So I need to export into a text

file the day's work, then import this file into the DOS system.

The DOS system needs the PO header records & detail records all in the same

file, yet requires five fields exported for header and seven for detail (with

no empty fields allowed). The solution is two concatenation filds that rope

together the fields to be exported for each. Then a calc field that fills itself

with one or another of these, depending on the header/detail flag.

So far, so good. My final export calc field looks perfect onscreen. But I

am having a quote problem. For this field to emulate the standard .csv

format, "FieldA","FieldB","FieldC", I need to embed the double quotes

correctly in the concatenation calculation. In addition, I have to be

aware that the export will treat this as one huge field, and place a set of

quotes around the whole thing, so I should not embed the very first and

last quote.

Experimentation (and dim memory of some prior discussion here) shows me

that FM has a tough time inserting a double quote as a text object. I need

to bracket it with *more* double quotes. I seem to recall someone saying

that you need four double quotes in a row to do this job, but FM gives me a

syntax error. Three in a row works. Here's the calc:

FieldA & """,""" & FieldB & """,""" & FieldC

This seems to decode properly when the result is displayed onscreen.

However, when I export it and open in a text editor, I find that *extra*

quotes have been added in (including the expected leading & trailing set),

giving me a result that looks like:

"" FieldA "","" FieldB "","" FieldC ""

(There are no spaces between quotes & data, I inserteds them above for

visual clarity.) The DOS app gives me an "unknow field type" error when I

try to use it anyway.

FYI, though I mostly work on a Mac, this work was done on Win2000, FM 5.5,

files exported alternately with .csv and .txt suffixes (didn't seem to make

any difference).

Anybody have any ideas?

Steve Brown

[jasonwood]

FieldA & """,""" & FieldB & """,""" & FieldC

What are the commas for? Did you want them to be exported?

If memory serves me, if you want a ", you have to write """" (4 of them). You must then surround them by &'s to join them to other things.

Can you show exactly how you want it to output?

[spb]

QUOTE: What are the commas for? Did you want them to be exported?

Yes. In this example, I'm concatting three fields into one, then exporting the one field. But I need to fool DOS into thinking that it is getting three fields. The convention in a .csv file is for fields to be surrounded by double quotes and separated by commas, with a line feed between records.

QUOTE: If memory serves me, if you want a ", you have to write """" (4 of them).

That's what I thought, but putting in four sets of quotes gives me a FM syntax error. Three sets seems to work (FM 5.5 on either Win or Mac), and the calculated result onscreen is correct.

QUOTE: Can you show exactly how you want it to output?

Sure. In this example, I'd like the exact field contents to look like this:

FieldA","FieldB","FieldC

Note that it does not begin or end with a quote. When I export this to .csv, the entire thing is treated as one field, and quotes are automatically placed before and after, giving me a text file result of:

"FieldA","FieldB","FieldC"

This should be what DOS needs to read. This is what my concat field looks like, but when I export then open the result in a text editor, I find that an extra quote has been inserted next to every one. The DOS app will not read it.

Steve Brown

[jasonwood]

Try this...

FieldA & """" & "," & """" & FieldB """" & "," & """" & FieldC

The thing that really confuses me is that your calculation works on screen but not the export. I wonder if you could put those quotes at the beginning then export as text than change the filename ".csv"...

[spb]

Try this...

FieldA & """" & "," & """" & FieldB """" & "," & """" & FieldC

The thing that really confuses me is that your calculation works on screen but not the export. I wonder if you could put those quotes at the beginning then export as text than change the filename ".csv"...

I tried that, with the same result. Onscreen, the calc field looks correct, but the exported text file contains an extra double quote next to each one exported. I also tried (at the suggestion of someone on another forum) creating a global field, g_QCQ, that contains "," and including that in the calc instead, FieldA & g_QCQ & FieldB & g_QCQ & etc. Same thing. Looks good onscreen, exported result contains doubled up quotes.

I've wrestled with this on copies of the same DB installed on a Mac (OS 9.2.2) and a PC (Win2000), both running FM Dev. 5.5, with identical results. I've tried changing the suffix (on the PC) to both .txt and .csv, with the same result. FM does not have a straight text export option, only .csv. I suppose I could try tab separated instead of comma, but I need the end result to be comma, so I'd have to run it in and out of some other app to alter the tabs to commas, which I'll do if I have to, but would rather not.

Steve Brown

[BobWeaver]

Is there some reason why you can't simply export your three fields as a csv file?

Oops, I reread your earlier post, 'nuff said.

You may be better off exporting as tab delimited as a single concatenated field. Then FM won't mangle your data by inserting additional quotes or commas. And if you export it as a single field, FM won't put any tabs in either.

[spb]

I had just discovered this solution by accident ten minutes before I read your post. FM is trying to preserve the integrity of my internal quotes by trapping them with an extra quote. Exporting as tab delimited instead allows FM to ignore the quotes. I had vaguely thought that extraneous tab characters would be inserted within the text if I did this, but if I'm only exporting one field per record, there are no multiple fields to delimit, hence no tabs.

Works like a charm!

Steve Brown