Column formatting of SQL result export to CSV

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

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.
 
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 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
 
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
 
Back
Top