Questions tagged [load-data-infile]

1

votes
0

answer
86

Views

Spring, JPA, Mysql LOAD DATA INFILE could not serialize

I'm using JPA to load info from a CSV to MySQL, here the query: @Query(value = 'LOAD DATA LOCAL INFILE :file INTO TABLE info FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' (d_create, folder, work, status, country, component, type)', nativeQuery = true) void upldInf(@Param('file')MultipartFile fi...
Fernando Aguilar
1

votes
0

answer
26

Views

LOAD DATA LOCAL INFILE fails only on the first record's key (auto_increment) — bug?

Here's the content of a very simple text file to show my problem. Somehow, I end up using '|' as the separator. But that's not the problem... 10|Antonio 11|Carolina 12|Diana 13|Alejandro Here is the code I use to create that very simple table and to load the file into it. CREATE TABLE IF NOT EXISTS...
Antonio
1

votes
1

answer
31

Views

how to import data into spcial character column name using LOAD DATA INFILE

under following sample im trying to import CSV data into MySQL table, LOAD DATA INFILE 'file1.csv' INTO TABLE loc_chg_log FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS (@col1,@col2,@col3,@col4,@col5) set [email protected],[email protected],[email protected],[email protected],[email protected]; MySQL ta...
Sadzone
1

votes
0

answer
28

Views

load data in file, on duplicate of primary key update columns from csv file

I have a table name list_subscriber, I also have few records with there primary keys available(all records are in csv file and it is considered that these are updated records), so in case if there is any duplicate found based on primary key in csv file, then that particular records should go for upd...
Abhishek
1

votes
1

answer
112

Views

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 database that assumes 'utf8' character set. However...
Osvaldo Banuelos
1

votes
1

answer
36

Views

MySQL Format Date with a Load Data command

Hello I am trying to load data from a csv file but need to format the date when loading the file. The date format of my csv file is: 1/1/2008 0:00 i'd like to format the date and to just the year with the load command load data infile 'C:/mysql/ca_pop_educational_attainment.csv' into table ca_pop...
bryan
1

votes
2

answer
44

Views

How to format mytext file?

I have to load set of questions to MYSQL database table.But the table format is like below QNO Questions Option a Option b Option c Option d Rightanswer... but my textfile which i need to load in database using LOADINFILE command is in different format. How can i match my textfile format to my table...
giri
1

votes
1

answer
1.7k

Views

How do I get SQL LOAD DATA from CSV to honour the column type when loading?

I am loading a CSV file into MySQL (5.1) using CREATE TABLE and LOAD DATA. I have a number of columns which are textual types (categories) but contain numerical values. When I create the table I assign these columns as VARCHAR(254) but the JDBC driver throws a SQLException 'Data truncated for colu...
Simon
1

votes
1

answer
110

Views

Insert rows of states and iso's from comma separated textfile into webbased Mysql

I'm building an international webshop and in the part where a customer has to fill in the address I wonder how I insert the rows in a comma separated text file with a list of stats in my web based MySQL? Example AM,04,'Geghark'unik'' AM,05,'Kotayk'' AM,06,'Lorri' AM,07,'Shirak' AM,08,'Syunik'' AM,09...
Janis
1

votes
5

answer
12k

Views

mysql “datetime NOT NULL DEFAULT '1970-01-01' ” gets turned to 0000-00-00 00:00:00

I have a column created as `date_start` datetime NOT NULL DEFAULT '1970-01-01' However when I upload data from a CSV file with the LOAD DATA command with a blank entry for date_start the value saved is 0000-00-00 00:00:00 ?
veccy
1

votes
2

answer
11.5k

Views

MySQL: Loading multiple files into a table

I've been trying to load multiple files into a table, so that they would fit the same row. I can insert them separately, but then the issue lies within the NULL values, and I plan to JOIN this table. If that happens, I get too many NULL values -- useless data. LOAD DATA LOCAL INFILE 'malefirst.txt...
Michael
1

votes
1

answer
227

Views

in mysql database,how to upload the modified data from csv file

Initially, I created a database called 'sample' and updated the data from massive size CSV file. Whenever I have small changes in .csv file (some data are added/deleted/modified), I have to update this in database too. Always updating the entire .csv file (large) is not efficient. Is there any eff...
WithIssue
1

votes
1

answer
100

Views

define PK before or after LOAD DATA INFILE

I neet to load >1 billion rows into empty MyISAM table. I'm sure all entries in the file are unique. Is it better to load the data into table with PK defined, or add PK later? I have got 8GB RAM and SAS disc workstation with 5GB RAM limit to store indexes. There is noticeable slow down (from ~200k e...
Leszek
1

votes
1

answer
2.9k

Views

MySQL LOAD XML LOCAL INFILE

I'm building an application on my local machine, and for the longest time, was using Windows 7 with MySQL installed, but as I've now moved onto Linux, I'm trying to execute this statement: LOAD XML LOCAL INFILE 'http://example.com/tech.xml' INTO TABLE t ROWS IDENTIFIED BY ''; This is not working, an...
Ryan
1

votes
1

answer
1.9k

Views

mysql trigger used with load data infile

I have been having a hard time figuring out how to make 'table2' update its values based on the values in 'table1' whenever new rows are inserted in 'table1'. Depending on what is being inserted into table1, the trigger may need to insert new rows in table 2, or just update values in existing rows i...
Riggster
1

votes
2

answer
3.1k

Views

LOAD DATA LOCAL INFILE importing only one row in MySQL

Using MySQL Database, I want to import a csv file with 13 columns and 120 rows. I am trying to upload them into a table. Here is my command: mysql> load data local infile 'restaurants.csv' into table restaurants_restaurant fields terminated by ',' enclosed by ''' lines terminated by '\n';...
user1786284
1

votes
2

answer
1.6k

Views

LOAD DATA LOCAL INFILE loading one record only

In MySQL I use LOAD DATA LOCAL INFILE like this: LOAD DATA LOCAL INFILE 'code.csv' INTO TABLE MyDB.code FIELDS TERMINATED BY ',' ENCLOSED BY ''' LINES TERMINATED BY '\r\n' (code_id,code_name,code_date); I get a response like this: Query OK, 1 row affected, 2 warnings (1.80 sec) Records: 665893 De...
Alex
1

votes
2

answer
1.6k

Views

“load data infile” with many files

I have to load and append data to a MySQL table from many files. The files are stored in the same directory. To load data from a single file I use: load data local infile 'D:\\MyDir\\file.DAT' into table catasto.dm_import fields terminated by '|' lines terminated by '\n'; Now I need to append every...
user2867049
1

votes
1

answer
90

Views

Loading data mysql

I'm trying to Load Data that look like: 2WIRE;3COM;3M;3WARE And each of the companies I want to be in new row in table which contains columns: c_id (auto increment, not in file) and c_name
user3198221
1

votes
1

answer
777

Views

load data infile syntax in mysql

I am using LOAD DATA local INFILE command to upload big csv file in mysql db. The field is an integer in the mysql table and so it will accept only integer. Now if the csv file contains numbers like below then the sql doesn't work.The format of my csv file is ID '322' '445' '211' So it is inserting...
user3671491
1

votes
1

answer
1.2k

Views

Import huge data set csv in MYSQL

I'm trying to import a huge data set from a csv file ~400MB with 900000 rows. This file has the information of two relational tables. For example: ['primary_key','name','lastname','phone,'work_id','work_name'] Every row i have to check if the primary key exists for insert or updated if needed, also...
jopereyal
1

