1

I can import my csv-file via phpmyadmin (but it takes very long). I can't load it via SSH and MySQL as a command, because I don't have the permission.

So is there some possibility to load the csv data like this:

    INSERT INTO table LOAD_CSV(MY_CSV_STRING)

If not i have to parse it into individual rows.

Improve this question
2
  • How big is the CSV file? I'm not aware of how phpmyadmin operates behind the scenes but logically it'd upload the file first and then import it in the method you are hoping for. The time taken may just be the upload process. It's hard to be certain without some specific information regarding your upload bandwidth and the CSV size (both physical and number of rows) Commented Feb 27, 2012 at 9:50
  • its 300 mbs big. i only can upload 200. so i already split it, but uploading it as a whole takes just too long. Like 20 minutes and I get an error telling me "the blocks are too long" because instead of inserting every row phpmyadmin wants to put it all into a single insert-statement. and i dont see an option to tell phpmyadmin to execute them seperatly. (only possible with big sql-files) Commented Feb 27, 2012 at 9:57

5 Answers 5

Reset to default
3

There is actually no way to do what I wanted. It is only possible to load it via file.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for making it clear, you save me a lot of time. Do you handle this using insert into as well?
1

You can use LOAD DATA INFILE in this way : 

LOAD DATA LOCAL INFILE '/importfile.csv' 
INTO TABLE test_table 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(field1, filed2, field3);
Improve this answer

1 Comment

well not possible. tried it via ssh but i cant an error with something like "no permission"... the odd thing is i can do it via phpmyadmin.
0

It is possible to use a stored procedure for this combined with SUBSTRING_INDEX. Keep in mind that if your CSV is too large you may want to use LOCATE and SUBSTRING instead. Here is a simple one for reference:

CREATE PROCEDURE load_csv (IN `icsv` MEDIUMTEXT)
    MOD_SP
    BEGIN
        DECLARE csv MEDIUMBLOB;
        DECLARE csv_begin INTEGER DEFAULT 1;
        DECLARE csv_end INTEGER;
        DECLARE csv_size INTEGER;
        SET csv = COALESCE(`icsv`,'');
        SET csv_size = LENGTH(csv);
        WHILE csv_begin < csv_size DO
            SET csv_end = LOCATE('\n',csv,csv_begin);
            SET csv_end = IF(csv_end != 0,csv_end,csv_size+1);
            SET pbegin = pend+1;
            INSERT INTO mytable (field1, field2, field3, field4, field5) VALUES (
                SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(policy FROM pbegin FOR pend-pbegin),',',1),-1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(policy FROM pbegin FOR pend-pbegin),',',2),-1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(policy FROM pbegin FOR pend-pbegin),',',3),-1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(policy FROM pbegin FOR pend-pbegin),',',4),-1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(policy FROM pbegin FOR pend-pbegin),',',5),-1)
            )
        END WHILE;
    END //

You can make this a bit faster though with some optimizations, in particular caching the csv line and using some properties of SUBSTRING_INDEX to get the smallest string.

CREATE PROCEDURE load_csv (IN `icsv` MEDIUMTEXT)
    MOD_SP
    BEGIN
        DECLARE csv MEDIUMBLOB;
        DECLARE csv_line MEDIUMBLOB;
        DECLARE csv_begin INTEGER DEFAULT 1;
        DECLARE csv_end INTEGER;
        DECLARE csv_size INTEGER;
        SET csv = COALESCE(`icsv`,'');
        SET csv_size = LENGTH(csv);
        WHILE csv_begin < csv_size DO
            SET csv_end = LOCATE('\n',csv,csv_begin);
            SET csv_end = IF(csv_end != 0,csv_end,csv_size+1);
            SET csv_line = SUBSTRING(policy FROM pbegin FOR pend-pbegin);
            SET pbegin = pend+1;
            INSERT INTO mytable (field1, field2, field3, field4, field5) VALUES (
                SUBSTRING_INDEX(csv_line,',',1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(csv_line,',',2),-1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(csv_line,',',3),-1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(csv_line,',',-2),1),
                SUBSTRING_INDEX(csv_line,',',-1)
            )
        END WHILE;
    END //

Finally you can even use a mix of locate and substrings for the commas themselves if your lines are particularly long but I don't have time to write this example.

Improve this answer

1 Comment

Well after two years it became possible.
0

Use LOAD DATA LOCAL INFILE command to import as aleroot suggests.

If there is a permission error you need to allow it by connecting to mysql instance with the following command : mysql --local-infile -uroot -p

Improve this answer

Comments

0

Solution using dynamic sql

CREATE PROCEDURE load_csv(IN CSV_INPUT LONGTEXT)
/*

 CALL load_csv(@csv:='a1,b1,c1\na2,b2,c2\na3,b3,c3')
 */
BEGIN
    SET CSV_INPUT := @csv;

    SET @sql = concat('INSERT INTO mytable (field1, field2, field3)
            VALUES ("', replace(replace(CSV_INPUT, ',', '","'), '\n', '"),("'), '");');
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.