Is there any way to get FMP to export data to CSV without the quotes
[" "]? Since the commas are there as delimiters the quotes are
superfluous, and they can get in the way sometimes.
--
FW
FileMaker Pro 8.5 under Win XP Pro, and
FileMaker Server 8.0 under Win 2003 Server
On 2007-10-01 10:16:17 -0700, FastWolf
> Is there any way to get FMP to export data to CSV without the quotes
> [" "]? Since the commas are there as delimiters the quotes are
> superfluous, and they can get in the way sometimes.
The quotes are not superfluous. They are there to delimit the text when
there are or might be internal commas in the data, which would truly
screw up your imports if they were not enclosed in quotes. If you've
got "John Smith, MD" in one field, for instance, every field after that
record will be in the wrong spot.
Either use tab-delimited text files or do data-cleaning after import to
get rid of the commas.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA
On Oct 1, 10:49 am, Lynn Allen
> On 2007-10-01 10:16:17 -0700, FastWolf
>
> > Is there any way to get FMP to export data to CSV without the quotes
> > [" "]? Since the commas are there as delimiters the quotes are
> > superfluous, and they can get in the way sometimes.
>
> The quotes are not superfluous.
I'd say that's really for the developer to decide, not filemaker.
> They are there to delimit the text when
> there are or might be internal commas in the data, which would truly
> screw up your imports if they were not enclosed in quotes. If you've
> got "John Smith, MD" in one field, for instance, every field after that
> record will be in the wrong spot.
Ah, but what if there aren't any internal commas, and the next app
down the line is choking on the quotes filemaker added for us? Or
worse, what if there are quotes in the -data-, but no commas?
Filemaker really shouldn't be deciding this for us. Frankly I'm
annoyed they don't let us choose the delimiter and optional quote
character for imports and exports.
If I want to delimit with tabs and quote with angle brackets why not
let me? Its a trivial feature that practically any csv library already
supports. They'd only need to expose the option in the UI.
> Either use tab-delimited text files or do data-cleaning after import to
> get rid of the commas.
And on that note there are lots of free and/or built-into-the-os
command line tools that can be called from filemaker that can do this.
-cheers,
Dave
In article <1191263529.447882.36940@o80g2000hse.googlegroups.com>,
d-42
> On Oct 1, 10:49 am, Lynn Allen
> > On 2007-10-01 10:16:17 -0700, FastWolf
> >
> > > Is there any way to get FMP to export data to CSV without the quotes
> > > [" "]? Since the commas are there as delimiters the quotes are
> > > superfluous, and they can get in the way sometimes.
> >
> > The quotes are not superfluous.
>
> I'd say that's really for the developer to decide, not filemaker.
Except for the fact that quoting strings within comma-separated records
is, and has for decades, been a well-established convention.
> > They are there to delimit the text when
> > there are or might be internal commas in the data, which would truly
> > screw up your imports if they were not enclosed in quotes. If you've
> > got "John Smith, MD" in one field, for instance, every field after that
> > record will be in the wrong spot.
>
> Ah, but what if there aren't any internal commas, and the next app
> down the line is choking on the quotes filemaker added for us?
It's the reader that can't cope with the quotes (while claiming to be
able to accept CSV) that's broken, not the writer that put them there.
> Or worse, what if there are quotes in the -data-, but no commas?
Then the writer will emit doubled double-quotes to signal it.
These conventions predate the desktop computer. FMP emits valid CSV. If
you need a format that FMP doesn't provide, it'll take post-processing.
G
In article <1191263529.447882.36940@o80g2000hse.googlegroups.com>, d-42
> On Oct 1, 10:49 am, Lynn Allen
> > On 2007-10-01 10:16:17 -0700, FastWolf
> >
> > > Is there any way to get FMP to export data to CSV without the quotes
> > > [" "]? Since the commas are there as delimiters the quotes are
> > > superfluous, and they can get in the way sometimes.
We already answered this question last week. If you don't want quotes,
then use a Tab delimited file or one of the other formats that don't
need them for field separation purposes. Quotes are part of the CSV
file format.
> > The quotes are not superfluous.
>
> I'd say that's really for the developer to decide, not filemaker.
The quote marks are part of the established CSV format, and therefore
ARE necessary.
Letting people change these file formats as they wish is very silly and
would lead to no application knowing what is about to be imported. If
FileMaker was a REAL application development system (and not "just" a
Database development system), then you could include an ability for
custom file formats, but they could not be called "CSV", etc.
In fact, thanks to Microsoft continually trying to change established
formats on people, there's already some mess in things like RTF. :o(
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
On Oct 1, 11:48 am, Gregory Weston
> In article <1191263529.447882.36...@o80g2000hse.googlegroups.com>,
> > > The quotes are not superfluous.
>
> > I'd say that's really for the developer to decide, not filemaker.
>
> Except for the fact that quoting strings within comma-separated records
> is, and has for decades, been a well-established convention.
Except when its not.
> It's the reader that can't cope with the quotes (while claiming to be
> able to accept CSV) that's broken, not the writer that put them there.
No offense, but so what? We have to deal with 'broken' software all
the time.
My Bank exports my statements in CSV with a 5 line header, without
quotes. Sure would be nice if Filemaker could import it without
choking on the header so inelegantly...
The courier I deal with requires their electronic shipping manifests
in CSV without quotes. Sure would be nice if filemaker could generate
a usable file directly...
> These conventions predate the desktop computer.
For a decades old standard, CSV is pretty regularly abused. It would
be nice if filemaker was more robust.
> FMP emits valid CSV. If
> you need a format that FMP doesn't provide, it'll take post-processing.
Why should it need to? If you want to add a menu item 'export bastard
CSV' to keep the purity of 'valid CSV' unbesmirched I could live with
that. But there should still be support.
In article <1191343914.914913.253720@d55g2000hsg.googlegroups.com>,
d-42
> On Oct 1, 11:48 am, Gregory Weston
> > In article <1191263529.447882.36...@o80g2000hse.googlegroups.com>,
>
> > > > The quotes are not superfluous.
> >
> > > I'd say that's really for the developer to decide, not filemaker.
> >
> > Except for the fact that quoting strings within comma-separated records
> > is, and has for decades, been a well-established convention.
>
> Except when its not.
Then it's not CSV, and labeling it as such is an error.
> > It's the reader that can't cope with the quotes (while claiming to be
> > able to accept CSV) that's broken, not the writer that put them there.
>
> No offense, but so what? We have to deal with 'broken' software all
> the time.
True. But what makes more sense? Trying to get the deviant vendor to fix
their broken software, or trying to get the vendor who's doing it right
to accommodate arbitrary broken-ness? (The pragmatic course is door #3.
Write/contract a pre-processor that deals with the specific broken-ness
you're facing.)
> My Bank exports my statements in CSV with a 5 line header, without
> quotes. Sure would be nice if Filemaker could import it without
> choking on the header so inelegantly...
>
> The courier I deal with requires their electronic shipping manifests
> in CSV without quotes. Sure would be nice if filemaker could generate
> a usable file directly...
There are lots of things that would be nice, but they're not all
practical. The key point there is despite your use of the letters "CSV"
what your bank is providing is NOT a CSV file and what your courier is
requiring is a restricted subset of CSV. Give a kid with a functional
knowledge of perl $20 and 5 minutes later they'll give you tools to deal
with both of those specific situations. Looking to FileMaker to support
arbitrary variations on what might loosely be called CSV is going to
cost a lot more and take a lot longer.
> > FMP emits valid CSV. If
> > you need a format that FMP doesn't provide, it'll take post-processing.
>
> Why should it need to?
Because the general problem is more complex than you seem to realize.
> If you want to add a menu item 'export bastard
> CSV' to keep the purity of 'valid CSV' unbesmirched I could live with
> that. But there should still be support.
Yes, but "export (or import) bastard CSV" is such a broad category that
adequately supporting it is a fool's errand.
Post removed (X-No-Archive: yes)
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP
My Custom Functions:
http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills
On 10/02/07 1:48 PM, Gregory Weston wrote:
> In article <1191343914.914913.253720@d55g2000hsg.googlegroups.com>,
> d-42
>
>> > On Oct 1, 11:48 am, Gregory Weston
>>> > > In article <1191263529.447882.36...@o80g2000hse.googlegroups.com>,
>> >
>>>>> > > > > The quotes are not superfluous.
>>> > >
>>>> > > > I'd say that's really for the developer to decide, not filemaker.
>>> > >
>>> > > Except for the fact that quoting strings within comma-separated records
>>> > > is, and has for decades, been a well-established convention.
>> >
>> > Except when its not.
>
> Then it's not CSV, and labeling it as such is an error.
>
>>> > > It's the reader that can't cope with the quotes (while claiming to be
>>> > > able to accept CSV) that's broken, not the writer that put them there.
>> >
>> > No offense, but so what? We have to deal with 'broken' software all
>> > the time.
>
> True. But what makes more sense? Trying to get the deviant vendor to fix
> their broken software, or trying to get the vendor who's doing it right
> to accommodate arbitrary broken-ness? (The pragmatic course is door #3.
> Write/contract a pre-processor that deals with the specific broken-ness
> you're facing.)
>
>
Overlooked is the idea that, like it or not, "CSV" seems to have become
a generic term. What is supposed to mean "Comma-Separated Values" now
appears to include values delimited by pretty much anything.
Case in point: if I want to download the data from my Google AdSense
account, I click the "CSV" link. The file I receive is TAB, not comma,
delimited.
We might as well argue about whether "to Xerox" is a valid construct. We
all know it's a trademarked name, and we can individually decide to say
"photocopy" instead, but the world is on a roll. You're not going to get
people to stop saying "Xerox this for me, please."
So...
As nice as it would be to have everything orderly with "CSV" describing
a list of return-separated records, the individual values within each
record being separated by commas, and only that, it's not going to
happen. I've given up on trying to get people to call the copier a
copier instead of "the Xerox". It's a Canon, BTW.
As undesirable as it is for some of us to be forced to accept (myself
included), having to do make accommodations to get real CSV when we want
it is pretty much the way things are. I'm tempted to start leasing
equipment from Xerox so that when someone wants a "xerox" of something,
I can stop cringing.
Matt
On Oct 2, 10:48 am, Gregory Weston
> In article <1191343914.914913.253...@d55g2000hsg.googlegroups.com>,
> True. But what makes more sense? Trying to get the deviant vendor to fix
> their broken software, or trying to get the vendor who's doing it right
> to accommodate arbitrary broken-ness?
Its not arbitrary, and its not broken. CSV despite your claims is not
a decades old rigid standard, it is a standard defined by convention,
and ALL of the forms of 'brokeness' we are discussing are so common
they are conventions in their own right.
> (The pragmatic course is door #3.
> Write/contract a pre-processor that deals with the specific broken-ness
> you're facing.)
Yes. However filemaker pretty much sucks at automating that processor
once you've shelled out your pittance to have it made. The issue isn't
that pre-processors are hard to make, its that they are a pain in the
butt to use with filemaker. I'd be happy to forego more robust import
export in favor of more robust external program interaction that would
make working with a pre/post processors less of clumsy hack.
> There are lots of things that would be nice, but they're not all
> practical. The key point there is despite your use of the letters "CSV"
> what your bank is providing is NOT a CSV file and what your courier is
> requiring is a restricted subset of CSV. Give a kid with a functional
> knowledge of perl $20 and 5 minutes later they'll give you tools to deal
> with both of those specific situations.
And how exactly do you propose filemaker automate invoking those tools
and capturing the result without ending up with a clumsy brittle
solution, that will need to be built twice to work cross platform. Or
without purchasing a 3rd party plugin?
If I could call a shell command from filemaker and have stin / stdout
redirected to fields or variables we'd have a great automation tool.
If I could import a file into a field so that I could preprocess
within filemaker we'd have a decent option, but again, no simple
solution presents itself.
Perform Applescript, sadly, is only available on the Apple.
> Yes, but "export (or import) bastard CSV" is such a broad category that
> adequately supporting it is a fool's errand.
And yet excel, open office, and countless other software packages have
managed it pretty admirably. After all we're talking about modifying
the constant for delimiter, for text-enclosure, and adding the ability
to treat the first n lines as a header.
In article
wrote:
> On 10/02/07 1:48 PM, Gregory Weston wrote:
>
> > True. But what makes more sense? Trying to get the deviant vendor to fix
> > their broken software, or trying to get the vendor who's doing it right
> > to accommodate arbitrary broken-ness? (The pragmatic course is door #3.
> > Write/contract a pre-processor that deals with the specific broken-ness
> > you're facing.)
> >
> >
>
>
> Overlooked is the idea that, like it or not, "CSV" seems to have become
> a generic term. What is supposed to mean "Comma-Separated Values" now
> appears to include values delimited by pretty much anything.
>
> ...
>
> We might as well argue about whether "to Xerox" is a valid construct. We
> all know it's a trademarked name, and we can individually decide to say
> "photocopy" instead, but the world is on a roll. You're not going to get
> people to stop saying "Xerox this for me, please."
Apples and oranges. There's a wide gap between the phenomenon of using a
trade name as the generic name for a category of functionally identical
products and incorrectly claiming conformance to a data transmission
protocol. The end result of using a photo copier, a facial tissue or an
adhesive bandage is the same no matter what name you put on the product.
The end result of putting the extension 'hqx' on a file that was emitted
by uuencode is failure at the receiving end. When you claim to conform
to a file format, you implies the rules for how that should be processed
and doing something different is an error.
G
In article <1191354110.063198.48990@w3g2000hsg.googlegroups.com>, d-42
> On Oct 2, 10:48 am, Gregory Weston
> > In article <1191343914.914913.253...@d55g2000hsg.googlegroups.com>,
>
> > True. But what makes more sense? Trying to get the deviant vendor to fix
> > their broken software, or trying to get the vendor who's doing it right
> > to accommodate arbitrary broken-ness?
>
> Its not arbitrary, and its not broken. CSV despite your claims is not
> a decades old rigid standard, it is a standard defined by convention,
> and ALL of the forms of 'brokeness' we are discussing are so common
> they are conventions in their own right.
CSV stands for Comma Separated Values.
The very original format of this file type would have been single data
values (a couple of words at most) each separated by a comma. With the
advent of databases storing more and more, the need came to have commas
within a field's data, and to accommodate that the quote marks were
added to each field (some applications will only add them as necessary
and "good" applications will ignore them on import).
A file with a header of a different format is not a true CSV file.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
> CSV stands for Comma Separated Values.
I hope you don't really think I didn't know that. :)
> The very original format of this file type would have been single data
> values (a couple of words at most) each separated by a comma.
Uh-huh
> With the
> advent of databases storing more and more, the need came to have commas
> within a field's data, and to accommodate that the quote marks were
> added to each field (some applications will only add them as necessary
> and "good" applications will ignore them on import).
More or less.
And sometimes we still need to be able to export files of the form:
642, 425, 551, 11, 94
43, 132, 332, 1, 22
....
because lots of systems that don't need to handle commas in the data
have never gotten around to supporting quotes to enclose the data.
> A file with a header of a different format is not a true CSV file.
Do you think someone saddled with importing or exporting one really
cares what or not you, I, or anybody else thinks on the subject of
whether or not they are 'true csv files'?
They still need to import them.
They still need to export them.
Excel can happily import them as CSVs.
OpenOffice can happily import them as CSVs.
A great deal of data interchange stuff that predates XML, and even a
good chunk of modern stuff still uses some variant of CSV; often with
headers. Including major banks, couriers, accounting packages, and all
manner of job control stuff on 'big iron' enterprise systems,
manufacturing, etc.
What is really the point of pissing against the wind? This is how real
world data is formatted, lots of it. Millions upon millions of files
worth. Why not give filemaker the flexibility to skip a number of rows
on import, and stick a little header on the file at export if that's
what the end user needs?
Why would one prefer struggle to cling to the purity of a file format
that is little more than a trivial idea with a few decades worth of
uses and abuses that define what is more-or-less normal.
CSV is not XML1.1 or HTML4. CSV has no formal standard or version. And
a dozen different variations of perfectly valid CSV exist... with
headers, without quotes, with a row of field names as the first
record, some of them don't even generate the commas between null
fields at the end of a record.
Because of all this variation CSV is a crappy format to have to use.
But we still have to use it when talking to other systems that use it,
and we often have to conform to the way those systems use it; using a
protocol designed precisely for that application. The more flexible
filemaker's import/export is, the better for everyone who has to use
it.
If you disagree that's fine. If you prefer to pre/post process files
that's fine too, but if you have to deal with million record files or
you have to import them hundreds of times I think you'll come to wish
that you had a little finer control over the import/export options
too. pre/post processing takes time, and automating it in filemaker is
clumsy.
-cheers,
Dave
In article <1191354110.063198.48990@w3g2000hsg.googlegroups.com>,
d-42
> On Oct 2, 10:48 am, Gregory Weston
> > In article <1191343914.914913.253...@d55g2000hsg.googlegroups.com>,
>
> > True. But what makes more sense? Trying to get the deviant vendor to fix
> > their broken software, or trying to get the vendor who's doing it right
> > to accommodate arbitrary broken-ness?
>
> Its not arbitrary, and its not broken. CSV despite your claims is not
> a decades old rigid standard,
Please provide a URL for the post where I made the claim that CSV was a
rigid standard.
> And how exactly do you propose filemaker automate invoking those tools
> and capturing the result without ending up with a clumsy brittle
> solution, that will need to be built twice to work cross platform. Or
> without purchasing a 3rd party plugin?
Hadn't thought about it. This post is the first time that requirement
was identified.
Here's a silly thought. What about FMP's ability to export to XML and to
apply a transformation on the fly?
> > Yes, but "export (or import) bastard CSV" is such a broad category that
> > adequately supporting it is a fool's errand.
>
> And yet excel, open office, and countless other software packages have
> managed it pretty admirably. After all we're talking about modifying
> the constant for delimiter, for text-enclosure, and adding the ability
> to treat the first n lines as a header.
Sure, if you want to stop there. What happens to the next person who
comes along and wants to cope with a footer? Or escaping of troublesome
characters instead of surrounding fields?
> Please provide a URL for the post where I made the claim that CSV was a
> rigid standard.
When you argued that Filemaker is correct to support exactly one
variation of a file format that is known for having a fair number of
different implementations and variations.
> Here's a silly thought. What about FMP's ability to export to XML and to
> apply a transformation on the fly?
I think that would work actually. Nice suggestion! I doubt the OP will
see this though; although it would do what he needs to do; provided
he's equipped to tackle the relatively esoteric world of FMXMLRESULT
grammars and XSLT.
Really, I can't think of a much more complex solution to removing
superfluous quotes from a regular CSV file than to generate the
dataset as an FMXMLRESULT and pipe it through an XSL transform. Like
going from New York to New Jersey by way of Hong Kong. :)
I also don't know whether 'on-the-fly' means quite what you think. I
suspect FM creates the entire FMXMLRESULT before applying the XSL
transform to it. If so the performance hit, and memory utilisation on
a large export could be substantial.
> What happens to the next person who
> comes along and wants to cope with a footer? Or escaping of troublesome
> characters instead of surrounding fields?
Surely there is room for middle ground here. A few more options for
CSV import/export would not be overkill, and would be quite welcome.
And for those screwball corner cases, yeah, something custom will
still be needed, but why reject the most common variations? Why
support exactly one variation of a file format known to regularly
deviate.
-cheers,
Dave
In article <1191372348.914279.239810@n39g2000hsh.googlegroups.com>,
d-42
> > CSV stands for Comma Separated Values.
>
> I hope you don't really think I didn't know that. :)
>
> > The very original format of this file type would have been single data
> > values (a couple of words at most) each separated by a comma.
>
> Uh-huh
>
> > With the
> > advent of databases storing more and more, the need came to have commas
> > within a field's data, and to accommodate that the quote marks were
> > added to each field (some applications will only add them as necessary
> > and "good" applications will ignore them on import).
>
> More or less.
>
> And sometimes we still need to be able to export files of the form:
>
> 642, 425, 551, 11, 94
> 43, 132, 332, 1, 22
> ...
>
> because lots of systems that don't need to handle commas in the data
> have never gotten around to supporting quotes to enclose the data.
Then don't use CSV at all. Create one Calculation field that joins all
your other fields together with commas, and then export that single
fields as a Tab separated text file (you can always rename it to ".csv"
if needed) ... you'll end up with what you want, and FileMaker isn't
saddled with more complexity it doesn't need. :o)
There's never going to be one product that can handle EVERY weird file
type you come across.
On one database I deal with I have to export the data to a "CSV" file,
then open that in Excel and re-save as a "Windows CSV" file before
uploading to a server. If I don't do that the server refuses to
recognise it.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
Post removed (X-No-Archive: yes)
On Oct 4, 2:59 am, Martin Trautmann
> On Tue, 02 Oct 2007 17:45:48 -0700, d-42 wrote:
> > > CSV stands for Comma Separated Values.
>
> > I hope you don't really think I didn't know that. :)
>
> I didn't - since I know that some take this as Character Separated
> Values
>
> > And sometimes we still need to be able to export files of the form:
>
> > 642, 425, 551, 11, 94
> > 43, 132, 332, 1, 22
> > ...
>
> with or without spaces between comma and data?
Touche.
To be fair though, most legacy importers are more tolerant of
whitespace than they are of quotes.
And finding one that *requires* spaces would be hard I think, so a no
spaces by default would probably work for the vast majority of cases.
The only time you'd probably really want those spaces is if you are
shooting for something humans have to look at.
> > Excel can happily import them as CSVs.
> > OpenOffice can happily import them as CSVs.
>
> But Excel and OOo are far too smart here, taking each x:y as time or x-y
> and x/y as date, whenever possible.
You can specify what type each field is supposed to be. (Although I
agree it can be tedious.)
> > What is really the point of pissing against the wind? This is how real
> > world data is formatted, lots of it. Millions upon millions of files
> > worth. Why not give filemaker the flexibility to skip a number of rows
> > on import, and stick a little header on the file at export if that's
> > what the end user needs?
>
> You can add a header easily by creating an extra header record
I'm thinking of a header of the type:
Created: 10/3/06
Job #: 4356
Requested by: Susan T
-------------------------------
data, data, data, data, data
data, data, data, data, data
....
But even adding a header by 'creating an extra header record' is not
always easy. If the tables has, for security/data integrity reasons, a
no-deletion policy you can't simply jam a record in for the import and
get rid of it when you are done. Additionally, suppose the field is
strictly range limited to integers between 5 and 9, getting the value
you want in the header, such as 'bearing radius (mm)' might be a non-
trivial hassle, especially if the underlying field happens to be
called 'user_defined_parameter_4'.
> > Because of all this variation CSV is a crappy format to have to use.
>
> FMP should accept most of it - as liberal as possible, with a simple
> switch of "literal" or "smart" (which would trim escape formatting such
> as "", '', \" etc.)
Agreed. FM does a pretty good job on import, actually. My only
complaint is the inability to ignore a set number of lines and to set
an alternate delimiter. I recognize a semi-colon separated file isn't
a csv, but dammit, its close enough!! :)
And on the export side of things, the ability to write a custom header
easily would be a big help, along with a couple options on formatting
- delimiter, whether or not to use quotes on a per field basis, etc.
> I feel worst of all is XML within FMP - it does not even ask what to do
> about data types which it does not know (where taking them as text would
> be the most simple choice). It just dies instead as soon as problems
> arise...
I don't do much xml importing into FMP (yet!); but I agree its just
needlessly painful. I've actually done XSL transforms to CSV to make
life easier. And that's sad. :)
-cheers,
dave
Post removed (X-No-Archive: yes)
d-42
> The issue isn't
> that pre-processors are hard to make, its that they are a pain in the
> butt to use with filemake
note that pre and post processing can be done in FMP, with dedicated
tables and internal import-export
- you import the records to export in an only text field with as much
quotes, colons or tab you want, you add before 1,2 or 256 records with
your headers in this text field, and you can export this only fiels as a
tab export, with a csv extension.
conversely, you can import your csv data in a text field as a tab (or
other if it contains tabs...) and parse it with calulated fields or
scripts ; and there, you can see and work on your headers.
it's not such difficult, and can be entirely automated.
--
www.D-L-S.org
On Oct 4, 2:48 pm, pma...@invivo.edu (manet) wrote:
> d-42
> > The issue isn't
> > that pre-processors are hard to make, its that they are a pain in the
> > butt to use with filemake
>
> note that pre and post processing can be done in FMP, with dedicated
> tables and internal import-export
No, it can't.
> - you import the records to export in an only text field with as much
> quotes, colons or tab you want, you add before 1,2 or 256 records with
> your headers in this text field, and you can export this only fiels as a
> tab export, with a csv extension.
Suggesting that one put their entire database into a single text field
before exporting it and calling that a good solution is ... insane.
Lets take 15GB of data, duplicate it all into one field, process it,
and then export it. I can't see how that could blow up in my face.
Unless of course there is a limit on the size of a text field...
> conversely, you can import your csv data in a text field as a tab (or
> other if it contains tabs...) and parse it with calulated fields or
> scripts ; and there, you can see and work on your headers.
You can't handwave it away by saing import it as 'other' if it
contains tabs. Because there isn't any 'other' that works in all
situations, if it has both tabs and commas, etc.
> it's not such difficult, and can be entirely automated.
Its a work-around. Its a solution that works most of the time instead
of all the time. We all use it. But we shouldn't ever come to think
its a great solution.
On top of the other issues, its dog slow (try throwing a few million
records of 'semicolon delimited files' at either of these workarounds
and see what kind of performance hit you take compared to a 'comma
separated file' supported directly by filemaker.)
Not all of us want to dedicate the weekend to handle an import export
that should take under a couple hours, and would if FM allowed us to
set the delimiter.
I know I'm being a bit tenacious here, and I'm not trying to offend
anyone or call these work arounds "bad ideas". They are what we have
to work with. I use them too. But I think we can all agree that they
aren't remotely ideal.
-cheers,
dave
d-42
> Suggesting that one put their entire database into a single text field
> before exporting it and calling that a good solution is ... insane.
surely !
what I suggested is to put all the fields of the table to export in an
only field (with calcls), record by record ! my explanations were a bit
too short ; the way (to adapt) is :
1) select the records to export (x fields) in the original table ; have
a field with a serial number
2) from the service table, creates x records with a sérial number ;
thanks to a link with the original table, a text field with automated
calc options (I don't know the exact english FMP words for those very
useful text fields with automatic formula at init)) is filled with the
pre processing formula ; the formula makes a record by record auto
import of the pre processed datas ; it's smooth and speedy.
3) with negative serial number, create as many header records you want,
writing what you want in the text field; by sorting, they come in head
of the table. If necessary, others records at the end...
4) export the only text field, which contains quotes and all thoses
things.
5) clear the service table.
you can do the same, when you need exporting fixed length records for
all sorts of old apps of the 70's.
the inverse process works when importing special formats : all in one
field, and post processing by thoses automated entryes. This is a very
useful option of FMP, that can be customised if your formulas refers to
formatting fields (ie, globals you can import / export).
I did this with tables of millions records and GB of data (hum... takes
some hours...).
--
www.D-L-S.org
d-42
> You can't handwave it away by saing import it as 'other' if it
> contains tabs. Because there isn't any 'other' that works in all
> situations, if it has both tabs and commas, etc.
If you have fields containing tabs and quotes and commas and typing
errors, you'll be in a pain anyway ;-)
sylk exchange could be a solution, but the FMP implementation seems very
buggy.
Anyway, defining the separators and previewing the result could be a
good thing...
the XL versions I used were limited to 32 or 64000 lines,I abandoned it
and now use OpenOffice, but the case of dates remains a very problem.
--
www.D-L-S.org
On Oct 5, 1:55 pm, pma...@invivo.edu (manet) wrote:
Ah, yes, I see what you've done now. And that is a good solution, and
even allows for an aribtrary header block. Although its still a time
consuming 2 stage process, that for simple tweaks to the output file
format seems painfully redundant.
> the inverse process works when importing special formats : all in one
> field, and post processing by thoses automated entryes. This is a very
> useful option of FMP, that can be customised if your formulas refers to
> formatting fields (ie, globals you can import / export).
Except there is no way to import, say, a semi-colon delimited field
with quotes and tabs in the data.
And adding support for it would be trivial, if FMI wanted to, by
simply letting us set the delimiters.
-cheers,
Dave