Greetings,
John Fitzsimons wrote:
>On Sun, 21 Nov 2004 06:32:40 -0500, Patrick Questembert wrote:
>
>
>>Remember the power of Google too,
>>
>>
>
>Yes, I have. The power of Google IIRC gave me more than 9,000 "hits"
>with my "1044 Access Denied" error.. Page after page of windows users
>trying to work out why they got that error. The majority not getting
>much more of an answer than RTFM. If a windows program had that many
>people unable to even get to step number one then the programmer would
>probably be laughed out of town. Or leave in disgrace of his own
>volition.
>
>Creating a database and creating/populating it is the most basic of
>database tasks but MySQL makes one jump through hoops to do something
>that is essentially trivial. In windows such things can be done with a
>half dozen clicks of a mouse and maybe five minutes work.
>
>
>
Sincerely, MySQL is the best open source database ever, it has
everything a newbie would need to create/populate a database, if you
know the basic about databases and SQL its possible to create a table
azap, if you don't, even Access would be difficult to deal with. If you
want your database to be stable and reliable, than you'll have to type
and understand the commands, else you'll get even more errors, mouse
clicks do what a programmer told the program to do, not always the best
choice.
>>if
>>you do a search on "load data infile MySQL" the first match you get is what
>>you need: http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
>>
>>
>
>Thanks for pointing out the exact part of the manual. It doesn't
>however explain in any way how/where I am supposed to put that syntax.
>
>Added to that it mentions such things as "The mysqlimport client"
>without giving a download link and/or instructions as to where it is
>installed to/run from.
>
>
>
All MySQL executables are in your /bin directory, where they can be
reached by command prompt, they'll give you their syntax, and the manual
will tell you exactly what each command will do. The mysqlimport client
is the "mysqlimport" binary, that was designed to load data into MySQL
from a number of different sources.
>>To your questions:
>>
>>
>
>
>
>>The format of the source file can be CSV
>>
>>
>
>Yes, I was pretty sure I had read that in one of the many pages I had
>read. I couldn't however see how a C.S.V. file could supply data and
>info such as "key" column data etc.
>
>
>
You define the table on MySQL, the structure of the table (keys,
definitions, data types) are separated from the data, so, first you'll
define the table, then you can import the data, accourding to the table
definition, it will give you warnings or errors if the data does not
match the definition.
>>- if that's the case, be sure to
>>include the "FIELDS SEPARATED BY ','" directive for the load data statement.
>>
>>
>
>Command syntax. You know, I was thinking that this mailing list was
>for windows users. Yet almost every answer talks about Unix commands.
>This is like joining a Toyota mailing list where every post is about
>Fords.
>
>
>
Windows versions 3.x and 9x runs using a DOS core, that is a command
line OS, and users of the winXP have the MySQL Query Browser and the
MySQL Administrator, that are GUIs for what you need, and even using XP,
the command line is faster and easy to use.
>>It is NOT .SQL - if it is .SQL, these would be statements you want to
>>execute, which has nothing to do with loading data from a text file.
>>
>>
>
>Well, thats what I first thought. Yet my hosting server had a video
>tutorial where an .sql file appeared to be imported into a MySQL
>database to create/populate it. From both a data and structure point
>of view.
>
>
>
If you already have a table, you can export its definition and data in
form of a .sql file, that when imported, will create and populate the
table, you can even "build" an .sql file with a "CREATE TABLE" statement
and various "INSERT INTO", one for each row of the table.
>>Regarding location of the file, you have two choices with LOAD DATA:
>>LOAD DATA LOCAL INFILE - will load data from a file located on the client,
>>in which case the pathname of the file corresponds to the client
>>
>>
>
>What's "the client" ? The hosting server ? A program ? If so then is
>there a specific directory to load from ? The "mysql> LOAD DATA INFILE
>'data.txt" suggests I need to find the mysql directory of the root on
>my host's server.
>
>
>
I am sorry, but I can give you another answer with command line. Inside
the /bin directory there is the "mysql" program, commonly know as the
client, or mysql console. You can use the -h <host> option to specify
the host you'll connect to, and the -u <user> to log in. Once inside the
console, you can give the host any mysql commands, including "LOAD DATA
INFILE".
>>LOAD DATA - will load data from a file located on the server, in which case
>>the pathname of the file corresponds to the server
>>
>>
>
>C:\ ?
>
>
>
>>Note that the server may be configured to disallow loading from files on the
>>client.
>>
>>
>
>Just what I needed. Another hoop to jump through. Thanks for pointing
>that out.
>
>
>
If you're running the server on you machine, its ok to give paths to
files on your system, using the --local option.
>>Lastly, you are NOT transferring "from the client's C: drive to the server's
>>C: drive", you are loading from either the client
>>
>>
>
>Well, the client would presumably be on my C: drive.
>
>
>
>>or the server's drive onto
>>a TABLE in the MySQL database, so only one drive is involved here.
>>
>>
>
>Okay. Thanks.
>
>Regards, John.
>
>
>
>
Ok, now, lets go to the point. Have you defined your table structure? If
not, you'll have to do it, using a GUI program or the mysql console. I
suggest MySQL Query Browser, but you can take a look at an old post
regarding "best GUI client" at this mailing list. Once you do it (you'll
have to connect to your host, that will be asked by the GUI client you
choose) you can create the table, after creating your table, you can
load data in it by using the mysqlimport program like this:
C:\mysql\bin\mysqlimport.exe --user="root" -C -d
--fields-terminated-by="^" test "P:\PSAN01"
where test is your database, PSAN01 is the file to import, wich has
fields separated by ^s, but you can specify fields-terminated-by,
fields-enclosed-by, lines-terminated-by, etc. Now, you'll be angry with
me again, but you can refer to the manual pages for all the syntax.
Sorry if I can't give you the exacts commands or all the steps to do
what you want, I tried my best, if you need more help, please tell me
the table structure you need and the type and definition of the file to
load data from.
"If you build a program that stupid people can use, only stupid people
will use it" - Jhon Letterman
