Daniel, is it that the columns in the file are separated by a variable
number of spaces? Or is it that the columns are fixed-width,
therefore the number of spaces varies with length of data? If the
file is fixed-width, the SQL below ought to work pretty well.
Either way, your situation is not too well supported by LOAD DATA
INFILE, unfortunately.
I'd suggest an intermediate step: read complete lines from the file,
inserting them as text or varchar into a temp table. Then run an
INSERT ... SELECT to move the data from the temp table to your real
table.
- CREATE TABLE temp_data (dataline VARCHAR(255));
- LOAD DATA INFILE 'data.txt' INTO TABLE temp_data
FIELDS TERMINATED BY '' ENCLOSED BY '';
INSERT INTO test (server_time, usr, sys, wio, idle)
SELECT
TRIM( SUBSTRING(dataline, 1, 8) ),
TRIM( SUBSTRING(dataline, 9, 8) ),
TRIM( SUBSTRING(dataline, 17, 8) ),
TRIM( SUBSTRING(dataline, 25, 8) ),
TRIM( SUBSTRING(dataline, 33, 8) )
FROM temp_data;
TRUNCATE TABLE temp_data;
HTH,
Dan
On 27 Sep 2006 08:49:40 -0000, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> >Did you try using a space (' ') as the separator? Did you get an error?
And at 7:41 +0000 27/9/06, [EMAIL PROTECTED] wrote:
>Yes, I did.
...
>So, I need to specify somehow that the fields are delimited by any
>number of spaces...
One answer of course is grep. However, as far as I can determine,
MySQL can only apply grep in the context of a LIKE clause, So...
Do it on your text file before importing, if you have a text editor
that can handle regular expressions. Just search for / +/ and replace
with ' '. Then import using a single ' ' as the 'enclosed by' string.
--
Cheers... Chris
Due to the nature of my application, this would be rather difficult. I would
prefer to import this files directly to mysql. Any other suggestions?
Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
