You are here:  » CSV Field Formatted as Currency Not Importing

CSV Field Formatted as Currency Not Importing

Submitted by dan76 on Tue, 2014-07-01 13:12 in

I'm trying to import a csv file that has a currency field into a mysql database, but for some reason the import only brings in zero's. I'd like to have this data imported as a decimal with two decimal places.

How should I format my insertion query to accomplish this? Currently, it looks something like this:

$sql = "INSERT INTO database SET
`SalePrice` = '".database_safe($record["SalePrice"])."';

The format string for the file is "csv|44|1|34".

Any help is appreciated.

Submitted by support on Tue, 2014-07-01 13:18

Hi Dan,

If not already, I would suggest making sure that `SalePrice` is DECIMAL(10,2). The following script would do the trick (using the library included with the Get It Coded MySQL examples)

$sql "ALTER TABLE `table` CHANGE `SalePrice` `SalePrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00'";

(don't forget to change `table` as required for your database)

Hope this helps!

Submitted by dan76 on Wed, 2014-07-02 14:15

Hi David,

My database is already setup as DECIMAL(10,2) so I'm not quite sure why it isn't working for me. I just sent a Get It Coded request so you can see the csv file.

Thanks again for your help.

Submitted by support on Wed, 2014-07-02 15:44

Hi Dan,

Thanks for the copy of your feed, I immediately noticed that the price field has the currency symbol "$" included, which makes it invalid for inserting into a MySQL field of type DECIMAL(10,2), so this needs to be stripped - that should be all it is. Rather than modify your SQL code, simply add the following line just before that point:

  $record["SalePrice"] = str_replace("$","",$record["SalePrice"]);

That should be all it is!

Submitted by dan76 on Wed, 2014-07-02 16:35

Great, thank you! I figured it was something simple, and that added code did the trick. Thanks again.