MySQL Forums :: General :: Is it possible to use variables and string functions with LOAD DATA LOCAL INFILE?


Advanced Search

Is it possible to use variables and string functions with LOAD DATA LOCAL INFILE?
Posted by: Geoffrey Hoffman ()
Date: September 05, 2012 08:38PM

I have a lot of data in a Tab delimited text file exported from another database platform and I need to import it into a new MySQL database.

Say the text file has this format:

23 Jim_Jameson 20120529
24 Fred_Frederick 20120530
25 Bill_Bilson 20120602

I can easily make the table like so:

CREATE TABLE table1 (
id INT PRIMARY KEY,
fullname VARCHAR(100),
joined CHAR(8)
);

...then import these strings to table1 Using MySQLImport or LOAD DATA LOCAL INFILE no problem.

Now then secondarily, I'll need to transform this bulk load to another table that has the proper MySQL data types I actually want:

CREATE TABLE table2 (
id INT PRIMARY KEY,
firstname VARCHAR(50)
lastname VARCHAR(50)
joined DATETIME
);


...by using CAST, SUBSTR, CONCAT, etc., in a second pass or process Selecting the data in table1 and transforming it to table2.

But isn't there a way to use CAST, SUBSTR, LEFT, CONCAT... or @variables on the columnar data, and import it directly into table2 how I want in a single pass?

My actual data to process is a little more complicated than the above, but the basic idea is there. I have a lot of data to import -- hundreds of gigs of tab delimited files, so it has to be processed in batches.

My development language is PHP 5.3+. I was planning on running PHP from the command line, or maybe bash.

Any examples or links would be very appreciated.

Options: ReplyQuote


Subject Written By Posted
Is it possible to use variables and string functions with LOAD DATA LOCAL INFILE? Geoffrey Hoffman 09/05/2012 08:38PM
Re: Is it possible to use variables and string functions with LOAD DATA LOCAL INFILE? Geoffrey Hoffman 09/06/2012 01:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.