Usually MS Excel provides many options to import data from other sources like XML or using data source. Now, the data from excel file has to be exported to a form which MySQL could read and insert into existing relational tables. You can think about .sql file. Usually, MySQL tables can be imported by SQL script (.sql) files. SQL script file contains the DDL and DML queries. But in our case, we need to load the rows of excel file into a table.
Category: MySQL
Permanent link to this article: https://blog.openshell.in/2011/02/excel-to-mysql/
Feb 01
How to Back Up and Restore a MySQL Database
As you know your website data is stored in your database server. To prevent disaster, backup your data regularly is a must for you and every website owner. There are many ways to backup MySQL data, one of them is using mysqldump to dump your MySQL data to a file. Dump all MySQL Databases mysqldump …
Permanent link to this article: https://blog.openshell.in/2011/02/how-to-back-up-and-restore-a-mysql-database/
Jan 30
Mysql Error: #1045
After installed the XAMPP and mySQL, when you typed http://localhost/phpmyadmin in your web browse to get into the phpmyadmin, it shows a error like this: #1045 – Access denied for user ‘root’@’localhost’ (using password: NO) At this point you have to edit the config.inc.php file on line 21 $cfg[‘Servers’][$i][‘password’] = ”; to $cfg[‘Servers’][$i][‘password’] = ‘yourpassword’; …
Permanent link to this article: https://blog.openshell.in/2011/01/mysql-error-1045/
Jan 30
Replace String In Mysql With One Query
We know how to change data inside a field by using update command inside a query. Here we will try to replace part of the data without changing the entire data of the field. The SQL query we design will work like string replacement in any script we use. For this we will use replace …
Permanent link to this article: https://blog.openshell.in/2011/01/replace-string-in-mysql-with-one-query/
Jan 14
Searching Techniques with SQL
I want to explain basic searching techniques using SQL LIKE statement. Replace spaces Before executing SQL query searching word replace spaces with %(symbol) using PHP str_replace function. Here searching word jquery ajax spaces replaced with %(symbol)- jquery%ajax SQL ‘LIKE’ SELECT * FROM messages WHERE msg LIKE ‘%jquery%ajax%’ ; CODE: <?php if($_SERVER[“REQUEST_METHOD”] == “POST”) { $q=$_POST[‘q’]; …
Permanent link to this article: https://blog.openshell.in/2011/01/searching-techniques-with-sql/
Jan 12
Reset Forgotten MySQL Root Password
Reset MySQL Root Password in Linux: You can reset forgotten MySQL database server password with following five easy steps in ubuntu. Step 1: Stop the MySQL server process. /etc/init.d/mysql stop Step 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password. mysqld_safe –skip-grant-tables Step 3: Connect …
Permanent link to this article: https://blog.openshell.in/2011/01/reset-forgotten-mysql-root-password/
Dec 29
MySQL Subquery Optimization
Never use a subquery inside a WHERE field IN(…) the SQL query. It will slow down the process. Slow: SELECT * FORM table1 WHERE field1 IN (SELECT field2 FROM table2 GROUP BY field1) Instead of using a subquery inside an IN(…), use INNER JOIN query. It will speed up the process, but in IN(…) query …
Permanent link to this article: https://blog.openshell.in/2010/12/mysql-subquery-optimization/
Dec 10
Importing data from CSV File
Here is an example to importing data from csv file into MySQL table. LOAD DATA INFILE “/home/mysql/data/file_name.csv” INTO TABLE table_name FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\\n’;
Permanent link to this article: https://blog.openshell.in/2010/12/importing-data-from-csv-file/
Dec 10
Find duplicate repords in Table
Here is an example to find duplicate records from the table. select address, count(address) as cnt from mailing_list group by address having cnt > 1 order by cnt;
Permanent link to this article: https://blog.openshell.in/2010/12/find-duplicate-repords-in-table/
Dec 10
Export a table into csv format
Here is an example to export data from MySQL into CSV File Format. SELECT * INTO OUTFILE ‘/tmp/file_name.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘n’ FROM table_name;
Permanent link to this article: https://blog.openshell.in/2010/12/export-a-table-into-csv-format/