Can anyone please tell me the mysql command to execute a query to select records and export them into a csv file? Thanks, jsb1022
Converting txt to csv I found the command to export the data from mysql to a txt file. Now I need the command to convert the txt file to a .csv format?
how do you export it to a .txt file? A .csv file isn't that much of a difference, instead of (probably tabs) you use commans to seperate the fields... but then I'd rather use the pip | as it is not a standard character.
Mysql The below is the script I am using to place the .cdr into a .txt file. My question is how do I find the .txt file? I cannot find it. I looked in the tmp folder but its not there.... select * into outfile 'master.txt' fields terminated by ',' from sigpro.cdr where billsec > '0' and calldate between '20071101' and '20071201'; Then I need to convert the txt to a csv in order to FTP to a website. This seems a bit advanced for a junior linux person and I'm mainly windows. Thanks
how do you run that script that you posted? I'd create a PHP script to do an export... Here's one I use: Code: <? // MySQL Connection details require_once ('../config.inc.php'); // Connect to DB mysql_connect($INFO['sql_host'],$INFO['sql_user'] ,$INFO['sql_pass']) or die("Unable to connect to SQL server!"); mysql_select_db($INFO['sql_database']) or die("Unable to select database!"); unset($output); $header = "id\tname\tfirstname\tstreet1\tstreet2\tzip\tcity\temail"; // Get info from Members Table and Build Member ID Array $sql = "SELECT * FROM members ORDER BY id ASC"; $result = mysql_query($sql); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $output .= $row["id"] . "\t" . $row["name"] . "\t" . $row["firstname"] . "\t" . $row["street1"] . "\t" . $row["street2"] . "\t" . $row["zip"] . "\t" . $row["city"] . "\t" . $row["email"] . "\n"; } $output = $header . $output; $fp = fopen ("data.txt", "wb"); fwrite ( $fp, $output); fclose($fp); header("Content-type: application/txt"); header("Content-Disposition: attachment; filename=data.txt"); readfile('data.txt'); ?> You'll be prompted for a TAB-separated .txt file for downlaoding.
If you have phpmyadmin installed it will output to csv. Otherwise is either Perl, PHP or Python to do what you want I believe.....
Well I am very basic with Linux, i dont know how to tell if PHP admin is installed. Whats the easiest way to convert a txt file to csv in linux?
its a web interface to mysql. if you aren't sure you prolly don't have it. if you have a cpanel based site or a virtual hosted site you may but you'd have to look around in the control panels or ask your provider. The script sjau provided would do the trick with a little customization: Code: mysql_connect($INFO['sql_host'],$INFO['sql_user'] ,$INFO['sql_pass']) or die("Unable to connect to SQL server!"); mysql_select_db($INFO['sql_database']) or die("Unable to select database!"); Change sql_host, sql_user, sql_pass and sql_database to their respective values for your DB. Code: $header = "id\tname\tfirstname\tstreet1\tstreet2\tzip\tcity\temail"; change id name firstname etc to your table headers and replace \t with , Code: $sql = "SELECT * FROM members ORDER BY id ASC"; change that to be a select * from <yourtable> and order however you want. Code: $output .= $row["id"] . "\t" . $row["name"] . "\t" . $row["firstname"] . "\t" . $row["street1"] . "\t" . $row["street2"] . "\t" . $row["zip"] . "\t" . $row["city"] . "\t" . $row["email"] . "\n"; Again change id name etc to your own table column names (add and remove as needed just follow the patterns). Again replace any instance of \t with a , Then put that script on your web server and call the script directly in a web browser....so for example you can call it dump.php and put it in the root of your web site....then call http://yourwebsite.com/dump.php and save the file it presents.... I would suggest you remove the script as soon as you are done cause anyone can get a dump of your db otherwise....
Ok, so when I am logged into mysql and I run this: select * into outfile 'master.txt' fields terminated by ',' from sigpro.cdr where billsec > '0' and calldate between '20071101' and '20071201'; Where do I find the master.txt file? Then, how would I convert the master.txt file to a csv? jsb1022
I was able to pull the data I need from the database in mysql in a txt file and I moved it from the tmp folder to a folder I created in the /home directory. I did this b/c I wasnt sure if the linux server is rebooted if the tmp directory gets deleted. Anyway, I now need to conver this txt file over to a csv file so I can FTP it to someone.
Thats not the answer here. You cannot just change a .txt file to a .csv file. It will not work b/c of the comma delimiter needed.
csv --> comma separated values as you already did separate with commas it is a .csv file with a .txt extension.
