mysql duplicates with LOAD DATA INFILE
Hi... When using LOAD DATA INFILE, is there a way to either flag a duplicate row, or dump any/all duplicates into a separate table? ...
load-data-infile
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_table ENABLE KEYS statement. It takes arou...
What is the best way to periodically load data into table
I have a database with static tables which require to be updated from CSV weekly. Tables are Mysql MyISAM and by static i mean they are used for read only (except when updated from CVS, obviously). There're about 50 tables and in total about 200mb of data to be reloaded weekly. I can think about 3 ways: Truncate table Load data from ...
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 format? ...
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 column AgeGroup at row 1". My data looks l...
Problem using MySQL LOAD INFILE
hi, I have a products table with the following structure CREATE TABLE IF NOT EXISTS `products` ( `id` int(50) NOT NULL AUTO_INCREMENT, `productname` varchar(255) NOT NULL, `description` text NOT NULL, `merchanturl` text NOT NULL, `imageurl` text NOT NULL, `price` varchar(10) NOT NULL, `original` varchar(10) NOT NULL, ...
(SQL) How to set column names of MySQL table as the delimited first line of my infile?
The first line of my infile is actually the name of the columns delimited in the file. Is there anyway for a mysql import to use those column names in the file, and create columns in the mysql table with those names so that I don't have to? ...
Importing bulk CSV data in UTF-8 into MySQL
Hi, 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 stores UTF-8 charaters fine. The problem seems to be with LOAD DATA INFILE, and I've ...
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 Arabic content which gets entered as a s...
How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
I've got a CSV file with 11 columns and I have a MySQL table with 9 columns. The CSV file looks like: col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 and the MySQL table looks like: col1, col2, col3, col4, col5, col6, col7, col8, col9 I need to map the columns 1-8 of CSV file directly to the first 8 columns of ...
How to import latitude/longitude data correctly into MySQL?
Hi, I've got a dataset of city names with their corresponding latitudes/longitudes that I've loaded into a MySQL table, like so: city_id | city_name | latitude DECIMAL(9,6) | longitude DECIMAL(9,6) Typical latitude/longitude coordinates might look like this: 54.284758 / 32.484736. However, I'm only getting values with a scale of 2 to...
LOAD DATA INFILE only 1 record inserted
I have a csv file that I'm trying to import via the command line. But only 1 row is being inserted. They are comma separated values. I'm on a Mac using Excel Mac. I save as a csv file. How can I tell if the lines are terminated by \r or \n or both? Here is the code I used: LOAD DATA LOCAL INFILE '/Users/eric/Documents/contacts_test.csv'...
How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE
I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from an CSV file into an existing database table. Here is an example SQL statement: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (name, address, dateOfBirth) The third column in the CSV that m...
different line break characters in CSVs
I use LOAD DATA LOCAL INFILE to upload csv files to MySQL. If the csv was created by a mac, I include LINES TERMINATED BY '\r' in the query. If the csv was created by a MS Office, I include LINES TERMINATED BY '\n' in the query. If the csv was created in Open Office, I omit LINES TERMINATED BY altogether. Is there a way I can formula...
MySQL load data infile - acceleration?
Hi folks, sometimes, I have to re-import data for a project, thus reading about 3.6 million rows into a MySQL table (currently InnoDB, but I am actually not really limited to this engine). "Load data infile..." has proved to be the fastest solution, however it has a tradeoff: - when importing without keys, the import itself takes about ...
How to use PHP Load Data Infile to replace the current copy of the table with the new one?
We get a new copy of data in a pipe-delimited text file from time to time. I have to refresh the table or in other words, replace the existing copy with that of the newly generated copy (output.txt). Any ideas are highly appreciated. Thank you.. TRUNCATE Table elements; LOAD DATA INFILE '/data/out.txt' IGNORE INTO TABLE elements FIELD...
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? ...
mysql LOAD DATA INFILE
I am trying to load a data file into mysql table using "LOAD DATA LOCAL INFILE 'filename' INTO TABLE 'tablename'". The problem is the source data file contains data of every fields but the primary key is missing ('id' column). I add a unique id field while I create the database but now I need to import the data into the table starting f...
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) ...
MySQL: Blank row in table after LOAD DATA INFILE
Hi, 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 empty line full of nulls/zeros...