Column formatting of SQL result export to CSV

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 04 Aug 2006 10:16 GMT

Dear all,

I would try to export SQL result from Access DB by following SQL
statement:

Select row1 into [Text;DATABASE=c:/tmp].[result.csv] from table1

row1 is in datatype currency.

I have tried this statement and the result shows "HK$ XX.00" for the
column row1. However I don't want to show "HK$" and the decimal place,
i.e. just "XX". How can I get my expected result?

thx in Advance

Peter Leung
Resaonable Software House Ltd.

Reply to this Message

Pritcham - 04 Aug 2006 15:20 GMT

Hi Peter

It looks like it's formatting the output based on the column type so in
that instance, if you don't want it to do that, just change the type
via your query - e.g.

Select CInt(row1) into [Text;DATABASE=c:/tmp].[result.csv] from table1

Hope that helps/works!

Cheers
Martin

> Dear all,
>
[quoted text clipped - 13 lines]
> Peter Leung
> Resaonable Software House Ltd.

Reply to this Message

Peter - 05 Aug 2006 04:05 GMT

Dear Martin,

thx for your reply first.

but I am afraid that this will raise overthrow error if the number is
too large. will it be another option?

regards,
Peter Leung

Reply to this Message

Pritcham - 07 Aug 2006 08:37 GMT

Hi Peter

Is it not possible for you to just change the formatting within Excel
once you've exported it?

I believe the only reason you're seeing it the way that you are is that
Excel is formatting it (correctly) as a currency field so just change
that from currency to either "General" or "Number" (or whatever format
you need).

Other than that, I don't think there's an integer equivelent to a
currency field (an int would obviously just get a standard "Normal"
format in Excel).

Cheers
Martin

> Dear Martin,
>
[quoted text clipped - 5 lines]
> regards,
> Peter Leung

Reply to this Message

Column formatting of SQL result export to CSV

Free Magazines