PeterDownie.com

Back to the Library

My Mysql Functions

Mysql CSV Text To Table Stored Procedure

Okay this is a stored procedure function that takes a csv input and turns it into a table, it can connect data if the column name is different but the key is the same. I have big plans for this function so it will be heavily rationalized and simplified and then will be implemented in many projects. There will soon likely be a much better way of doing this as this is a hack in my opinion.

Sample Tables

List Entry

You can use whatever table you want here

CREATE TABLE listEntry(
    ListEntryID BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    fk_listID BIGINT UNSIGNED NOT NULL,
    item BIGINT UNSIGNED, 
    connectionID VARCHAR(50)
    );

Unique Key Table

This table is for creating a unique id for trhe csvStorageTable

CREATE TABLE csvStorageTableUniqueKeyCreator(
    customKey BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
);

You can put the data into this if the data is temporary

CREATE TABLE csvStorageTable(
    csvStorageKey BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ConnectionKey BIGINT UNSIGNED,
    InsertKey BIGINT UNSIGNED,
    payload BIGINT UNSIGNED,
    connectionID VARCHAR(50),
    modifications VARCHAR(255)
);

The Stored Procedure

DELIMITER //
DROP PROCEDURE IF EXISTS seperateCSV//
CREATE PROCEDURE seperateCSV(IN tempConnectionName VARCHAR(255),IN tempConnectionValue BIGINT UNSIGNED,
    IN csvText TEXT,IN tableName VARCHAR(255), IN keyName VARCHAR(255),IN columnName VARCHAR(255))
function: BEGIN
    DECLARE var_itemLength BIGINT UNSIGNED;
    DECLARE p BIGINT UNSIGNED DEFAULT 0;
    DECLARE curLetter CHAR(1);
    DECLARE curInput TINYTEXT DEFAULT '';
    DECLARE var_insertCount BIGINT UNSIGNED DEFAULT 0;
    SELECT CHAR_LENGTH(csvText) INTO var_itemLength;

    /* Creates a INSERT statement */ 
    SET @table = tableName;
    SET @listKey = keyName;
    SET @columName = columnName;
    SET @tempConnection = tempConnectionName;
    SET @tempConnectionValue = tempConnectionValue;
    SET @prep = CONCAT('INSERT INTO ',@table,'(',@tempConnection,',',@listKey,',',@columName,')',' VALUES(?,?,?)');
    PREPARE s1 FROM @prep;
    
    SET @updater = CONCAT('UPDATE ',@table, ' SET ',@columName,'=? WHERE ',@tempConnection,'=? AND ',@listKey,'=?');
    PREPARE s5 FROM @updater;
    
    /* Implemented  */
    SET @alreadyExist = CONCAT('SELECT COUNT(',@listKey,') INTO @var_itemCount  FROM ',@table,'
        WHERE ',@tempConnection,'=? AND ',@listKey,'=? AND ',@columName,'=?');
    PREPARE s3 FROM @alreadyExist;
    
    /* Update when tempConnectionValue and listKey are the same */
    SET @update_not_insert = CONCAT('SELECT COUNT(',@listKey,') INTO @var_itemCount_inner FROM ',@table,'
         WHERE ',@tempConnection,'=? AND ',@listKey,'=?');
    PREPARE s4 FROM @update_not_insert;

    csv_loop: LOOP
        SET p = p + 1;
        IF p <= var_itemLength THEN
            SELECT SUBSTRING(csvText,p,'1') INTO curLetter;
            IF curLetter = ',' THEN
                IF curInput != '' THEN
                    SET var_insertCount = var_insertCount + 1;
                    SET @insertCount = var_insertCount;
                    SET @currentInput = curInput;
                    EXECUTE s3 USING @tempConnectionValue, @insertCount ,@currentInput;
                    IF @var_itemCount = 0 THEN
                        EXECUTE s4 USING @tempConnectionValue,@insertCount;
                        IF @var_itemCount_inner = 0 THEN
                            EXECUTE s1 USING @tempConnectionValue,@insertCount,@currentInput;
                        ELSE
                            EXECUTE s5 USING @currentInput,@tempConnectionValue,@insertCount;
                        END IF;
                    END IF;
                END IF;
                SELECT '' INTO curInput;
            ELSE
                SET curInput=CONCAT(curInput,curLetter);
            END If;
            ITERATE csv_loop;
        END IF;
        /* Select Current Input if not ending with ,*/
        IF curLetter != ',' THEN
            SET var_insertCount = var_insertCount + 1;
            SET @insertCount = var_insertCount;
            SET @currentInput = curInput;
            EXECUTE s3 USING @tempConnectionValue, @insertCount ,@currentInput;
            IF @var_itemCount = 0 THEN
                EXECUTE s4 USING @tempConnectionValue,@insertCount;
                IF @var_itemCount_inner = 0 THEN
                    EXECUTE s1 USING @tempConnectionValue,@insertCount,@currentInput;
                ELSE
                    EXECUTE s5 USING @currentInput,@tempConnectionValue,@insertCount;
                END IF;
            END IF;
        END IF;
        LEAVE csv_loop;
    END LOOP csv_loop;
END //
DELIMITER ;

A working query

Will be changed in order for simplification.
CALL seperateCSV('ConnectionKey','1','1','csvStorageTable', 'InsertKey','connectionID');
CALL seperateCSV('ConnectionKey','99','Micky,mouse','csvStorageTable', 'InsertKey','modifications');

This works
CALL seperateCSV('fk_listID','100','1,100','listEntry', 'ListEntryID','connectionID');
CALL seperateCSV('fk_listID','100','2,200','listEntry', 'ListEntryID','item');

Lets see if I understand my own code with this test

CREATE TABLE X(
    matchingID BIGINT UNSIGNED, //Connects all the data
    currentKey BIGINT UNSIGNED, //Connects the payloads
    payloadOne VARCHAR(50), //Holds data populated by the first CSV
    payloadTwo VARCHAR(50) //Holds data populated by the second CSV
);
CALL seperateCSV('matchingID','6','1,2,3,4,5','X','currentKey','payloadOne');
CALL seperateCSV('matchingID','6','Pam,Dave,Peter,Nathan,Drew','X','currentKey','payloadTwo');