csvread with commas and semicolons

[ggrothendieck]

In some countries its common to have data in which the decimal point
is represented by a comma and the field separator is a semicolon. I
know how
to specify a semicolon in csvread. Is there a way to specify that the
decimal
point is represented by a comma?

[bob mcgee]

The easiest way is to use read the column as a string type (VARCHAR)
and use "CONVERT(REPLACE(REPLACE(string,'.',''),',','.'),DOUBLE)" or
CONVERT(<string_replace_stuff>,DECIMAL) to convert to version with
traditional formatting with decimal formatter.

You could also go poking about in java's internationalization for
numeric formatting or write a custom number and date formatter.
Probably safer to explicitly do the above, though.

Cheers,
Bob McGee

[Thomas Mueller]

Hi,

CSVREAD returns VARCHAR for all columns, so converting comma to a dot
within CSVREAD would be the wrong place. If you use CSVREAD as a table
to insert into or create another table, the data is converted using
the default conversion, for example CAST(x AS DECIMAL). This
conversion is not local specific, and I don't plan to add such a
feature as other databases also don't support it (as far as I know). I
also don't plan to add a feature request, sorry.

You could use REPLACE:
http://www.h2database.com/html/functions.html#replace - or you could
create a special java function to do that (see CREATE ALIAS).

I would try to avoid using a comma however.

Regards,
Thomas

[ggrothendieck]

On Jul 13, 3:04 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > In some countries its common to have data in which the decimal point
> > is represented by a comma and the field separator is a semicolon.  I
> > know how
> > to specify a semicolon in csvread.  Is there a way to specify that the
> > decimal
> > point is represented by a comma?
>
> CSVREAD returns VARCHAR for all columns, so converting comma to a dot
> within CSVREAD would be the wrong place. If you use CSVREAD as a table
> to insert into or create another table, the data is converted using
> the default conversion, for example CAST(x AS DECIMAL). This
> conversion is not local specific, and I don't plan to add such a
> feature as other databases also don't support it (as far as I know).

Regarding other databases, SQL Server's BCP import utility has a -R
switch which if used respects regional settings and that may support
comma decimal in relevant locales.