votes
1

answer
922

Views

LOAD DATA LOCAL INFILE inserting blank record

Trying to import text file using below command LOAD DATA LOCAL INFILE '/var/www/colleges.txt' INTO TABLE selections LINES TERMINATED BY '\n' SET unit=null, created=NOW(), type='college'; Table Structure id INT Autoincremented name Varchar type Varchar unit Varchar crated datetime But the above c...
neeraj
1

votes
2

answer
1.3k

Views

Importing a very large tab delimited file into MySQL WITHOUT first creating a table

I have a very large tab delimited file. It's about 500 columns wide, and perhaps a million records long. It's a mix of text, dates and numbers. I'm actually only interested in a few columns from inside that - the rest will get deleted. I want to import it into MySQL, but it's a one-off (perhaps 2- o...
StackG
1

votes
3

answer
573

Views

load data infile order by line

I :) I need to insert 13500 lines and +500 columns from csv. So, I use load data infile and it's work. But, I need exactly the same order in my MySQL database and my Csv. Actually, for example, the 1000 line of the csv can be at the 800 place in my base I need something like 'Order by column1' but...
Xenofexs
1

votes
2

answer
2.8k

Views

Import csv file with less columns than the table to which I'm importing?

I have exported a csv file with four columns in phpMyAdmin whith these options: Columns separated with: , Columns enclosed with: ' Columns escaped with: ' Lines terminated with: AUTO Now I want to import this file into a table with 17 columns!? So only four columns will be populated and the...
Jeton Thaçi
1

votes
1

answer
221

Views

MySQL Get result of loading data from file

I create a procedure to load data from file into table and return the result. Here's my procedure CREATE PROCEDURE `ImportData1Table`(in FilePath text, in TableName text, in DataType text) BEGIN LOAD DATA INFILE FilePath INTO TABLE TableName FIELDS TERMINATED BY '\t'; END $$ How can I get the query...
Quan Nguyen
1

votes
1

answer
583

Views

Column Name requirement while importing csv into mysql workbench

