Converting excel data to mysql database.

Converting excel data to mysql database is very important in those days because so many companies wants maintain their old/current excel data into mysql database so that they can access from any where by maintaining some authentication, not only authentication but also useful for particular record, attribute and so on..

In most cases this methodologies are useful for those who want convert their previous old excel data to mysql database. Mean while if you want Converting excel data to mysql database you have to follow three conditions and the conditions are,

1. Excel data must be in CSV (Comma delimited) format.

2. It should not contain any highlights or colors used.

3. The first row and first column should fill up with some value that means it not be null.

If you familiar with above conditions than follow next three steps for converting excel data to mysql database.


2. Create some database, in that create a table name but this table name must same to excel sheet name (which you want to convert) and when come to no. of fields just take no. of fields(headings) located in excel data.

3. Now fill the fields (headings) and provide data type=”text” for each (remember no. of fields equal to no. of columns of excel data).

4. Now click tab called import and browse respective CSV,
 Set utf8 as character set of the file,
 Tick "Allow interrupt of import in case script detects it is close to time limit." This might   be good way to import large files; however it can break transactions, 
Number of records (queries) to skip from start: 1 (because one row in excel you already written as headings in mysql database’s table so we don’t need that first row).

When come to Format of imported file, tick CSV with LOAD data and again in that unpick Replace table data with file, Ignore duplicate rows 

Fields terminated by: “,” (change it to comma or leave it as it is check once which is suitable for you),
Fields enclosed by, Fields escaped by change it to empty or blank it.
Lines terminated by to auto, Column names change it to empty or blank it.
Finally, tick Use LOCAL keyword and click button called GO that’s it.

Important issues:

Whenever you save as excel data to CSV it will ask u that name already exist its better to save with some other name so that your previous data will be safe at least if you done any thing wrong. One more thing and it also ask that “selected work file types does not support workbooks that contain multiple sheets” just say ok and again it comes with another txt message just say yes. Now your data is in CSV format.


For any queries contact me via below respective comment box.

1 comment:

Unknown said...

Not working
Error:
Invalid parameter for CSV import: Columns escaped by