I download data into a comma delimited text file with fields enclosed in quotes, from a remote system. The data includes a listprice field that has a value in the format of 12,345. I need to run compares off of this field in my queries. I have tried two different ways:
1.) I import the text file directly into my MySQL table. The problem is that no matter what column type I choose (int, double, etc.) it strips of the comma and remaining numbers (12,345 becomes 12).
2.) I import the text file into an MS Access database, change the column types as I import (I think I chose currency). The field now looks like $12,345. I export the data from MS Access to MySQL database via ODBC and the data imports fine and the column value is changed to double and the field looks like 12345. Which is exactly the way I want it. The only problem is that everytime I export the data from MS Access it has to be a new table in MySQL. I just don't want a new table for every export which in my case would have to be everynight. I want to automate or at least make as easy as possible the updating of the MySQL table.
Has anyone else experienced this or could someone suggest a direction for me from here? I would greatly appreciate it. Thanks in advance.
