Ran into the same issue as root and threw me for a moment could be an issue with your server settings set with compile to test login to console with the same user and try your load data command if you get the same error, try closing console and running mysql -u USER -p –local-infile=1 … Read more
If you’d do echo($sql); before you execute it you’d see that syntax of your query is incorrect for following reasons: Filename should be enclosed in quotes rather than backticks because it’s a string literal not an identifier. There is absolutely no need to call mysql_escape_string() to specify a delimiter in FIELDS TERMINATED BY and ENCLOSED … Read more
You could use SQL for this: $file=”backups/mytable.sql”; $result = mysql_query(“SELECT * INTO OUTFILE ‘$file’ FROM `##table##`”); Then just point a browser or FTP client at the directory/file (backups/mytable.sql). This is also a nice way to do incremental backups, given the filename a timestamp for example. To get it back in to your DataBase from that … Read more
if you’re using innodb and bulk loading here are a few tips: sort your csv file into the primary key order of the target table : remember innodb uses clustered primary keys so it will load faster if it’s sorted ! typical load data infile i use: truncate <table>; set autocommit = 0; load data … Read more
From Mysql docs: You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column: LOAD DATA INFILE ‘file.txt’ INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
Try this command load data local infile ‘file.csv’ into table table fields terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘\n’ (column1, column2, column3,…) The fields here are the actual table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have … Read more
These steps can be used to emulate this functionality: Create a new temporary table. CREATE TEMPORARY TABLE temporary_table LIKE target_table; Optionally, drop all indices from the temporary table to speed things up. SHOW INDEX FROM temporary_table; DROP INDEX `PRIMARY` ON temporary_table; DROP INDEX `some_other_index` ON temporary_table; Load the CSV into the temporary table LOAD DATA … Read more
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax. To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty … Read more
Use mysqlimport to load a table into the database: mysqlimport –ignore-lines=1 \ –fields-terminated-by=, \ –local -u root \ -p Database \ TableName.csv I found it at http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/ To make the delimiter a tab, use –fields-terminated-by=’\t’
This will do what you want. It reads the fourth field into a local variable, and then sets the actual field value to NULL, if the local variable ends up containing an empty string: LOAD DATA INFILE ‘/tmp/testdata.txt’ INTO TABLE moo FIELDS TERMINATED BY “,” LINES TERMINATED BY “\n” (one, two, three, @vfour, five) SET … Read more