Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2007
    Posts
    19

    Red face Unanswered: CSV Import Into MySQL

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    19
    Working on it... Thanks!
    Last edited by data1025; 01-19-07 at 11:15.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Posts
    19
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, STR_TO_DATE is even better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Posts
    19
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    run the queries themselves, one at a time, to make sure they work correctly, before attempting to put them into a proc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Posts
    19
    I did that. They work one at a time and they also work with all of them running (all queries highlighted and hit F5)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Posts
    19
    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.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2007
    Posts
    19
    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?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    insert into finaltable
    ( amount, othercolumns ... )
    select 
      case when amount_string like '%(%)%' then -1 else 1 end 
    * case when coalesce(
    replace(replace(replace(replace(amount_string,'$',''),'(',''),')',''),' ','')
     ,'') = '' then 0 else
    replace(replace(replace(replace(amount_string,'$',''),'(',''),')',''),' ','')
    end as amount_numeric
    , otherexpressions ...
    from holdingtable
    Last edited by r937; 01-20-07 at 14:50.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Posts
    19
    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 ;$$

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •