automatically importing text file to table

[satre]

Hi,

using MySQL version 5.0.82sp1

I have a text file (attached) that is basically already a table and I've imported it successfully (after a little fixing in Word) using the CSV using LOAD DATA option with a space " " as the field delimiter, but as you might notice, some of the lines have 4 spaces in front of them. These were easily removed with Word's find/replace command, but I'm hoping there's a way to just upload the file as is and some way to ignore those 4 spaces so I don't have to process every single file I have like this. Any ideas?

 

Further, is there a way to do this through an html interface using php so I don't have to train anyone on how to do this?

 

Best,

Satre

 

[attachment deleted by admin]

[Dennis1986]

Use PHP to remove the extra whitespaces.

http://php.net/manual/en/function.trim.php

[PFMaBiSmAd]

The LOAD DATA ... query accepts a SET term that can be used to modify values, for example -

LOAD DATA LOCAL INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

 

You would use the mysql TRIM() function to remove any leading spaces.

 

You can use an upload form, then in the php form processing code execute the LOAD DATA LOCAL INFILE ... query using php code. http://us2.php.net/manual/en/features.file-upload.php

 

[satre]

Tried the SET command afterwards and TRIM cannot be used here. If I'm reading it correctly, according to the manual, only numeric operations can be done here.

 

LOAD DATA LOCAL INFILE 'set PROTNAME.txt' INTO TABLE `test2` FIELDS TERMINATED BY ' ' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'(`type` , `command` , `value`) SET type TRIM

 

was one example I used, and I tried all variations I could think of after the SET command including setting the 'type' column as a variable and trying to TRIM that, and using the operand "=" between and so on. The code works fine without the SET command, just still have those spaces there. Any other ideas?

[satre]

So, I couldn't find any MySQL command to do this, but I ended up pre-processing the file with php as such:

 

$file = 'file/path/name.txt';
$file_contents = file_get_contents($file);
$fh = fopen($file, "w");
$file_contents = str_replace('    ','',$file_contents);
fwrite($fh, $file_contents);
fclose($fh);

 

The trim command never worked, neither in php nor in SQL commands. I think it's because the file is turned into a string, or an array depending on the command you use to read it, but the spaces somehow aren't included in the array? I dunno. No point in dissecting it further for me since this php code will handle it.

 

NB that your permissions have to be set correctly on your file so you can r/w!  I wasted an embarrassing amount of time trying to figure out why the file wasn't changing!