Each iteam with surrounded by ~ is a different value (the first and last value are missing 1 ~). However, not all values should be included. Fields number 1, 5, 7 and 12 should be omitted and not entered to the table.
You can also discard an input value by assigning it to a user variable and not assigning the variable
to a table column:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);
I am not too sure how to use this. What I need is to ignore a few columns and get rid of the extra whitespaces and ~'s.
I know you can specify the delimiter in LOAD DATA INFILE but this appears to be more messy as there are 2 ~'s plus spaces.
I didn't expect to struggle with this all day but I am. still no real progress....
1. I use MySQL 4 and @dummy is not supported. my workaround is to get all the columns in and then drop the ones I don't need. Any better way to achieve that with MySQL 4?
yarons wrote:1. I use MySQL 4 and @dummy is not supported.
What makes you think that?
well, I tried the query with the @dummy fields and got a syntax error. When I took them out the same query worked fine.
I also didn't find any reference to @dummy in MySQL 4 manual. Am I wrong in thinking that?
yarons wrote:1. I use MySQL 4 and @dummy is not supported.
What makes you think that?
well, I tried the query with the @dummy fields and got a syntax error. When I took them out the same query worked fine.
I also didn't find any reference to @dummy in MySQL 4 manual. Am I wrong in thinking that?
@dummy is an SQL variable. MySQL 4 definitely supports them in most operations (selects, updates, inserts, etc). It's possible that it doesn't like them in a load, but highly unlikely. I reckon they should work fine.
well, I tried the query with the @dummy fields and got a syntax error. When I took them out the same query worked fine.
I also didn't find any reference to @dummy in MySQL 4 manual. Am I wrong in thinking that?
@dummy is an SQL variable. MySQL 4 definitely supports them in most operations (selects, updates, inserts, etc). It's possible that it doesn't like them in a load, but highly unlikely. I reckon they should work fine.
Just tried it again. still no joy.
If you are right then I'm doing something terribly wrong here.
I ran the exact same query as GM suggested. Didn't work with @dummy but worked without.
I've had a look in the MySQL 4 manual for LOAD DATA INFILE, and it seems to be quite scarse compared to the MySQL 5 manual. So I can't say with 100% certainty that MySQL 4 does support assigning the input to variables. Unfortunately, I'm without a MySQL 4 environment.
Do you have the option to clean the file up in some other way? (At it's source would be best, if possible).
Could you, for example, parse it into a PHP script and clean it there, and then send it to the database? You'd still be doing it on the fly, but it'd be a little bit of extra work.
I've had a look in the MySQL 4 manual for LOAD DATA INFILE, and it seems to be quite scarse compared to the MySQL 5 manual. So I can't say with 100% certainty that MySQL 4 does support assigning the input to variables. Unfortunately, I'm without a MySQL 4 environment.
Do you have the option to clean the file up in some other way? (At it's source would be best, if possible).
Could you, for example, parse it into a PHP script and clean it there, and then send it to the database? You'd still be doing it on the fly, but it'd be a little bit of extra work.
This is possible but it won't be a great idea as I'm dealing with very large files here (~2G).
I have the possiblity to upgrade to MySQL 5. Will this solve both my problems?
I can't make any guarantees, but for me the query I posted seemed to work in MySQL 5.0.2 (albeit with a single line of input).
Upgrading may affect other databases you've got, so proceed with caution if you choose this route.
Maybe someone else out there will be good enough to run a couple of tests in a MySQL 4 environment? As onion2k says, I find it strange that it isn't supported.
What is the exact error that you get when you try to run the query I posted?