At 06:27 PM 10/6/2008, Matthew Burke wrote:
So I recently discovered the FLOAT function trying
to figure out how to get rid of dollar symbols and
commas in my CSV exports. I am curious if there is
a simpler, more efficient way of doing this.
Presently I have to create a view, and then use the
expression (FLOAT(table.column)) for every single
field that is a CURRENCY data type. Without doing
that, the exported CSV text file displays the dollar
symbols in addition to placing the value within
quotes
as though it were a string. If there were
some way I could just say
export this and convert
all currency data types into real or double data
types
that would be pretty sweet.
Matthew,
There are at least two approaches to achieve your goal
without using an intermediate step to create a view or
mucking with a live database CURRENCY settings.
Here's how:
01. An eloquent method of using "Specification" file
that can be customized to format each column and
then use the customized specification file (.rgw)
to automate the entire process to create CSV file
on demand. You only have to do this once!
Once fully customized the way you want, use the
following example to create CSV file that uses
your customized specification file.
-- Example
-- Create CSV file using Specification (.rgw)
GATEWAY EXPORT CSV filename.CSV +
SELECT * FROM tablename ORDER BY colname +
OPTION SPECIFICATION_FILE_NAME filename.rgw
Take a look at the sample application included
with 2008 Super Advanced Training Applications.
Folder: UsingGatewayExportToCreateXLSWorkSheets
Database: ColorXLS
Gateway Export Specification File: Customer.rgw
RUN UsingGatewayExportToCreateXLSWorkSheets.DAT
Updated versions of both 2008 SAT Applications
for R:BASE 7.6 and Turbo V-8 are available at:
http://www.rupdates.com/sat2008
02. A very simple approach using UNLOAD ... AS CSV
-- Example 01 (Using FLOAT Function):
CONNECT RRBYW14 or RRBYW15
OUTPUT InvoiceHeader.CSV
UNLOAD DATA FOR InvoiceHeader USING +
CustID,(FLOAT(NetAmount)) AS CSV
OUTPUT SCREEN
{
Use RBEDIT InvoiceHeader.CSV command to view
the entire file in R:BASE Editor, or use the
LAUNCH InvoiceHeader.CSV command to view the
entire file in MS Excel
}
-- Example 02 (Using FORMAT + FLOAT Functions):
OUTPUT InvoiceHeader.CSV
UNLOAD DATA FOR InvoiceHeader USING +
CustID,(FORMAT(FLOAT(NetAmount),'999999999.90')) +
AS CSV
OUTPUT SCREEN
-- RBEDIT InvoiceHeader.CSV
-- LAUNCH InvoiceHeader.CSV
You may further customize the UNLOAD command as
you wish. For more details ... HELP UNLOAD ...
Hope that helps!
Very Best R:egards,
Razzak.
