I am trying to import a CSV into MySQL that has the following:


Date, Money amount, and description. The () around the money means it's a neg. amount. This is a normal CSV format.

When I attempt to import into MySQL using a DATE, DECIMAL 11,2 and VARCHAR(100), the date and amount do not convert correctly and I get warnings that the DATE was truncated and the amount had an incorrect decimal value.

Here is an example of the data row I am trying to import:

1/5/2007,($11.40),STORE DECATUR

Thanks for your help.

import your csv into a holding table defined with varchars

then run a query to do the necessary conversions --

- reformat the date into yyyy-mm-dd format
- strip the parentheses and dollar sign off the money, and use a CASE expression to set the result negative if the parentheses were present

use these conversions in a query to INSERT INTO your final table by SELECTing from the holding table

Working on it... Thanks!

use SUBSTRING_INDEX function

SUBSTRING_INDEX(charfield,'/',1) gives the month

SUBSTRING_INDEX(charfield,'/',-1) gives the year

the day is obtained by
SUBSTRING_INDEX(SUBSTRING_INDEX(charfield,'/',2),'/',-1)

sweet, eh?

I was researching while waiting for an answer and found this:


SELECT STR_TO_DATE(date, '%m/%d/%Y') from table;

Is that OK to use?

BTW, I can certainly use your example for my amount problem

yup, STR_TO_DATE is even better

When I try to create a stored procedure, I am getting an error.


DELIMITER $$;

DROP PROCEDURE IF EXISTS `citi`.`MOVE`$$

CREATE PROCEDURE `citi`.`MOVE` ()
BEGIN
delete from temp_citi where trans like '"CLICK%';
-- Update Date and Trim Amounts
UPDATE TEMP_CITI
SET TRANS_DATE = (SELECT STR_TO_DATE(trans_date, '%m/%d/%Y'));

UPDATE TEMP_CITI
SET AMOUNT = (SELECT TRIM(LEADING '($' FROM amount));

UPDATE TEMP_CITI
SET AMOUNT = (SELECT TRIM(TRAILING ')' FROM amount));
-- Update TRANS Table with converted data

INSERT INTO TRANS SELECT * FROM TEMP_CITI;
-- Cleans TEMP_CITI table
delete from TEMP_CITI
END$$

DELIMITER ;$$


and the error is:

(0 row(s) affected)
(0 ms taken)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12
(0 ms taken)

I am taking my working query and pasting it into the create stored procedure window.

run the queries themselves, one at a time, to make sure they work correctly, before attempting to put them into a proc

I did that. They work one at a time and they also work with all of them running (all queries highlighted and hit F5)

um, i just noticed -- you are updating the same table

that won't work

load your csv data into a table which has VARCHAR columns

then use INSERT/SELECT (not UPDATE) to reformat the data into a second table (the one you want to keep) which has the correct datatypes

I am doing conversions in the temp table and then moving data to the real table, and then deleting all temp data from the temp table.

All of this works without use of a stored procedure. When I use a SP, it doesnt work. Even if I shift everything down, it always says there is a problem on line 12.

i'm afraid i have not explained it very well

there is no UPDATE

you do the conversion during the copy from the holding table to the real table

there is no UPDATE

just one INSERT...SELECT...

Can you show me the step where I need to change the amount? I have to use 2 select TRIM statements. Can it be done in one step?

here is how you convert the string amount column in one table into a numeric value that you can insert into a DECIMAL(11,2) column --

Ok, I am still doing something wrong with just a basic insert WITHIN a SP.

When I run the below from a query window, it works.

INSERT INTO TRANS
(TRANS_DATE, AMOUNT, TRANS)
SELECT TRANS_DATE, AMOUNT, TRANS FROM TEMP_CITI

When I do the create query window and paste the working code from above, I get an error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO TRANS
(TRANS_DATE, AMOUNT, TRANS)
SELECT TRANS_DATE, AMOUNT, TRAN' at line 3


So, taking everything else out of the equation, what am I doing wrong with just this basic insert?

P.S. I created 2 tables with the varchar characters and tried to move data via a SP and still didnt work, so its nothing with the conversion.


DELIMITER $$;

DROP PROCEDURE IF EXISTS `citi`.`COPY`$$

CREATE PROCEDURE `citi`.`COPY` ()
BEGIN
INSERT INTO TRANS
(TRANS_DATE, AMOUNT, TRANS)
SELECT TRANS_DATE, AMOUNT, TRANS FROM TEMP_CITI
END$$

DELIMITER ;$$