0

I am doing data load from txt file into table, most of the loads are working. Some load errors out.

  1. I want to save the output of the load into a log file to verify later.
  2. I have HTML and special characters stored in txt file and loading into table column that is been error out -ERROR 1300 (HY000): Invalid utf8mb4 character string.

Sample code inside the SQL file

LOAD DATA INFILE 'folder_Load/tbl_1.txt' INTO TABLE tbl_1 FIELDS TERMINATED BY '|' ENCLOSED BY '';

Output from CMD:

mysql> source G:/Load_Script/MYSQL_Load_Script.sql; Query OK, 24 rows affected, 222 warnings (0.03 sec) Records: 24 Deleted: 0 Skipped: 0 Warnings: 222

Query OK, 879437 rows affected, 65535 warnings (1 min 23.39 sec) Records: 879437 Deleted: 0 Skipped: 0 Warnings: 55370493

ERROR 1300 (HY000): Invalid utf8mb4 character string: 'I have performed a left knee intra-articular steroid injection u' ERROR 1300 (HY000): Invalid utf8mb4 character string: ' <input type="text" id="txtMissed" onchange="txtMe(th' Query OK, 427058 rows affected, 65535 warnings (1 min 15.00 sec) Records: 427058 Deleted: 0 Skipped: 0 Warnings: 49934240

1 Answer 1

0

Use the commandline tool from Windows's cmd:

mysql -u ... -p  <G:\Load_Script/MYSQL_Load_Script.sql  >log.txt

where script.txt is a file that contain SQL statements, including LOAD DATA. and log.txt is where you want to write the output. Other arguments should be added (like u and p) as needed. Note that backslashes are needed in cmd.

5
  • Rick James, Thank you. Is it possible to add log file in the LOAD DATA INFILE of each statement?
    – Saran
    Nov 11, 2022 at 7:41
  • @Raja_mssqlDBA - No. On the other hand, run multiple mysql calls, one per LOAD, and redirect output to different log files.
    – Rick James
    Nov 11, 2022 at 18:27
  • Thank you Rick James
    – Saran
    Nov 17, 2022 at 5:17
  • Getting this error: mysql> source G:/Load_Script/MYSQL_Load_Script_10.sql >G:/Load_Script/log.txt; ERROR: Failed to open file 'G:\Load_Script\MYSQL_Load_Script_10.sql >G:\Load_Script\log.txt', error: 22
    – Saran
    Nov 17, 2022 at 5:33
  • Put quotes around the path and use forward slash: "G:/....txt"
    – Rick James
    Nov 17, 2022 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.