Excel, phpMyAdmin, php

extract data from excel into database

[yllai]

initially if i have all the data keyed into Excel format, i want import using phpAdmin..bcos i need these data for my system coded with script.I think this will be more faster than i entering the data into database manually. any suggestion? any example? where can i get these information?

[johnerazo]

From Excel, export your work as .csv or comma delimited text file.

From phpMyAdmin, open the database and select the target table then click on 'Insert data from a textfile into table'.

Hth,
John Erazo

[yllai]

in phpMyAdmin, what is the different between "field terminated by" and "filed enclosed by" and "field escape by"? how do I set all these?

i export my data in excel to txt file, each field separated by ; as below

field1,field2, field 3

[coopster]

I don't use phpMyAdmin, so I can't tell you how to set the values, but I can tell you what they mean.

• FIELDS TERMINATED BY

  tells where a field ends.
  

• FIELDS [OPTIONALLY] ENCLOSED BY

  controls quoting of fields. For output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character.
  

• FIELDS ESCAPED BY

  controls how to write special characters.

If you don't specify a FIELDS clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Example:

Let's say we use something like this:

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'

Our data should be formatted with the fields enclosed in double quotation marks, separated by commas, and any special characters escaped with a single slash:

"field 1", "field 2", "field 3", "field 4 with slash \\ character in it"

Resources:
LOAD DATA INFILE [dev.mysql.com]
SELECT [dev.mysql.com]

[HughMungus]

in phpMyAdmin, what is the different between "field terminated by" and "filed enclosed by" and "field escape by"? how do I set all these?

Speaking from experience, you need to change "field terminated by" to whatever comes between each field in the .csv file (viewed in notepad, not excel). If you exported to .csv, this is a comma. Change the semicolon to a comma.

I've never had to mess with "fields escaped by" and I doubt you'd have to either.

[henry0]

To do it like 1,2,3 I use a little tool
named xls2sql works well for me, and no I do not get kick back :)

regards
Henry