I have this following code to import selected columns into mysql workbench. SET autocommit=0; LOAD DATA LOCAL INFILE '150926.csv' INTO TABLE innodb.fh FIELDS TERMINATED BY ',' ENCLOSED BY ''' LINES TERMINATED BY '\n' ignore 1 lines (@dummy,date,@dummy,@dummy,@dummy,**CS-URI-STEM**,@dummy,@dummy,@du...
yingnan liu
1

votes
1

answer
173

Views

How to ignore exponential notification and get the actual value while importing data from excel into mysql

I have a huge data in Excel. I need to import it into MySQL. I've saved my Excel file as CSV. There are several columns in my table which are having contact numbers. I've sat format for such fields to 'Number' with decimal places zero. When I saved it as CSV, its showing such values of such fields w...
iks_in
1

votes
1

answer
660

Views

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 insert data in my table by using following query: LOAD DATA...
pro_newbie
1

votes
1

answer
821

Views

LOAD DATA INFILE: Invalid ut8mb4 character string

I'm using LOAD DATA INFILE to import some big tables (iTunes EPF). However, the import fails with this error: string(52) 'Invalid utf8mb4 character string: 'אל נא תלך'' The table is created like this: CREATE TABLE `song-tmp` ( `song_id` int(11) NOT NULL DEFAULT '0', `name` varchar(1000) DEFAU...
guidev
1

votes
1

answer
83

Views

MySQL INSERT… SELECT Large Dataset of 420 Million Records

I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. I need this temporary table to stage the data because I do some cleaning on it before loading it into it's final d...
Adam
1

votes
2

answer
60

Views

load data query is not working with INSERT/REPLACE option

I am trying to upload data from CSV file into a mysql database table but i am getting error 'java.sql.SQLException: Invalid utf8 character string: '' ' when using INSERT/REPLACE option in load data query. but the same query works fine without INSERT/REPLACE option. Query: Statement stmt = conn.crea...
pmgowda
1

votes
1

answer
0

Views

MySQL : error with --secure- file-priv while using USE LOAD DATA INFILE from R

Im running code in MYSQL works fine no problem. But when i try to run the same code in R I get the error: could not run statement: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement I googled a bit and found alot of people having trouble with running...
A.Salehi
1

votes
1

answer
0

Views

Error in mysql while importing from csv

This is my command line query. mysql> load data local infile 'c:\\re\\30-11-08.csv' into table powerdata(Date, DG1, DG2, DG3, Dg4, DG5, ChillerPanel1, ChillerPanel2, ChillerPanel3, ChillerPanel4,1st_Floor, 2nd_Floor, 3rd_Floor, 4th_Floor, UPS1, UPS2, UPS3, UPS4, UPS5,Server_Power, Cooling_Power)...
1

votes
3

answer
0

Views

Most efficient way to import data from one MySQL database to another

I'm writing a PHP script which imports data from tables in two different databases into another one. I've got it working ok with sample data, except now I've moved to using data closer resembling its final use: 25+ million records per table, and growing daily. Obviously, efficiency is a bit of a con...
nickf
1

votes
1

answer
0

Views

Blank row in table after LOAD DATA INFILE

I'm uploading a large amount of data from a CSV (I'm doing it via MySQL Workbench): LOAD DATA INFILE 'C:/development/mydoc.csv' INTO TABLE mydatabase.mytable CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY '\r'; However, I'm noticing that it keeps adding an...
Tom
1

votes
2

answer
0

Views

mySql: reasonable limitations on bulk inserts

I am receiving a large amount of CSV data from a client that I plan to insert into a mySql database and process the data within my application. There are about 10 tables involved. The rows are less than 1k bytes each. What limits should I obey in individual insertions? I have quite a few millions of...
Jim
1

votes
3

answer
0

Views

Date problem when importing from file into MySQL

I have a table with payDate DATETIME in which I'm inserting using load data local infile 'file.txt' into table tableName; dates like 26/04/2012 00:00:00. This gives warnings like Warning | 1265 | Data truncated for column 'payDate' at row 1 and the date in the table is 0000-00-00 00:00:00. Is there...
Michael
1

votes
1

answer
0

Views

Problem with Python and MySQL

I am trying to import a large text file into a MySQL database. The SQL statement is as follows: LOAD DATA INFILE '/tmp/epf/full/album_popularity_per_genre' INTO TABLE album_popularity_per_genre CHARACTER SET UTF8 FIELDS TERMINATED BY X'01' LINES TERMINATED BY '\n' IGNORE 45 LINES (export_date, s...
David
1

votes
2

answer
0

Views

Why do I get an error when using LOAD DATA INFILE?

I'm trying to use SQL to upload a csv file from my laptop to a database, however I get a strange error. The code I am typing into the SQL window in phpMyAdmin is as follows: LOAD DATA INFILE '/Users/myMac/testServerUpload.csv' INTO TABLE `testDatabase` FIELDS TERMINATED BY ',' LINES TERMINATED BY...
djq

View additional questions