php - Load Data Local Infile with Hash and Date Using Set

Question

I am at wits end on this. I have researched this to death and cannot seem to resolve. I have the following PHP statement:

    $sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE contacts FIELDS 
TERMINATED BY ',' 
LINES TERMINATED BY '
' 
IGNORE 1 LINES
(@ignore, @ID, @Name, @Email, @Type, @Hash, @Time)
SET Date_Update=date('Y-m-d')
";

My CSV file includes the following header row: ID, User Name, User Email, Type.

ID      User Name   User Email  Type
12345   Jackson, F  [email protected]   Level 1
67890   Stewart, J  [email protected]   Level 1
43210   Fuller, T   [email protected]   Level 2
62295   Lewis, M    [email protected]   Level 2

The CSV file also has extra fields which I do not need to import. Was hoping I could select which fields to import. Not sure of the technique.

My MYSQL table has the following fields: Tag (auto-increment), ID, Name, Email, Type, Hash, Date_Update, Time_Update.

I can't seem to figure out how to use the SET feature in this command. I set @ignore to skip the Tag (auto-increment field).

• How do I get the CSV "User Name" field into the MYSQL "Name" field?
• How do I generate today's date for the MYQL "Date_Update" field? I tried SET Date_Update=date('Y-m-d') but it shows up blank.
• How do I generate a Hash from the CSV "ID" field? I tried Hash=Hash('sha384',$pre.@ID.$suf)

I'm at a loss. Any help would be appreciated.

Answer 1

This banks on the assumption of a Tab '' as the column delimiter.

Modify your Hash as you will. I just put the sha2() in as a placeholder for now.

It took a while to figure this out. I had to go into 'Edit' of your question to assume there were tabs. And finally found a peer Answer over Here by user peixe.

You never supplied a schema, so I flew with this one. Add your Time column if you want. But I collapsed the date and time into one column with now() into a datetime.

Schema:

drop table if exists pumpkin001;
create table pumpkin001
(   Tag int auto_increment primary key,
    ID varchar(10) not null,
    Name varchar(100) not null,
    Email varchar(100) not null,
    Type varchar(100) not null,
    Hash varchar(1000) not null,
    Date_Update datetime not null
);

Load (Windows):

LOAD DATA LOCAL INFILE 'c:\nate\file007.txt'
into table pumpkin001
COLUMNS TERMINATED BY ''
LINES TERMINATED BY '
' 
IGNORE 1 LINES
(@ID, @Name, @Email, @Type)
SET ID=@ID, Name=@Name, Email=@Email, Type=@Type,
Hash=sha2(@ID,384), Date_Update=now();

Load (Linux):

LOAD DATA LOCAL INFILE 'file007.txt'
into table pumpkin001
COLUMNS TERMINATED BY ''
LINES TERMINATED BY '
' 
IGNORE 1 LINES
(@ID, @Name, @Email, @Type)
SET ID=@ID, Name=@Name, Email=@Email, Type=@Type,
Hash=sha2(@ID,384), Date_Update=now();

Results:

select Tag,ID,Name,Email,Type,left(Hash,50) as L50_Hash,Date_Update from pumpkin001;
+-----+-------+------------+-----------+---------+----------------------------------------------------+---------------------+
| Tag | ID    | Name       | Email     | Type    | L50_Hash                                           | Date_Update         |
+-----+-------+------------+-----------+---------+----------------------------------------------------+---------------------+
|   1 | 12345 | Jackson, F | [email protected] | Level 1 | 0fa76955abfa9dafd83facca8343a92aa09497f98101086611 | 2016-07-14 18:21:40 |
|   2 | 67890 | Stewart, J | [email protected] | Level 1 | 6988c291a83b05760b93263fc78e8feeca8ca4641b007c6978 | 2016-07-14 18:21:40 |
|   3 | 43210 | Fuller, T  | [email protected] | Level 2 | 6d07aa9758595e1dfe5dca93acc46dea01fef0856fe7dadf04 | 2016-07-14 18:21:40 |
|   4 | 62295 | Lewis, M   | [email protected] | Level 2 | f3d4154869ef03ff09ea778b5066bd909c3ce5baf894e0593b | 2016-07-14 18:21:40 |
+-----+-------+------------+-----------+---------+----------------------------------------------------+---------------------+