1

I have a CSV file that i want to enter into a table in my database which is present on a remote server. But presently I don't have access to temp folder so I cannot copy my file on that folder. Is there a way I can upload file from any random folder onto the database?

This is my command in sql server:

load data local  infile 'C:/Users/trainee_aiyer/Downloads/cash_ord.csv' into table cm_ord fields terminated by ',' enclosed by '"' lines terminated by '\n';

and my file path is this:

C:\Users\trainee_aiyer\Downloads\cash_ord.csv

I switched '\' to '/' as the prior was causing syntax errors. but I get an error saying

ERROR 2 (HY000): File 'C:/Users/trainee_aiyer/cash_ord.csv' not found (Errcode: 2 "No such file or directory")
9
  • @jkavalik when using local file should be in temp folder right ? , because i tried that and it's not working for any arbitrary path – avz2611 May 30 '16 at 7:35
  • What does "not working" mean? Do you get some specific error message or just unexpected results? What is the exact statement? (you can sanitize paths and some identifiers, but please keep as much as possible intact) – jkavalik May 30 '16 at 7:38
  • @jkavalik it says it could not find my file , but the path is exactly the same – avz2611 May 30 '16 at 7:41
  • Please be specific - exact error message may be needed (maybe even containing an error code) to find out what is the problem. You are not allowed to access the /tmp/ on your local computer? – jkavalik May 30 '16 at 7:43
  • 1
    if the file is at C:\Users\trainee_aiyer\Downloads\cash_ord.csv and the error tell you 'C:\Users\trainee_aiyer\cash_ord.csv' was not found then there is something wrong for sure, missing one directory level. – jkavalik May 30 '16 at 8:47
0

From MySQL documentation, it seems it is not possible, since it is operating systems temporary directory and not it is not configured in MySQL.

When using LOCAL with LOAD DATA, a copy of the file is created in the server's temporary directory. This is not the directory determined by the value of tmpdir or slave_load_tmpdir, but rather the operating system's temporary directory, and is not configurable in the MySQL Server. (Typically the system temporary directory is /tmp on Linux systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for the copy in this directory can cause the LOAD DATA LOCAL statement to fail.

Alternative, you may try with mysqlimport with local parameter, but make sure, you have access to that directory, else you may encounter an error. I hope this answer will help you. Thank you.

3
  • That temp file should be created by the server and the user rights should have no effect on the operation. – jkavalik May 30 '16 at 7:53
  • i had read somewhere to upload a file onto a remote server you need to have your file in the temp folder while using local , is it false ? – avz2611 May 30 '16 at 7:55
  • @avz2611 I think it is false in general, but there may be some problems with rights etc. For example Apparmor may block the mysql binaries to read any other dir for security reasons so tmp would be the best bet in most cases. For the record I can hardly believe that your user account would even work properly without /tmp/ access being allowed as lot of apps expect and use it (but I may be wrong). – jkavalik May 30 '16 at 8:07

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