0

I am running a query to update the table however it's not impacting any data. I am doing the below steps:-

  1. Created a temporary file in MySQL which has a column accounts. CREATE TEMPORARY TABLE tempfile (accounts varchar(20));

  2. Created a CSV file that has only four account numbers. This CSV file is converted from xlsx format and each data is in a separate row. Look exactly like this- 22070601 22070701 22070801 21752301

  3. Now I have uploaded the above CSV data file to the tempfile using the command LOAD DATA LOCAL INFILE "C:/testaccounts.csv" INTO TABLE tempfile FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

  4. I have concatenated '0' to all accounts using the query UPDATE tempfile SET accounts = concat('0', accounts) where accounts IS NOT NULL;

  5. I have run a query to see the updated data. I see all the account numbers are contacted with '0' SELECT *FROM TEMPFILE;

Output: 022070601 022070701 022070801 021752301

  1. Later I ran a query to change the status of the accounts to 'A' for all the accounts whose numbers are there in tempfile

update users set status = 'A' where account in (select account from tempfile);

Output: Query is successful but with 0 changes

  1. Later I again ran the query SELECT *FROM TEMPFILE; The output is copied to a notepad and I see \r concatenated to all the account numbers It looks like this

'022070601\r' '022070701\r' '022070801\r' '021752301\r'

I believe because of additional '\r' in account numbers, my query on point 6 couldn't find any similar accounts in tempfile to change the status to 'A';

Someone, please help me on this issue and why there is '\r' in the account numbers when I copied the output to the notpad. I need to change the status of my accounts to 'A' depending upon the account numbers in my tempfile. All the account number in tempfile need to have leading '0'.

Anjit Singha
  • 11
  • 3
  • can you provide a ceate table with inserted data to show clearly your problem. Also you get rid of any characters before inserting it – nbk Mar 30 '22 at 14:23
  • Just after uploading the data using LOAD DATA query to the temporary file. I ran the SELECT query and copied all the account numbers in the output to the Notepad++ and I see they all have again '\r' padded in the end. '22070601\r' '22070701\r' '22070801\r' '21752301\r' I believe there is some issue with my query of using LOAD DATA. I am using this query LOAD DATA LOCAL INFILE "C:/testaccounts.csv" INTO TABLE tempfile FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS Note: All my data in the sheet are in separate rows and they are not separated by ',' – Anjit Singha Mar 30 '22 at 14:34
  • change `FIELDS TERMINATED BY '\t'` to `FIELDS TERMINATED BY '\r\t'` (See also this answer: https://stackoverflow.com/a/14133740/724039 ) – Luuk Mar 30 '22 at 14:36
  • Does this answer your question? [MySQL LOAD DATA INFILE: works, but unpredictable line terminator](https://stackoverflow.com/questions/10935219/mysql-load-data-infile-works-but-unpredictable-line-terminator) – Luuk Mar 30 '22 at 14:42
  • The issue got resolved after I made all the data to be comma-separated like 12345, 6789 then run the query LOAD DATA LOCAL INFILE "C:/estaccounts.csv" INTO TABLE tempfile FIELDS TERMINATED BY ' ,' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; However, still figuring out for the query for non-comma separated (data in each different rows) – Anjit Singha Mar 30 '22 at 14:49

0 Answers0