How to produce export file in specified format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

From a large database, I need to produce an extract file of selected records
in text CSV format with defined field specifications. After querying to get
the records and fields I need from the database, I must manipulate and
compare these fields to produce the required output data fields and then
export them to a file. What is the best way to accomplish this task. A
select query will get me the records and fields from the database, but what
do I use to do the rest of the job?
 
It depends on just what you mean by "manipulate and compare these fields
to produce the required output data fields". Normally one would do the
manipulation in the query, using calculated fields and maybe data
aggregate functions (e.g. DMax, DCount) and subqueries.

Once you've got a query that returns the data you want, just export it.
If the default field formats don't suit, you can use an export
specification and/or calculated fields (e.g. the Format function) to
tweak them.
 
The data manipulation I am referring to varies, but one example is checking
the values of a few fields from the database and setting a field in the
output to established values based upon what these values are. This would be
done in the selection query, creating new fields for the output?

In your second paragraph, what do you mean by an export specification?
 
The data manipulation I am referring to varies, but one example is checking
the values of a few fields from the database and setting a field in the
output to established values based upon what these values are. This would be
done in the selection query, creating new fields for the output?
Click to expand...

That's the general idea. Suppose you wanted an output field FieldX whose
value was
0 if FieldA > 100 and FieldB = False,
-1 if Field A <= 100 and FieldB = False,
+1 if Field B = True
, it can be done with a query like this:

SELECT
FieldA,
FieldB,
SWITCH(
(NOT FieldB) AND FieldA > 100, 0,
(NOT FieldB) AND FieldA <= 100, -1,
FieldB, 1
) AS FieldX
FROM TestTable
;

In your second paragraph, what do you mean by an export specification?
Click to expand...

I mean the things called "export specifications". Look in Help: one
place to start is the article titled "About exporting data and database
objects".
 
Back
Top