Importing .csv file into MySQL Dbase

datona

I am currently attempting to import an .csv file (comma seperated) into MySQL by execiting it with the following code:

$sql2 = "LOAD DATA LOCAL INFILE '$dbase_file.CSV' INTO TABLE `$dbase_file` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY 
'\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES";
$result2 = mysql_query($sql2) or die(mysql_error(). "<br>\n{$sql2}");

Everything goes good except for the formatting of one column.
I have a column with numerical values of:
2.00
94.49
1,000.00

The biggest thing I have is the comma in 1,000.00
The only way I can input it into the dbase is to have the table column as "varchar(10)" instead of what I would like it to be "float(9,2)"
That is fine but I would like to be able to sort using "ORDER BY thatcolumn" and if it is set as "varchar(10)", it will not properly sort.

I know this may be more of a MySQL question BUT this board is awsome and there are quite a few real smart cookies here so I thought I'd ask 😉

Any help would be greatly appreciated!!!

kakki

cant you just load that data

datona wrote:
The only way I can input it into the dbase is to have the table column as "varchar(10)" instead of what I would like it to be "float(9,2)"

cant you just load the data into a help-table and then create the real table with
create tabel realTable..... and then fill realTable with
"insert into realTable select ...,varcharCol+0,..." (adding 0 to varchar - column )

datona wrote:
The biggest thing I have is the comma in 1,000.00

if you create the csv yourself, maybe you can just str_replace the comma,
if not, you can check whether there is a "," in the varchar-column(with like '%,%' )
and change that by hand ....

datona

kakki wrote:
if you create the csv yourself, maybe you can just str_replace the comma,
if not, you can check whether there is a "," in the varchar-column(with like '%,%' )
and change that by hand ....

The imported file is actually created by MS Access, exported to an XLS file, then converted to a CSV file. From there, it is then imported. Due to the complex formula's in MS Access, it would be almost imposible to change the 1,000.00 format to 1000.00 in MS Access.

kakki wrote:
cant you just load the data into a help-table and then create the real table with
create tabel realTable..... and then fill realTable with
"insert into realTable select ...,varcharCol+0,..." (adding 0 to varchar - column )

When you say "help-table", are you referring to loading into a temporary/duplicate table, then moving all info over to the live table while ising the str_replace command on those columns?

kakki

datona wrote:
The imported file is actually created by MS Access, exported to an XLS file, then converted to a CSV file. From there, it is then imported. Due to the complex formula's in MS Access, it would be almost imposible to change the 1,000.00 format to 1000.00 in MS Access.

maybe a simple php-script can handle that(explode each line and look if the size of the result-array changes ...)

datona wrote:
When you say "help-table", are you referring to loading into a temporary/duplicate table...

i used "temp-tables" once and they only gave me problems(earlier mysql-versions),
now i create a new database(which i can drop easily ) and in there i create tables
for "single use only"

datona wrote:
then moving all info over to the live table while ising the str_replace command on those columns?

i dont know, how that wrong "1,000.00" will end up in your db
my guesses:
a) line wont be inserted(due to too many columns )
=> 1,000.00 is split into 2 fields, "1" and "000.00"
b) (better option) the value 1,000.00 will be inserted in the correct db-column(but no number available,due to "," )
a) is really bad
b) gives you the chance to find these values with "like '%,%'" and change them by hand(or a small php-script).....
with str_replace i referred to the php-function(in case you create that csv yourself using php )

maybe you can export that data from access to a xml-file, so you wouldnt have these "bad numbers" problem???
( in that case you would have to write a php-script to insert that data )