SQL Query Output to CSV file format

ridonkulous

Hi Guys,

I have yet another programming problem. I have a SQL Query whereby I need the output of the query to be in a csv file format. The query looks like this;

SELECT D.TMP, I.TMP, A.TMP TMP_2G, B.TMP TMP_3G

FROM ..................

I have tried changing the query to look like this;

SELECT D.TMP||','|| I.TMP||','|| A.TMP TMP_2G||','|| B.TMP TMP_3G

FROM ..................


but I get a "FROM keyword not where expected error(or something to that effect)". But when I remove the last ||','|| and replace it with a , the query will run but the output is not what I require.

Does anyone have any light they can shed to solve this problem. Thanks in advance.

CuLT

Are you directly accessing the database for this or using DB functions/libraries from an application development language like C#, PHP, ASP etc?

If it's the latter you won't be able to make the query return CSV formatting directly, it should be returning results as an array, and you can then format that array into CSV and output.

Should be an easy enough task in most languages, Google for "CSV output" + your chosen language, you'll find a few tutes.

Edit: I haven't really accounted for this being a windows development problem, in which case any help I might impart is probably useless.

Tom Dunne

ridonkulous wrote: »

SELECT D.TMP||','|| I.TMP||','|| A.TMP TMP_2G||','|| B.TMP TMP_3G

FROM ..................

I would say that is where your problem is. Lose the alias (i.e. TMP_3G bit).

ridonkulous wrote: »

but I get a "FROM keyword not where expected error(or something to that effect)".

We will need a couple of pieces of information.

Firstly, what database system are you using? (i.e. Oralce/MS Sql/MySQL/etc.)

Secondly, what is the error, the full error, and nothing but the error? The key to solving these problems is usually in the error.

Exactly what you are typing and the exact error message you are getting.

Ginger

MSSQL's Query Analyser can output results to CSV easily enough, just change the output type to report (RPT) which is can then be imported to Excel as a fixed width file

MOH

Tom Dunne wrote: »

I would say that is where your problem is. Lose the alias (i.e. TMP_3G bit).



We will need a couple of pieces of information.

Firstly, what database system are you using? (i.e. Oralce/MS Sql/MySQL/etc.)

Secondly, what is the error, the full error, and nothing but the error? The key to solving these problems is usually in the error.

Exactly what you are typing and the exact error message you are getting.

You'll actually need to drop all the aliases:

SELECT D.TMP||','|| I.TMP||','|| A.TMP [S]TMP_2G[/S]||','|| B.TMP [S]TMP_3G[/S]

FROM ..................

amen

just change the output type to report (RPT)

or if you can't do that how about something along the lines of
SELECT
FieldName1 + ',' + fieldName2 +',' etc
from
Table1

you may need to convert non string fields to characters

Ginger

amen wrote: »

or if you can't do that how about something along the lines of
SELECT
FieldName1 + ',' + fieldName2 +',' etc
from
Table1

you may need to convert non string fields to characters

Good for a limited number of cols, pain in the ass if you have a load

ridonkulous

Thanks alot for the swift and numerous replies guys. I just dropped the aliases and it now works with ||','|| placed in the sql query. I don't know why I hadn't thought of that myself but who cares it works. Thanks again.

ridonkulous

Bump.

Sorry guys I have the same problem again but removing the aliases wont work. I have the following query;

SELECT DATE, COLUMN_1 ALIAS_1, COLUMN_2 ALIAS_2
FROM
(
....................
UNION
....................
)
WHERE ( (ALIAS_1 + ALIAS_2 > 0 )
GROUP BY DATE

The old solution was to just remove the aliases and use ||','|| which worked perfectly but because of the WHERE statement at the end of the query the aliases are required. And because the aliases are required I am now getting the same error as I was previously. Any suggestions? Thanks in advance.

Tom Dunne

ridonkulous wrote: »

Bump.

Sorry guys I have the same problem again but removing the aliases wont work. I have the following query;

SELECT DATE, COLUMN_1 ALIAS_1, COLUMN_2 ALIAS_2
FROM
(
....................
UNION
....................
)
WHERE ( (ALIAS_1 + ALIAS_2 > 0 )
GROUP BY DATE

The old solution was to just remove the aliases and use ||','|| which worked perfectly but because of the WHERE statement at the end of the query the aliases are required. And because the aliases are required I am now getting the same error as I was previously. Any suggestions? Thanks in advance.

You are missing a bracket in the WHERE clause.

It should probably read

( (ALIAS_1 + ALIAS_2) > 0)

bala

MYSQL or Oracle

ridonkulous wrote: »

Bump.

Sorry guys I have the same problem again but removing the aliases wont work. I have the following query;

SELECT DATE, COLUMN_1 ALIAS_1, COLUMN_2 ALIAS_2
FROM
(
....................
UNION
....................
)
WHERE ( (ALIAS_1 + ALIAS_2 > 0 )
GROUP BY DATE

The old solution was to just remove the aliases and use ||','|| which worked perfectly but because of the WHERE statement at the end of the query the aliases are required. And because the aliases are required I am now getting the same error as I was previously. Any suggestions? Thanks in advance.

bala

bala wrote: »

MYSQL or Oracle

MYSQL solution try

SELECT DATE, COLUMN_1 ALIAS_1, COLUMN_2 ALIAS_2
FROM
(
....................
UNION
....................
)
WHERE ( (ALIAS_1 + ALIAS_2 > 0 )
GROUP BY DATE
INTO OUTFILE 'mycsv.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'


ORACLE Solution

Set colsep ','

spool 'mycsv.csv

<your sql command>