CSV and Field Names

[Ralf Comtesse]

Hi,

there is a little problem I have with generating CSV Files. I am pretty
good at generating the file as such but the other application expects one
of the field names to contain a dot. This is an invalid name for a DB
column. Is there any way to generate a cvs file and specify its field names
other than to take the column names of the database.

Thanks a lot

Ralf

 

[John Nurick]

Hi Ralf,

There are several ways of dealing with this. One is to create a query
that returns a single row containing the field names you want and union
this with your actual query. It's usually necessary to add a calculated
field to each query so that the field names appear first in the output.
The whole thing can be done in one SQL statement along these lines:

SELECT fID, fSubject FROM (
SELECT TOP 1
"X.ID" AS fID, "X.Subject" AS fSubject,
0 AS Dummy
FROM tblFAQ
UNION
SELECT
fID, fSubject,
1 AS Dummy FROM tblFAQ
) ORDER BY Dummy, fSubject;

Explanation: the first SELECT selects the fields you actually want to
output. The second SELECT generates the single row (TOP 1) containing
the field names (e.g. "X.ID") and the calculated field Dummy. The third
SELECT retrieves the actual data.

The calculated field "Dummy" has the value 0 in the header row and 1 in
the data rows, so can be used to sort the output to ensure that the
header row comes first.

 

[Ralf Comtesse]

Hi John

Hi Ralf,


SELECT fID, fSubject FROM (
SELECT TOP 1
"X.ID" AS fID, "X.Subject" AS fSubject,
0 AS Dummy
FROM tblFAQ
UNION
SELECT
fID, fSubject,
1 AS Dummy FROM tblFAQ
) ORDER BY Dummy, fSubject;

Thanks! That put me on the right track. I am sorry if that is a FAQ. I
couldn't find them.

Regards,

Ralf