Stack Overflow
Stack Overflow

Questions tagged [load-data-infile]

A mySQL function for loading external data directly into the database.

LOAD DATA INFILE is a mySQL function for loading external data directly into the database.

More information available from the mySQL manual: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

642 questions
3
votes
2 answers

load data infile, comment character

I have a data file where some rows are comment lines starting with %. How to load data from such a file into a mysql table?
suresh
  • 1,109
  • 1
  • 8
  • 24
3
votes
2 answers

Error Code: 1265. Data truncated for column at row 1 when I tried to input csv file to MySQL

I created a table with a query: CREATE TABLE Department ( EmpID INT NOT NULL, Designation VARCHAR(80) NOT NULL, Department VARCHAR(80) NOT NULL, PRIMARY KEY(EmpID)); SELECT * FROM Department; I enter data with a query: LOAD DATA INFILE…
Anna
  • 41
  • 1
  • 3
3
votes
1 answer

How can I escape NULLs on mysql in batch mode?

In order to dump and reload some data, I use the mysql client with a select query (-e 'SELECT ...') in batch mode (to be more specific, is very silent mode, '-ss') directing the intermediate result to the stdout. In turn, the result is then…
Marcus
  • 5,104
  • 2
  • 28
  • 24
3
votes
1 answer

MySQL error " 'WITH' is not valid at this position for this server version expecting ALTER, ANALYZE, ...."

I know this is a known issue with older versions of MySQL Workbench but I'm certain I have the latest version so that is definitely not the cause. The code is the following, I will also explain why I am using this approach. WITH temp as ( LOAD DATE…
Jesus Rodriguez
  • 137
  • 7
3
votes
2 answers

Is there a work-around that allows missing data to equal NULL for LOAD DATA INFILE in MySQL?

I have a lot of large csv files with NULL values stored as ,, (i.e., no entry). Using LOAD DATA INFILE makes these NULL values into zeros, even if I create the table with a string like var DOUBLE DEFAULT NULL. After a lot of searching I found that…
Richard Herron
  • 9,760
  • 12
  • 69
  • 116
3
votes
1 answer

Cloud SQL: Python Client API defaults to utf8mb3 character set on CSV file import

I am using Google Cloud's import API to load CSV file in Google Cloud Storage to a Cloud SQL MySQL 5.7 database: https://cloud.google.com/sql/docs/mysql/admin-api/v1beta4/instances/import The import API generates a LOAD DATA INFILE command to MySQL…
user8754646
3
votes
0 answers

Importing geometry data into MySQL using LOAD DATA LOCAL INFILE

I am trying to import CSV data into MySQL using the LOAD DATA LOCAL INFILE syntax. This is normally a fairly simple task, but in this case the data includes a geometry field that is tripping me up. When I try to run the import, I'm getting errors…
Spudley
  • 166,037
  • 39
  • 233
  • 307
3
votes
1 answer

How to disable insertion of double quotes when loading from local infile into table in mysql?

While importing a tab-separated-values (TSV) file, I find that there are two cases where mysql injects double quotes that I would like to disable. Case 1: When the field value is empty/blank (zero characters) - instead of a NULL, a "" gets inserted.…
Streamline
  • 2,040
  • 4
  • 37
  • 56
3
votes
1 answer

How to import xml files with mysql LOAD XML LOCAL INFILE

i need help with importing xml file to a table , xml file is having millions of records . first i was using simplexml_load_file and then put a loop to insert record one by one by that is taking a lot of time . i have used the infile for csv and that…
phpdev
  • 89
  • 2
  • 10
3
votes
1 answer

Mysql 'LOAD DATA LOCAL INFILE' specify column's value depending on file

I am stuck in a situation where I have to read data from 3 separate large CSV files and store it in MySQL database. csv file columns: total_rev,monthly_rev,day_rev Database table's columns: total_rev, monthly_rev, day_rev, from I am able to…
pro_newbie
  • 336
  • 2
  • 6
  • 19
3
votes
2 answers

What is best practice to store 50000+ records in mysql in a single transaction

Input set: thousands(>10000) of csv files, each containing >50000 entries. output: Store those data in mysql db. Approach taken: Read each file and store the data into database. Below is the code snippet for the same. Please suggest if this approach…
basu
  • 87
  • 1
  • 2
  • 10
3
votes
1 answer

Load data local infile using http:// location broken

Background: I moved some websites from a shared hosting account to a VPS. Everything works extremely smooth with the only exception of lines of php code which execute queries with LOAD DATA LOCAL INFILE, using data sources from external servers…
Andreas
  • 31
  • 4
3
votes
1 answer

Updating MySQL table with only unique records?

I am googling around and reading posts on this site but not clear what I should I do go with insert unique, records. I basically have a giant file that that has a single column of data in it that needs to be imported into a db table where several…
lcm
  • 1,737
  • 6
  • 17
  • 40
3
votes
1 answer

Importing Data from CSV file into MySQL, Scientific Notation

I am using a LOAD DATA INFILE to read from a CSV and insert into a MySQL table. The problem is that one column in the CSV is in scientific notation and when it is read and loaded into the table it won't go into a column of type DOUBLE, FLOAT, or…
Ben Howells
  • 53
  • 6
3
votes
1 answer

how to secure load data local infile update query against sql injection

In my app, I need to offer the possibility of uploading csv and excel files which are then used to update the database. The files contain +/- a few million rows, so I need to use load local data infile: $stmt1 = $dbh->prepare("CREATE TEMPORARY TABLE…
baao
  • 71,625
  • 17
  • 143
  • 203
1 2 3
…
42 43