Loading a file into a TABLE

[spwpreston]

Hello,

I am new to MySQL. I am trying to load from a text file to a table,
and am having problems
with the fields in the text file being translated to the correct fields
in the TABLE. Right now,
I have a tab between each field in the text file, something like:

Chicago USA 2,500,000
New York USA 5,000,000
Madrid Spain 2,000,000

My question: Can I use something else to differentiate between fields,
like a coma?
This would greatly help me.

Thank you for your time.

-D

[Bill Karwin]

<sp********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Chicago USA 2,500,000
New York USA 5,000,000
Madrid Spain 2,000,000

My question: Can I use something else to differentiate between fields,
like a coma?

Certainly. You should read about the MySQL command LOAD DATA INFILE.
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

For instance:
LOAD DATA INFILE 'textfile.txt' INTO TABLE databasename.tablename
FIELDS TERMINATED BY ','

Note, however, that your data already have commas in them. If you need to
have commas in the values in addition to being used as field terminators,
you must escape the literal commas. The default escape character is a
backslash (\). For example:

Chicago,USA,2\,500\,000
New York,USA,5\,000\,000
Madrid,Spain,2\,000\,000

Also note that MySQL doesn't understand numbers with thousands separators as
integers. You can input those values into string fields, but not numeric
fields. In the example above, it'll truncate the numbers and insert them as
integers 2, 5, and 2 respectively.

Regards,
Bill K.

[Xicheng]

sp********@gmail.com wrote:

Hello,

I am new to MySQL. I am trying to load from a text file to a table,
and am having problems
with the fields in the text file being translated to the correct fields
in the TABLE. Right now,
I have a tab between each field in the text file, something like:

Chicago USA 2,500,000
New York USA 5,000,000
Madrid Spain 2,000,000

My question: Can I use something else to differentiate between fields,
like a coma?
Do you have spaces in the second field?? or can you provide more sample
data?

for you current data(without spaces in the middle field) I can do(Under
Windows):

perl -alne "$,='#';print qq(@F[0..$#F-2]),@F[-2,-1]" mytb > mynewtb

or if all lines in your table are fixed-width-field records,

perl -alne "$,='#'; print unpack('A10A8A*',$_)" mytb > mynewtb

where 10 and 8 are the number of characters in your first and second
field, you need to count by yourself..

=======result=========
Chicago#USA#2,500,000
New York#USA#5,000,000
Madrid#Spain#2,000,000
====================
If you are under Linux, exchange all double quotes with single quotes
in the above command lines:

then under mysql:
LOAD DATA LOCAL INFILE 'D:\\mynewtb' INTO TABLE mydb.mytb FIELDS
TERMINATED BY '#' LINES TERMINATED BY '\r\n';

Best,
Xicheng
This would greatly help me.

Thank you for your time.

-D

[Jeff North]

On 12 Feb 2006 12:53:50 -0800, in mailing.database.mysql
sp********@gmail.com
<11**********************@z14g2000cwz.googlegroups .com> wrote:

| Hello,
|
| I am new to MySQL. I am trying to load from a text file to a table,
| and am having problems
| with the fields in the text file being translated to the correct fields
| in the TABLE. Right now,
| I have a tab between each field in the text file, something like:
|
| Chicago USA 2,500,000
| New York USA 5,000,000
| Madrid Spain 2,000,000
|
| My question: Can I use something else to differentiate between fields,
| like a coma?
| This would greatly help me.
|
| Thank you for your time.

When importing data, from any source, I place the data into a
temporary table, massage the data to the way I want it and then
transfer this data into my main table(s).

Setup:
create a temp table:
create table tmpImportTbl {
city varchar(100),
country varchar(100),
population varchar(100)
} type=MYISAM;

Why use varchar for the population? This is separated by commas. As
Bill has pointed out, they will be truncated so we need to store
whatever value into a text field.

Next the import:
LOAD DATA LOCAL INFILE 'c:\\temp\\population.txt' INTO TABLE
tmpImportTbl FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES
TERMINATED BY '\n' IGNORE 1 LINES;

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

This is saying:
get the file that is located within your c:\temp folder (change this
to where ever you save your file to)
place the data into the tmpImportTbl table
The file has the following format:
a tab character seperates the field values
(optionally) the values are enclosed in double quotes
when a linefeed character is found, start a new record
ignore the first line of the file (because it is an information row
i.e. the fields names).

*ALWAYS* check the mysql message after it has completed the process.
Make sure the number of records imported match the number of lines
within your text file. If they do not match then you need to empty the
tmpImportTbl and tweak the import parameters.

All being well the data should now be in the tmpImportTbl table.

Now to correct the population information. This is easily done by
removing the commas:
UPDATE tmpImportTbl set population=replace(population,',','');

Now to transfer the corrected data into the main table:

INSERT INTO mainTable (city,country,population) select city, country,
population from tmpImportTbl;

The main table structure might be:
create table mainTable {
city varchar(100),
country varchar(100),
population bigint(20) unsigned default '0'
/*
other fields defined here
*/
} type=MYISAM;
Clear out the temp table:
truncate table tmpImportTbl;
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Michael Austin]

sp********@gmail.com wrote:

Hello,

I am new to MySQL. I am trying to load from a text file to a table,
and am having problems
with the fields in the text file being translated to the correct fields
in the TABLE. Right now,
I have a tab between each field in the text file, something like:

Chicago USA 2,500,000
New York USA 5,000,000
Madrid Spain 2,000,000

My question: Can I use something else to differentiate between fields,
like a coma?
This would greatly help me.

Thank you for your time.

-D

The general term for this is ETL (Extract/Transform/Load).. so this would be a
two step process if you want to use a number/integer datatype for the
"population" field - (will make it much easier do execute math computations from
the database)

I am "assuming" that the field seperator in this case is a TAB character. If
this is so, then I would create a procedure that would:
1) EXTRACT DATA
2) TRANSFORM DATA (if necessary)
a) read each record
b) replace the TAB-delimiter with another meta-character like "~".
see http://us3.php.net/str_replace
c) replace the commas in the third field with "" (remove them)
again - see: http://us3.php.net/str_replace
d) write the file to a "transformed" file
3) LOAD "transformed" file into the database.

Or - you could re-extract the data and put it into a more readable format and
then just load it into the database.

Example:
Chicago~USA~2500000
New York~USA~5000000
Madrid~Spain~2000000

YMMV.

--
Michael Austin.
Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)