Project Design Advice

[john]

Hello all. Thanks for reading and for any advice...

Ok, I have a linux webserver with php/mysql/apache.
Next, there are csv (comma separated values) log files on a user's
WINDOWS machine
(and not on the webserver)
in a local directory of theirs like so:

d:\reports\log_ data2004_`\log_ data_jan\log_fi le_jan.txt
d:\reports\log_ data2004_2\log_ data_feb\log_fi le_feb.txt
etc...
(The \reports\log_da ta\ has several subordinate folders with txt log
files in each...

Now, I need to get these csv text files from the user's windows machine
and onto the linux server and into mysql...
I am able to manually get a single log file into mysql using LOAD DATA
via the command line, however, I need a good way to load all log files
(from their subordinate folders on the windows box) into the database.

I cannot get (and don't think it works anyways) LOAD DATA to work from
a php script.

SHould I have them upload the individual log files ontoe linux box
and process them there?
And if so, what would be the best way to have php look in a directory
(once the files are on the linux box) and load these files into mysql ?

Any ideas?

Thanks very much!

[jerry gitomer]

Re: Project Design Advice

john wrote:[color=blue]
> Hello all. Thanks for reading and for any advice...
>
> Ok, I have a linux webserver with php/mysql/apache.
> Next, there are csv (comma separated values) log files on a user's
> WINDOWS machine
> (and not on the webserver)
> in a local directory of theirs like so:
>
> d:\reports\log_ data2004_`\log_ data_jan\log_fi le_jan.txt
> d:\reports\log_ data2004_2\log_ data_feb\log_fi le_feb.txt
> etc...
> (The \reports\log_da ta\ has several subordinate folders with txt log
> files in each...
>
> Now, I need to get these csv text files from the user's windows machine
> and onto the linux server and into mysql...
> I am able to manually get a single log file into mysql using LOAD DATA
> via the command line, however, I need a good way to load all log files
> (from their subordinate folders on the windows box) into the database.
>
> I cannot get (and don't think it works anyways) LOAD DATA to work from
> a php script.
>
> SHould I have them upload the individual log files ontoe linux box
> and process them there?
> And if so, what would be the best way to have php look in a directory
> (once the files are on the linux box) and load these files into mysql ?
>
> Any ideas?
>
> Thanks very much!
>[/color]
John,

If you can easily generate a list of the files you wish to load
(with full pathnames) and copy it to your Linux server the
easiest way to solve your problem is to use a text editor the
file to a set of LOAD DATA commands and then execute the file.

For example assume you are using the vi editor in Linux:

Step 1 create a file containing 1 file name to load per line:
d:\reports\log_ data2004_`\log_ data_jan\log_fi le_jan.txt
d:\reports\log_ data2004_2\log_ data_feb\log_fi le_feb.txt

Step 2 edit the file to convert each line into a LOAD DATA command:
:%s/.*/LOAD DATA INFILE "&" INTO TABLE my_table FIELDS
TERMINATED BY "," ENCLOSED BY "\\"";\

Step 3 save the edited file and execute it. (Assume you called
it loadfiles)

.. ./loadfiles

(The first dot says execute it even though it isn't defined as
being executable. The ./ says it is in the current directory.)

Step 4 QA the data to be sure that your load worked properly.


HTH

Jerry