Questions tagged [load-data-infile]

1

votes
0

answer
7

Views

No queries working on table after LOAD DATA INFILE

I've tried to upload a large CSV file (14.8GB) to a MySQL DB using InnoDB engine and the LOAD DATA INFILE command. The query was completed since InnoDB status shows the total number of rows were inserted: -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 1 read...
Pelayo Fernandez
1

votes
2

answer
44

Views

Mysql - Load Data Infile formatting a date

Edited: I can store the records of a file with only one line with this format. 0,2016-03-31-13:48:16.435,0.705486,1,F2221-222 But when I try to store a file with 2 or more lines i get an error. 0,2016-03-31-13:48:16.435,0.705486,1,F2221-222 1,2016-03-31-13:48:16.435,0.56606096,2,F2221-222 I am using...
Esteban S
1

votes
1

answer
53

Views

LOAD DATA LOCAL INFILE from fputcsv: data not recorded

I have below CSV file: (3 columns) 998877665544331,,baba 998877665544332,, 998877665544333,, 998877665544334,,test 998877665544335,, ,R32AS00ZZYY, 998877665544337,, 998877665544338,, 998877665544339,,asfd 998877665544330,, ,R32AS00XXYY, ,R32AS00XXZZ, I store it as file by: fputcsv($fp, array($data0,...
AnD
1

votes
1

answer
1.4k

Views

How to solve Error 1148 from MySql workbench and not cmd?

I am running the following statement to import data: LOAD DATA LOCAL INFILE 'C:\\Users\\talkohavy\\Desktop\\MySQL_data\\categories.csv' INTO TABLE BI_EX_OLTP.categories FIELDS TERMINATED BY ',' IGNORE 1 LINES; And then I get this error: error code 1148. the used command is not allowed with this...
Tal Kohavy
0

votes
0

answer
5

Views

Pentaho Data Integration bulk load into mysql

I have installed spoon 7.1. And I want to do a bulkload into mysql table. I have just created a table in mysql with 3 columns and a txt file, with 3 lines. I use the Bulkload into mysql. (below an image about the parameters). When I run it it goes well. (see the screenshot below). Besides the logs...
maria
1

votes
1

answer
9.5k

Views

PHP MySQL - Import CSV Using LOAD DATA INFILE - Determine file path?

I need some help connecting the dots on this. There's plenty of snippets floating around but I haven't found a good end to end tutorial. For a simple custom CMS I'm building I want an option for end user admins to upload a CSV of other users. PHPMyAdmin or other 'direct' access to the DB is not an...
Chaz
1

votes
2

answer
790

Views

Prevent LOAD DATA INFILE from escaping double double quotes

I have csv data like the following: 'E12 98003';1085894;'HELLA';'8GS007949261';'';1 '5 3/4'';652493;'HELLA';'9HD140976001';'';1 Some fields are included in double quotes. The problem is that as you may see in the second line the data in the first column contains a double quotation mark at the end as...
pankal
1

votes
2

answer
501

Views

LOAD DATA LOCAL INFILe: loading data form text file auto incerment syntax

I'm trying to load data from a .txt file into mysql. The table looks like this: +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+------------...
dwstein
1

votes
2

answer
154

Views

which one is fast load data infile or normal insertion?

I have a table with 40 million records, and I want to copy this 40M into 80M like this- INSERT INTO cdr (id,dataPacketDownLink, dataPacketUpLink,dataPlanEndTime,dataPlanStartTime,dataVolumeDownLink,dataVolumeUpLink, dataplan,dataplanType, createdOn, deviceName, duration, effectiveDuration, HOUR, ev...
Aamir
1

votes
1

answer
1.4k

Views

load data infile error: MySQL throws a syntax error when specifying columns

StackOverflowers! I have an issue when trying to import a .csv file into a table. As far as I know (and as I've read in the reference manual), the syntax is something like this: load data infile '/path/to/my/file.csv' into table myTable(field1, field2, field3,..., fieldk) fields terminated by ',' o...
Barranka
1

votes
1

answer
1.2k

Views

mysql LOAD DATA LOCAL INFILE with default timestamp column value

I've a table which has a column defined like this: | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+-------------------+----------------+ | created_at | timestamp | NO | | CURRENT_TIMESTAMP | | and...
alexakarpov
2

votes
1

answer
1.4k

Views

LOAD DATA LOCAL INFILE only works sometimes

I have a LOAD DATA command that works fine in the MySQL terminal. It works fine in a test PHP file served from my local server. It works fine in phpMyAdmin on the same server. It inexplicably breaks down when run from Magento, also on the same server. I have confirmed all methods are using the same...
clockworkgeek
2

votes
2

answer
1.2k

Views

How to hold MYSQL performance while large data file import using LOAD DATA INFILE?

I'm working with a large database (some tables 500000000+ rows) . Each day 3-10 millions rows should be replaced and each import is about 1 million rows (so 3-10 times a day with CRON) using LOAD DATA INFILE. The problem is, that while making such big file import, MySQL eats 80% of CPU and all comm...
kami
1

votes
1

answer
202

Views

Retrieving ID's of imported CSV (LOAD DATA INFILE)

I have the following scenario: I have to import a CSV file to a table. For this i use the LOAD DATA LOCAL INFILE. Is there a way to retrieve the id's (column 'id') of all inserted rows, since i need them in next step.
Michael Grenzer
2

votes
4

answer
7.2k

Views

Mysql: How can I use RTRIM in my LOAD DATA INFILE query?

In my code I have a query that looks like this: $load_query = 'LOAD DATA LOCAL INFILE '{$file}' INTO TABLE `{$table}` FIELDS TERMINATED BY ',' ENCLOSED BY '\'';'; Here is an example row included in the file that I am trying to load: 'MC318199','06160','1','P','00750','00000','TN598792','04/16/2009',...
jeremysawesome
3

votes
2

answer
174

Views

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 is ok or not. PreparedStatement pstmt2 = null; try...
basu
1

votes
3

answer
2.4k

Views

Load single column of CSV into MySQL

I have a csv file that is formated as follows: Rank, URL 1, www.google.com 2, www.facebook.com etc... I have a table with structure url, source, date_added Where each csv file has a different source ID. I am using the following query to load my data load data local infile 'this.csv' into table see...
6

votes
3

answer
30.3k

Views

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 col2 col3 ===== ==== ==== 1 3 5...
1

votes
2

answer
1.1k

Views

Importing CSV file to mysql table using “LOAD DATA” command

I am running a windows machine for local development and attempting to load large csv files into mysql. My code looks like this: $sql_query = 'LOAD DATA LOCAL INFILE '' . $tempLoc . '' INTO TABLE users FIELDS TERMINATED BY ' ' ENCLOSED BY '\'' LINES TERMINATED BY '\\n'...
freakydev
2

votes
2

answer
3.5k

Views

MySQL ERROR 1064 cause (LOAD DATA INFILE)

When I run this SQL commands within MySQL LOAD DATA INFILE 'myFile.csv' INTO myTable FIELDS TERMINATED BY ',' I get this error: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TO myTa...
quarkdown27
5

votes
3

answer
4k

Views

MySQL LOAD DATA INFILE “not found (Errcode: 13 - Permission denied)”

Last week this was working, and today it isn't. I have not changed the php file. All I have done in the interim is run apt-get update on my Ubuntu 15.04 server. The MySQL statement I have is: LOAD DATA INFILE '/var/www/html/uploads/TitleList.csv' INTO TABLE tblLSITitleList FIELDS TERMINATED BY ',' O...
Neill
5

votes
4

answer
28.1k

Views

MySql file import (LOAD DATA LOCAL INFILE)

I have a table called city: +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_incr...
Flukey
11

votes
7

answer
18k

Views

Program for working with large CSV Files [closed]

Are there any good programs for dealing with reading large CSV files? Some of the datafiles I deal with are in the 1 GB range. They have too many lines for Excel to even deal with. Using Access can be a little slow, as you have to actually import them into a database to work with them directly. Is...
Kibbee
11

votes
5

answer
23.7k

Views

MYSQL: Display Skipped records after LOAD DATA INFILE?

In MySQL I've used LOAD DATA LOCAL INFILE which works fine. At the end I get a message like: Records: 460377 Deleted: 0 Skipped: 145280 Warnings: 0 How can I view the line number of the records that were skipped? SHOW warnings doesn't work: mysql> show warnings; Empty set (0.00 sec)
Nate Murray
2

votes
1

answer
465

Views

LOAD DATA INFILE - fields terminated by character which also appears in field

I have a large .csv file which I want to import into a MySQL database. I want to use the LOAD DATA INFILE statement on the basis of its speed. Fields are terminated by -|-. Lines are terminated by |--. Currently I am using the following statement: LOAD DATA LOCAL INFILE 'C:\\test.csv' INTO TABLE myt...
ByteBullet
3

votes
3

answer
10.4k

Views

Finding MySQL errors from LOAD DATA INFILE

I am running a LOAD DATA INFILE command in MySQL and one of the files is showing errors at the mysql prompt. How do I check the warnings and errors? Right now the only thing I have to go by is the fact that the prompt reports 65,535 warnings on import. mysql> use dbname; Database changed mysql> LOA...
Xeoncross
11

votes
4

answer
14.7k

Views

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 `dev_tmp` FIELDS TERMINATED BY ',' ENCLOSED BY ''...
Chris Muench
2

votes
2

answer
6.2k

Views

How can I improve LOAD DATA performance on large InnoDB tables?

I have this table with more than 7 million rows and I am LOAD DATA LOCAL INFILE'ing more data in the order of 0.5 million rows at a time into it. The first few times were fast, but this addition is taking increasingly long, probably due to indexing overhead: CREATE TABLE `orthograph_ests` ( `id` big...
mpe
2

votes
7

answer
2.4k

Views

Using LOAD DATA INFILE with arabic data

I am trying to import a .csv file into a table. I have figured out how to get the data inserted by using the following query: LOAD DATA INFILE 'examplesofdata.csv' INTO TABLE coins FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' IGNORE 1 LINES; However for several of my fields I have Arab...
Krustal
3

votes
1

answer
3.2k

Views

MYSQL LOAD DATA INFILE Syntax Error - where is it wrong?

where is the Synthax error here? LOAD DATA INFILE 'mysqlout_back.txt' INTO TABLE temp (user,category,site,tld,ip,updated,date) FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
user670186
3

votes
2

answer
4.1k

Views

conditionally ignore rows load data mysql

I am loading a large data set (60 gigs) into a database. There are some records that can be skipped because they contain values that are missing. How do I tell MySQL to skip rows that contain certain (missing in this case) values? For example, my file looks like this Value1, Value2 1,2 3,4 ,5 9,10...
Alex
4

votes
2

answer
23.1k

Views

MySQL fields terminated by tab

I am trying to upload a tab delimitted file with MySQL. I want a query something likes this: LOAD DATA LOCAL INFILE 'file' INTO TABLE tbl FIELDS TERMINATED BY 'TAB' Is there something I can subsitute for TAB to make this work?
Brian
4

votes
1

answer
1.4k

Views

Mysql select from another table in a load infile

I have a csv file that I am loading in to a mysql db using the load local infile command. The csv has a set of fields that are gathered from an enum of 5 values and I will need to query based on these. To speed up the queries (possibly up to 6 million rows) I'm trying to select on a reference table...
Michael Allen
4

votes
6

answer
12.8k

Views

MySQL cannot find data file for a load operation

I would like to load a data file into MySQL using the following command, LOAD DATA LOCAL INFILE '/Users/David/Desktop/popularity20110511/test_data' INTO TABLE test_table The above command gives me the following error #7890 - Can't find file '/Users/David/Desktop/popularity20110511/test_data'. I've a...
David
2

votes
2

answer
1.8k

Views

Load Data Infile + Disable/Enable Keys Performance

I have a table with approximately 7 million rows. Once a day, I need to bulk import around 200,000 new rows into this table. To do this, I am first disabling keys on the table, using LOAD DATA INFILE, and then re-enabling keys on the table. The problem I am having is with the ALTER TABLE my_tabl...
Steven Musumeche
3

votes
1

answer
3.2k

Views

MySQL Load Data Infile - Nulls causing error 1265: Data Truncated

I am having a problem importing data from csv into a MySQL table. I am attempting to use 'Load Data Infile' but every time I attempt to run my code I get 'Error Code: 1265. Data Truncated for column 'DIP20' at row 237' The problem stems from the fact that column DIP20 at row 237 is the first null e...
Smoggie Tom
4

votes
3

answer
3.5k

Views

Uploading CSV into MySQL table with simultaneous JOIN

What I'm trying to do is upload a CSV into a table, while appending information from a third table to the target table using JOIN. The CSV import.csv (with 1M rows) looks like this: firstname | lastname The target table 'names' looks like this: firstname | lastname | gender And the table 'gender' (w...
Brian Mayer
12

votes
8

answer
6.6k

Views

What is the fastest way to load an XML file into MySQL using C#?

Question What is the fastest way to dump a large (> 1GB) XML file into a MySQL database? Data The data in question is the StackOverflow Creative Commons Data Dump. Purpose This will be used in an offline StackOverflow viewer I am building, since I am looking to do some studying/coding in places wher...
AgentConundrum
2

votes
0

answer
55

Views

Strange behaviour of load data infile

I have the following simple CSV file which I want to load into a MySQL table: ViolationUtil1,RU FiftyFifty2013_prof1,UM Lunch_util1,RM ... It contains several rows with two fields separated by a comma. I load it using the following command: LOAD DATA LOCAL INFILE 'domains.txt' INTO TABLE domains FIE...
Erel Segal-Halevi
2

votes
2

answer
2.6k

Views

Got error message in LOAD DATA LOCAL INFILE

I've got this error: Error message is :: 'Error occured during query execution: (LOAD DATA LOCAL INFILE '/tmp/3sch.jofcial.txtgymCN5' REPLACE INTO TABLE `prod_sch` FIELDS TERMINATED BY ',' IGNORE 1 LINES (No_ ,Model_Code,Model_Name ,Lot_No_ ,Start_Seq_No_ ,Quantity ,Lot_Quantity ,Line_Code ,Line_Nam...
nunu

View additional questions