CODEWITHSUNDEEP
mysql
Swapnil Ganjegaonkar
Swapnil Ganjegaonkar

Reputation: 116

Insert text file content to column in mysql

I want to insert text file data to my table column. For that i am using following code.

INSERT INTO uncompress 
(stamp, what) VALUES 
(NOW(), LOAD_FILE('D:/test.txt'));

But it inserts null data in 'what' column. Please help me. Thanks in advance

Upvotes: 1

Views: 768

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

you are looking for load data infile

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

example:

CREATE TABLE `mytable` (
    `Account` VARCHAR(255) NOT NULL,
    `Month` DATE NOT NULL,
    `Device` VARCHAR(255) NOT NULL,
    `Campaign` VARCHAR(128) NOT NULL,
    `Keyword` VARCHAR(255) NOT NULL,
    `Customer_ID` VARCHAR(255) NOT NULL,
    `Keyword_state` VARCHAR(255) NOT NULL,
    `Match type` VARCHAR(255) NOT NULL,
    `Max_CPC` DECIMAL(10,5) NOT NULL,
    `Clicks` INT(11) NOT NULL,
    `Impressions` INT(11) NOT NULL,
    `CTR` VARCHAR(255) NOT NULL,
    `avg_CPC` DECIMAL(10,5) NOT NULL,
    `avg_CPM` VARCHAR(255) NOT NULL,
    `Cost` VARCHAR(255) NOT NULL,
    `avg_Pos` DECIMAL(10,5) NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;



LOAD DATA LOW_PRIORITY LOCAL INFILE 
'C:\\Users\\Adr\\Desktop\\Keyword performance report.csv' 
REPLACE INTO TABLE `mydatabase`.`mytable` 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
(`Account`, `Month`, `Device`, `Campaign`, `Keyword`, `Customer_ID`, `Keyword_state`, `Match type`, `Max_CPC`, `Clicks`, `Impressions`, `CTR`, `avg_CPC`, `avg_CPM`, `Cost`, `avg_Pos`);

/* 133,999 rows imported in 5.078 seconds. */

Upvotes: 0

Nadeem_MK
Nadeem_MK

Reputation: 7689

MySQL LOAD_FILE() reads the file and returns the file contents as a string.

To use this function, the file must be located on the host server, user must specify the full path name of the file, and user must have the FILE privilege. The file must be readable and size must be less than max_allowed_packet (set in the my.ini file) bytes.

It returns NULL if the file does not exist or can’t be read.

Upvotes: 1

Related Questions