-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
anupam --
...and then [EMAIL PROTECTED] said...
%
% Hi!
Hello!
% this is a beginner's question about MySQL. I have looked at the online MySQL
% documentation and have not found an answer.
I would say that this is actually a question about data conversion more
than about mysql... Of course, there may be conversion tools within
mysql of which I am unaware :-)
%
% I am looking for suggestions to read a fixed-column format file into a
% MySQL database with about 50,000 observations and 400 variables (fields).
% Each
% variable is in 2--5 colums of a text file with no column delimiters and
OK; that sounds like a great job for perl. You need to take apart the
line of data and break it into its 400 pieces, and perl is quite good at
that.
% blanks for missing values. Also, how do I create field names and labels for
% the values in each field? This will be used for creating subsets for
What do you mean by labels? Do you mean the columns/fields in the
database table, or do you mean for printing?
What I would do would be to define the fields in the input -- say,
columns 1-4 are field1, columns 5-7 are field2, columns 8-12 are field3,
and so on -- and then write my perl code to read each line of the file
and spit out the INSERT VALUES syntax for a table that you've already
defined -- perhaps with the same perl script. Your output should look
something like
CREATE TABLE gollybigtable
(
id int unsigned not null default 0 auto_increment primary key ,
field1 char(4) ,
field2 char(3) ,
field3 char(5) ,
...
fieldN char(n)
) ;
INSERT INTO TABLE gollybigtable values
('','aaaa','aaa','aaaaa',...),
('','bbbb','bbb','bbbbb',...),
...
;
and should be perfect for feeding to mysql. Of course, you could use
perl's DBI module to talk to mysql directly and not only create the table
but also fill it directly from the script. It's an extra step that you
may or may not want to bother to take.
You'll want to take a look at the data to see if any of it belongs in
separate tables; you might end up with about 400 tables, each with an ID
and a variable, or you might actually end up with one big table with 400
columns plus a unique identifier and perhaps some description.
% statistical analysis. Also, which table type is good for this purpose? I
% will be thankful for any suggestion on this.
Since you're just holding data and not, as far as I can tell, doing lots
of transactions, an ordinary myisam table is probably quite sufficient.
%
% anupam.
HTH & HAND
mysql query,
:-D
- --
David T-G * There is too much animal courage in
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health"
http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (FreeBSD)
iD8DBQE+SA2SGb7uCXufRwARAhozAJ0SCmD+Njv2crVNEynybtM9RTZeuQCff2NJ
ZDlHNZO/2ug2ID4j1qypfQ8=
=o5Fc
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
