dodge20
asked on
Select Into Outfile Variable Name
Is it possible to do a select into outfile and have the output file be a variable name? I am trying to write a cursor that will loop through a recordset, pass a variable to a procedure that exports a text file for each variable passed. Before I get to deep into this I would like to know if it is possible.
Here is my query.
select distinct student_name,student_addre
from student,ht_zips,media
where student_zip = htz_zip
and htz_media_id = md_ID
into outfile 'C:/tmp/myvar.txt'
Fields Terminated by ','
Lines Terminated by '\r\n'
Also is it possible to skip fields in the output file? So if student_honors is null that it would skip that field?
I tried the IFNull, but that still prints a comma. I would like this to be skipped completely.
Here is my query.
select distinct student_name,student_addre
from student,ht_zips,media
where student_zip = htz_zip
and htz_media_id = md_ID
into outfile 'C:/tmp/myvar.txt'
Fields Terminated by ','
Lines Terminated by '\r\n'
Also is it possible to skip fields in the output file? So if student_honors is null that it would skip that field?
I tried the IFNull, but that still prints a comma. I would like this to be skipped completely.
The file name cannot be variable from woithin the mysql environment. But you could write a small script to construct your select statement, and let the script choose the file name. Then use mysql to process the script
Try saying
where student_zip = htz_zip and htz_media_id = md_ID and student_honors is not NULL
Try saying
where student_zip = htz_zip and htz_media_id = md_ID and student_honors is not NULL
ASKER
Hi teraplane
I don't want the record entire record skipped, I just want the actual field skipped on what the outfile produces.
I don't want the record entire record skipped, I just want the actual field skipped on what the outfile produces.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can that be done through mysql? And if so, How?
Not as far as I know. But a scriptings language like perl, sed or VB would do it quite easily.
ASKER
Any help with the path of the file? I can't seem to get anything to work with that. Also on the skipping of fields, could that be done within a procedure using if else statement?
ASKER
I got this worked out using vbscript.
ASKER
After working with this for a while I figured out you can output to a variable name. My problem now is trying to figure out how to set up a path variable that works with this. I would like to create the file in C:/tmp/myMedia/ but I can't seem to figure out how. Any suggestions would be great.
DELIMITER $$
DROP PROCEDURE IF EXISTS `link`.`createtextfiles2` $$
CREATE PROCEDURE `link`.`createtextfiles2` (myMedia varchar(6),myRelease varchar(100))
BEGIN
SET @myMedia = myMedia;
SET @myRelease = myRelease;
SET @sql_text =
CONCAT(
"select distinct student_name,student_addre
from student,ht_zips,media
where student_zip = htz_zip
and htz_media_id = md_ID
and md_ID = @myMedia
into outfile 'C:/tmp/"
,myMedia, myRelease
, ".txt'"
"Fields Terminated by ','
ESCAPED BY ''
Lines Terminated by '\r\n'"
) ;
PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;
END $$
DELIMITER ;