Looping through a file to create separate CSV files

[William Carney]

New to MySQL, used FoxPro back in the day.

Here is my situation. I have a table created from data from a legacy app, with a field TAGS that contains values separated by pipes | (ex: |400|457|665|) and another field USERID that contains a numeric value.

Tags can be any number between 1 and 4000, and the value may not necessarily be in the file I am importing.

For example

TAGS, USERID
|400|57|600|, fab123
|57|60|, fcd245
|600|,abc123

What I need to do is export separate CSV files containing one TAG and the USERIDs of people that have that tag. Using my example above, I’ll have created files

400.csv
57.csv
600.csv
60.csv

I know I can brute force export the data:

SELECT ‘Tag', 'UserID' FROM my_table WHERE tags LIKE "%|400|%" INTO OUTFILE '~/Downloads/400.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';
SELECT ‘Tag', 'UserID' FROM my_table WHERE tags LIKE "%|401|%" INTO OUTFILE '~/Downloads/401.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

But this sure doesn’t seem graceful and it also creates a bunch of empty files, one for each number.

Doing something like I want was easy in FoxPro:

FOR counter = 1 TO 4000
COUNT TO m.it ALL FOR "|" + ALLTRIM(STR(counter)) + "|"$tags
IF m.it>0 THEN
COPY to "c:\temp\" + ALLTRIM(STR(counter)) +".csv" TYPE csv
ENDIF
ENDFOR

How does one accomplish the same in MySQL?