Import csv file to MySQL in Windows

-
LOAD DATA INFILE "C:/Users/USER/Desktop/test.csv"
INTO TABLE test
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Change the file name accordingly.  

There may be some issues.

1. SQL Error (1290): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement



You need to add LOCAL modifier LOAD DATA statement. Otherwise the following error occurred. So it looks as follows.


LOAD DATA LOCAL INFILE "C:/Users/USER/Desktop/test.csv"
INTO TABLE test
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

2. Even if we add LOCAL modifier, there will be another issue as follows.

SQL Error (3948): Loading local data is disabled; this must be enabled on both the client and server sides.
 


3. So you need to enable following

SET @@GLOBAL.local_infile = 1;
But there may be security issues. You may check more information here 
After that you may upload the csv file to Mysql server properly.
Enjoy.....



Location: Colombo, Sri Lanka

Comments

Post a Comment

Popular posts from this blog

Laravel Sail on Windows issue - "cmdlet Invoke-WebRequest at command pipeline position 1"

-
Image
I tried to install a laravel example app using Laravel Sail. When I executed following command I got following error even I install Docker Desktop in Powershell cmdlet Invoke-WebRequest at command pipeline position 1  Supply values for the following parameters:  Uri:  Then I tried to find the issue in the laravel documentation. But it is not explained about this issue and recommended steps either. I have found following solution:   1) You need to enable WSL in docker desktop. 2) Install WSL on the computer using Windows terminal - https://apps.microsoft.com/detail/9n0dx20hk701?hl=en-US&gl=US Install WSL - https://learn.microsoft.com/en-us/windows/wsl/install 3) You may be able to open tabs in Terminal with installed linux distribution. Thank you
Read more

GIT Error: The following untracked working tree files would be overwritten by merge:

-
Recently I renamed following file names(changed only captal to simple) and added to branch(abc) and commited them. I pushed the branch as well.  app/SocialMedia/EmailShare.php --> app/SocialMedia/Emailshare.php         app/SocialMedia/EmailShareFactory.php ---> app/SocialMedia/EmailshareFactory.php After that I tried to checkout a different branch(beta). Accidently git throws following error. $ git checkout beta error: The following untracked working tree files would be overwritten by checkout:         app/SocialMedia/EmailShare.php         app/SocialMedia/EmailShareFactory.php Please move or remove them before you can switch branches. Aborting But when applying git status command no files to commit $ git status On branch abc nothing to commit, working directory clean Finally I checkout master branch. It was succefull. I think, because those files are still not in the master branch. After that I was able to checkout beta branch as well. Now that error happens again when I'm...
Read more

How to point Magento multi websites to domains

-
Image
Backend configurations Add secure and unsecure domain urls for websites Add default domain url default config Index.php changes You need edit index.php file as follows switch($_SERVER['HTTP_HOST']) {     case ' domain.co.uk ':     case 'www. domain.co.uk ':         $mageRunCode = 'base';         $mageRunType = 'website';     break;     case ' domain.eu ':     case 'www. domain.eu ':         $mageRunCode = 'euwebsite';         $mageRunType = 'website';     break; } Comment following lines if you have /* Run store or run website */ $mageRunType = isset($_SERVER['MAGE_RUN_TYPE']) ? $_SERVER['MAGE_RUN_TYPE'] : 'store';   Point main domain by adding your hosting server dns in domain registrar ...
Read more