Tuesday, April 8, 2008

How to adding a bunch of data into database?

If you have a large amount of data to enter and it’s already in a computer file, you can transfer the data from the existing computer file to your MySQL database. The SQL query that reads data from a text file is LOAD. The LOAD query requires you to specify a database.

Because data in a database is organized in rows and columns, the text file being read must indicate where the data for each column begins and ends and where the end of a row is. To indicate columns, a specific character separates the data for each column. By default, MySQL looks for a tab character to separate the fields. However, if a tab doesn’t work for your data file, you can choose a different character to separate the fields and tell MySQL in the query that a different character than the tab separates the fields. Also by default, the end of a line is expected to be the end of a row — although you can choose a character to indicate the end of a line if you need to. A data file for the Pet table might look like this:

UnicornhorseSpiral horn5000.00/pix/unicorn.jpg
PegasushorseWinged8000.00/pix/pegasus.jpg
LioncatLarge; Mane on neck2000.00/pix/lion.jpg

A data file with tabs between the fields is a tab-delimited file. Another common format is a comma-delimited file, where commas separate the fields. If your data is in another file format, you need to convert it into a delimited file. To convert data in another file format into a delimited file, check the manual for that software or talk to your local expert who understands the data’s current format. Many programs, such as Excel, Access, and Oracle, allow you to output the data into a delimited file. For a text file, you might be able to convert it to delimited format by using the search-and-replace function of an editor or word processor. For a truly troublesome file, you might need to seek the help of an expert or a programmer.
The basic form of the LOAD query is

LOAD DATA INFILE “path/datafilename” INTO TABLE tablename

The query loads data from a text file located on your server. If the filename does not include a path, MySQL looks for the data file in the directory where your table definition file, called tablename.frm, is located. By default, this file is located in a directory named for your database, such as a directory named PetDirectory. This directory is located in your data directory, which is located in the main directory where MySQL is installed. For example, if the file was named data.dat, the LOAD command might look for the file at C:\Program Files\MySQL\MySQL Server 5.0\data\PetDirectory\ data.dat.
This basic form can be followed by optional phrases if you want to change a
default delimiter. The options are

FIELDS TERMINATED BY ‘character’
FIELDS ENCLOSED BY ‘character’
LINES TERMINATED BY ‘character’

Suppose that you have the data file for the Pet table, shown previously in this section, except that the fields are separated by a comma rather than a tab. The name of the data file is pets.dat, and it’s located in the same directory as the database. The SQL query to read the data into the table is

LOAD DATA INFILE “pets.dat” INTO TABLE Pet FIELDS TERMINATED BY ‘,’

To use the LOAD DATA INFILE query, the MySQL account must have the FILE privilege on the server host.

You can also load data from a text file on your local computer by using the word LOCAL, as follows:

LOAD DATA LOCAL INFILE “path/datafilename” INTO TABLE tablename

You must include a path to the file. Use forward slashes for the path, even on a Windows computer, such as “C:/data/datafile1.txt”. If you get an error message when sending this query, LOCAL may not be enabled.

To look at the data that you loaded — to make sure that it’s correct — use an SQL query that retrieves data from the database. I describe these types of SQL queries in detail in the next section. In brief, use the following query to look at all the data in the table so that you can check it:

SELECT * FROM Pet

No comments:

 
breast-cancer diabetes-informa... weight-losse lung-mesotheliom... eating-disorders medical-billing php-and-mysql skin-cancer medical-health astronomy-guide cancer-diseases health insurance seo-news-2008 forex3003 lawyer-lookingforalawyer earnmoneyonline-earn forexautotrading-forex forex-trade forextrading forex-trading-forex-trading-08 searchingforcancertreatment adsense jiankang8008 beauty-girl forex5005