Number format German style => English style when importing CSV files into MySQL-DB

[Andreas Emmert]

Hi there,

I'm trying to import a csv file into my MySQL database. Unfortunately the
number format for the price column is formatted in German style, i.e. XX,XX
(decimals separated by comma not by dot). When importing this file using
LOAD DATA LOCAL INFILE the decimals are cut off. I'm using column type FLOAT
for the price column in my database.

Is there any chance to preserve the decimals when importing the csv file
into my database even though the format differs from the one MySQL needs
(dot versus comma)? Is it possible to adjust the query to include this or
how can I achieve this?

My current query looks like this:

$query = mysql_query("LO AD DATA LOCAL INFILE '$file' INTO TABLE $data_table
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;");

Thanks for any help!

---
Andreas

[Fred]

Re: Number format German style => English style when importing CSV files into MySQL-DB

Andreas Emmert wrote:
[color=blue]
> Hi there,
>
> I'm trying to import a csv file into my MySQL database. Unfortunately the
> number format for the price column is formatted in German style, i.e.
> XX,XX (decimals separated by comma not by dot). When importing this file
> using LOAD DATA LOCAL INFILE the decimals are cut off. I'm using column
> type FLOAT for the price column in my database.
>
> Is there any chance to preserve the decimals when importing the csv file
> into my database even though the format differs from the one MySQL needs
> (dot versus comma)? Is it possible to adjust the query to include this or
> how can I achieve this?[/color]

Can't you simply write a 3 or 4-line perl script to convert the file into
the appropriate format?

-Fred

--
Shameless plug:

http://JobMarketIntelligence.com

A database of high-tech firms at your fingertips.

[Andreas Emmert]

Re: Number format German style => English style when importing CSV files into MySQL-DB

Good idea, I'll try to do this and run the file through such a filter first
before inserting everything into the database ... simple solutions are best
:)

--
Andreas

"Fred" <fred@jobmarket intelligence.co m> schrieb im Newsbeitrag
news:ktu3b.2890 81$uu5.63240@sc crnsc04...[color=blue]
> Andreas Emmert wrote:
>[color=green]
> > Hi there,
> >
> > I'm trying to import a csv file into my MySQL database. Unfortunately[/color][/color]
the[color=blue][color=green]
> > number format for the price column is formatted in German style, i.e.
> > XX,XX (decimals separated by comma not by dot). When importing this file
> > using LOAD DATA LOCAL INFILE the decimals are cut off. I'm using column
> > type FLOAT for the price column in my database.
> >
> > Is there any chance to preserve the decimals when importing the csv file
> > into my database even though the format differs from the one MySQL needs
> > (dot versus comma)? Is it possible to adjust the query to include this[/color][/color]
or[color=blue][color=green]
> > how can I achieve this?[/color]
>
> Can't you simply write a 3 or 4-line perl script to convert the file into
> the appropriate format?
>
> -Fred
>
> --
> Shameless plug:
> http://JobMarketIntelligence.com
> A database of high-tech firms at your fingertips.[/color]