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
11
votes
1 answer

How to use LOAD DATA INFILE statement when file is another location?

I want to use LOAD DATA INFILE statement to import data in my table. If the file is available on the same system, it works nicely and imports successfully, but when the file is located on another system, it cannot locate the path. Can anyone…
Sameek Mishra
  • 9,174
  • 31
  • 92
  • 118
11
votes
5 answers

Error 1148 MySQL The used command is not allowed with this MySQL version

I am using MySQL LOAD DATA LOCAL INFILE command and I get this error: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE '/tmp/phpI0ox54' INTO TABLE…
Chris Muench
  • 17,444
  • 70
  • 209
  • 362
10
votes
3 answers

How to see progress of .csv upload in MySQL

I have a very large .csv file, and I'm loading it into mysql with the LOAD DATA INFILE command. Because it takes so long, I'd like to see how far along the upload has progressed. I've tried 2 methods so far- First I simply did a SELECT COUNT(*)…
Tucker
  • 7,017
  • 9
  • 37
  • 55
10
votes
3 answers

LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version

I have a PHP script that calls MySQL's LOAD DATA INFILE to load data from CSV files. However, on production server, I ended up with the following error: Access denied for user ... (using password: yes) As a quick workaround, I changed the command…
Salman A
  • 262,204
  • 82
  • 430
  • 521
8
votes
6 answers

What is the best way to achieve speedy inserts of large amounts of data in MySQL?

I have written a program in C to parse large XML files and then create files with insert statements. Some other process would ingest the files into a MySQL database. This data will serve as a indexing service so that users can find documents…
Chris Kloberdanz
  • 4,436
  • 4
  • 30
  • 31
8
votes
2 answers

infile .csv file in sas with header

I have a csv file which named dataset1.csv and it contains header with 3 variables att1 (character), att2 and att3 (numeric data). I tried following code filename test 'C:\Users\1502911\Desktop\Practice\SAS\Dataset'; data dataset1; infile…
useR
  • 3,062
  • 10
  • 51
  • 66
8
votes
1 answer

Importing bulk CSV data in UTF-8 into MySQL

I'm trying to import about 10K rows of UTF-8 encoded data into a new MySQL table. I can do so successfully with LOAD DATA INFILE via MySQL Workbench but it the UTF-8 characters get mangled. I've tested the database otherwise via PHP and it accepts…
Tom
  • 30,090
  • 27
  • 90
  • 124
8
votes
5 answers

Upload 95 GB CSV file into MySQL MyISAM table via Load data infile: CSV engine an alternative?

I'm trying to upload a 95 GB CSV file into a MySQL database (MySQL 5.1.36) via the following command: CREATE TABLE MOD13Q1 ( rid INT UNSIGNED NOT NULL AUTO_INCREMENT, gid MEDIUMINT(6) UNSIGNED NOT NULL , yr SMALLINT(4) UNSIGNED NOT NULL , dyyr…
Janvb
  • 1,290
  • 2
  • 16
  • 17
8
votes
4 answers

MySql bulk load command line tool

Does MySql have a bulk load command line tool like bcp for SQLServer and sqlldr for Oracle? I know there's a SQL command LOAD INFILE or similar but I sometimes need to bulk load a file that is on a different box to the MySQL database.
Mike Q
  • 22,839
  • 20
  • 87
  • 129
8
votes
7 answers

MySQL Convert latin1 data to UTF8

I imported some data using LOAD DATA INFILE into a MySQL Database. The table itself and the columns are using the UTF8 character set, but the default character set of the database is latin 1. Because the default character type of the database is…
Kibbee
  • 65,369
  • 27
  • 142
  • 182
8
votes
2 answers

MySQL Load Data Infile auto incremented ID value

I want to insert an ID column to my table and get data of this table from a text file. For example my text file is like that: 12 1212 4989 121 121 23 123 110 789 99 234 544 ... and it has approximately 20M rows. I…
JoshuaJeanThree
  • 1,382
  • 2
  • 22
  • 41
7
votes
3 answers

SQL Loader : Load into 2 Tables from 1 controlfile and 1 CSV

Table T1 Structure:col1 number,col2 number Table T2 Structure: col1 number,col2 number,col3 number csv file: row1:1,2,3,4,5,6 row2:1,2,3,4,5,6 I want data in Table T1 as: col1 col2 ===== ==== 1 3 1 3 I want data in Table T2 as: col1…
Mayank Kapoor
7
votes
2 answers

LOAD DATA LOCAL INFILE forbidden after php / mariadb update

I know this has been asked before but I could not find any newer posts that could be of help. LOAD DATA LOCAL INFILE has been working perfectly up until today. I have not changed anything other than ran an update for PHP and (not sure but I think)…
Asle
  • 767
  • 2
  • 8
  • 22
7
votes
1 answer

Load data infile with blob field

Given I have to dump a lot amount of inserts in a short period managed to dump all records to a file and then loading them by the load data infile sentence of mysql. This was working fine but now I compress a little more the values into a blob field…
Pabloks
  • 1,484
  • 1
  • 14
  • 15
7
votes
5 answers

How to speed up a data loading into InnoDB (LOAD DATA INFILE)?

I want to speed up a data loading. I use MySQL 5.5, InnoDB and have 1M rows of data (65Mb file). It takes 5 minutes. What mysql settings and commands affect the speed of LOAD DATA INFILE for InnoDB? Thank you.
Jean Louis
  • 1,485
  • 8
  • 18
  • 33
1 2
3
…
42 43