suitecrm-sql-csv-dump.md (1643B)
1 --- 2 title: "SuiteCRM SQL database dump to CSV files" 3 date: 2019-01-17T10:18:00 4 tags: ["Formats", "Guides", "Linux", "Servers", "Snippets", "Software", "Work"] 5 --- 6 7 Recently I needed to dump our workplaces existing CRM database to a format that was easy to work with, namely CSV instead of the more likely SQL format. 8 9 To do this easily and quickly I developed a small script that would automatically grab the SQL servers credentials from your config file and then dump the files all from a single file. Your `config.php` usually resides in the `htdocs` folder on your server. 10 11 ``` 12 #!/bin/sh 13 14 # sqlHost=$(grep db_host_name config.php | cut -f4 -d\' | cut -f1 -d\:) 15 confFile=/opt/crm/apps/suitecrm/htdocs/config.php 16 sqlHost=127.0.0.1 17 sqlUser=$(grep db_user_name "$confFile" | cut -f4 -d\') 18 sqlPass=$(grep db_password "$confFile" | cut -f4 -d\') 19 sqlTable=$(grep db_name "$confFile" | cut -f4 -d\') 20 dumpDirectory=db_dump_$(date +%Y-%m-%d) 21 22 echo $sqlHost $sqlUser $sqlPass $sqlTable $dumpDirectory 23 24 mkdir "$dumpDirectory" 25 for table in $(mysql -h $sqlHost -u$sqlUser -p$sqlPass $sqlTable -sN -e "SHOW TABLES;"); do mysql -B -u$sqlUser -p$sqlPass $sqlTable -h $sqlHost -e "SELECT * FROM $table;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $dumpDirectory/$table.csv; done 26 find $dumpDirectory -type f -empty -delete 27 ``` 28 Make the script executable, run it and all being well you should have a folder containing all your csv files. 29 30 One point worth noting is the manual setting of `$sqlHost` as localhost doesn't seem to work with the `mysql` command so this may require changing if you're not running SQL and the script on the same machine.