MySQL Forums - CSV Storage Engine

ERROR 1064 with LOAD DATA statement (FIELDS COLUMNS) (no replies)

Christoph Kukulies — Sun, 05 Oct 2025 14:43:32 +0000

I have the following SQL statement which I source into mysql (5.7.44, Windows 10):

drop table if exists test;
create table test (col1 VARCHAR(64), col2 VARCHAR(80) );
LOAD DATA LOCAL INFILE "t.csv" INTO TABLE test FIELDS OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' COLUMNS TERMINATED BY ';' LINES TERMINATED BY '\r\n' ;

The Data is the following:

E605;^M
E507;^M"A string^M
spanning several lines "some Umlauts in "üöä" to show"^M
escaping^M
last line"^M
E600;"once^M
again"^M

The error occurs when I add the FIELDS related keywords:
FIELDS OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'

parsing csv file to analyze insert, update operations (1 reply)

Kaushik Shete — Tue, 06 Apr 2021 15:49:32 +0000

I am trying to find some csv parsing mechanism so that it detect insert and update operation to be performed.

a.csv

id name age operation
1. abc 23 insert
2. xyz 50 insert
3. pqr 45 update
4. efg 20 insert

Above is the csv file structure that I need to parse.

I already have stored procedures for the create and update the records.
Records with id(1,2,4) to be pass to create stored procedure and
id(3) to be pass to update stored procedure.

MySql after inset trigger not exporting new inserted data into csv file (1 reply)

Priyadarshni Sagar — Sun, 08 Mar 2020 16:59:14 +0000

I am calling the procedure to export a newly inserted record into the CSV file from MySQL after insert trigger. For dynamic file generation, I wrote a PHP script. The procedure is invoking this PHP script by UDF function, sys_exec. The problem, I am facing here is my trigger is calling procedure before actually inserting data into the database so after every after insert trigger call, I am getting old records in CSV file. Is there any solution to get only new inserted data into CSV file?
Here is my after insert trigger, which gives a call to the procedure for exporting CSV file:
BEGIN
call exportFile();
END

exportFile Procedure:
BEGIN
DECLARE RESULT INT;
SET RESULT = sys_exec('php C:/export.php');
END

PHP script to generate a new CSV file and write a new inserted record in the file:

$query = " select id from table ";
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_row($result)) {
fputcsv($fp, $row);

}

INTO OUTFILE Error 2 (2 replies)

Llewellyn Pienaar — Sat, 22 Jun 2019 16:09:52 +0000

Good day, I am trying to run the following command but it does not seem to work.

[root@ ]# mysql -pPassword asteriskcdrdb -s -b -e "select 'Account ID','Destination','Operator','Provider','Date','BillSec','Rate id','Cost' UNION select accountcode,dst,'PBX',route_name,date_format(calldate,'%Y/%c/%e %H:%i'),billsec as Duration,route_id,round(cost,5) from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' UNION select '','','','','','',concat('R',sum(round(cost,5))),'' from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' INTO OUTFILE '/tmp/VoipBilling-$date/CDR/$accountcode-$date.csv' FIELDS TERMINATED BY ',';"

Error is: ERROR 1 (HY000) at line 1: Can't create/write to file '/tmp/VoipBilling-/CDR/-.csv' (Errcode: 2)

Note the filepath in the error code and the filepath specified differs. Almost as if Mariadb does not know to create the folder with the date i.e /tmp/VoipBilling-2019-06-22/CDR/BILLTEST-2019-06-22.csv''

Instead it wants to write it as '/tmp/VoipBilling-/CDR/-.csv'

When I run the same command but change the output path to not use the $date it works. i.e [root@ ]# mysql -pPassword asteriskcdrdb -s -b -e "select 'Account ID','Destination','Operator','Provider','Date','BillSec','Rate id','Cost' UNION select accountcode,dst,'PBX',route_name,date_format(calldate,'%Y/%c/%e %H:%i'),billsec as Duration,route_id,round(cost,5) from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' UNION select '','','','','','',concat('R',sum(round(cost,5))),'' from cdr where calldate > '$prev_date' and calldate < '$cur_date' and cost > 0 and accountcode='$accountcode' INTO OUTFILE '/tmp/VoipBilling/CDR/accountcode.csv' FIELDS TERMINATED BY ',';"

So it is safe to say this is not a folder permision but the command not being able to create the folder with $date specified.

Please assist

Uploading CSV (2 replies)

Peter Richards — Sat, 23 Jun 2018 04:10:05 +0000

Sorry if this is a very basic question, but ....

I'm in the process of setting up a MySQL database and have a one line test entry.

When the database is running live I need to be able to sort by one of the columns (surname) so have been trying to see how I may do this.

I can export the database as a CSV file, which is then opened in a spreadsheet (Open Office Calc) where it may be sorted.

However, I am unable to import it back again as whatever I try I have the error message:

Invalid column count in CSV input on line 1.

Yet I have ten columns in both MySQL and the CSV file

The columns look like this:

"id" "username" "firstname" "surname" "password" "email" "active" "code" "role" "avatar"

When I export the file into OO Calc I use the | to separate the fields. I know I have to edit this out if uploading to a flat database, but what should I be doing to successfully import the CSV file into MySQL please? At present I have been changing the "Columns separated with:" field from a comma to the | separator and I've tried entering:

id,username,firstname,surname,password,email,active,code,role,avatar

in the Column names field, but it made no difference.

how to automate Loading mutiple .csv files from folder (no replies)

Info question — Fri, 23 Dec 2016 00:05:48 +0000

Hi all,
How to load with one call to load multiple .csv files sitting in the folder.

And once done to move them to archive

Any instruction to execute that?

When to use CSV storage engine for MySQL? (no replies)

Mukul chadha — Wed, 14 Dec 2016 06:35:49 +0000

When to use CSV storage engine for MySQL?

Unable to import csv by sqlimport (3 replies)

Alan Jebakumar — Thu, 22 Oct 2015 04:02:53 +0000

Hey Team,

Im trying to import the below csv file data-
Names
Gould, Cherokee
Mccall, Quyn
Blair, Jerome
Norris, Austin
Bush, Vera
Bryant, Macy
Bray, Raphael
Kelley, Drake
Moore, Yardley
Compton, Pascale
Howell, Martena

But i get the below error during its mysqlimport operation-
C:\Program Files\MySQL\MySQL Server 5.6\bin>.\mysqlimport.exe Events h:\Desktop\Nameslist.csv -u root -h dcw01 -vr --local -p --fields-enclo
sed-by='"'
Enter password: ********
Connecting to dcw01
Selecting database Events
Loading data from LOCAL file: h:/Desktop/Nameslist.csv into Nameslist
.\mysqlimport.exe: Error: 1083, Field separator argument is not what is expected; check the manual, when using table: Nameslist

Do suggest how this can be fixed.

Import txt files with fixed width (1 reply)

mariacristina branca — Fri, 27 Jun 2014 13:41:50 +0000

hi,
I have to do a procedure to import data from text files.
The fields have fixed width and are not delimited with "; " character or similar.
I can not use sql like this:
LOAD DATA INFILE '/ tmp / test.txt' INTO TABLE test FIELDS TERMINATED BY ';'

Thanks a lot

How to load multiple csvs from a hierarchy structure? (no replies)

Roberto B. — Mon, 24 Mar 2014 22:12:29 +0000

Hi all,
i am on a Windows machime.
I have a directory A with many subdirectories A1, A2, ... An (some hundreds).
Each subdirectory Ax has some csv files Ax1 Ax2 ...Axm (some tens).
Each csv file has the same structure and i build the LOAD DATA INFILE statement to load the data on a mysql unique table.
LOAD DATA INFILE filename ....
How can i reiterate automatically the single load data infile to load all the csvs without moving them and regroup the csvs??
Any instruction to execute that?
Thanks for your help
Roberto

Cron bash MySQL Query from 2 tables - export to CSV (no replies)

Kevin Mc — Fri, 13 Sep 2013 22:07:14 +0000

I am really bad at creating MySQL queries and need some help. I need to create a bash file to be triggered by a cron job once a week - that queries two tables, grabbing data where the user IDs match in both tables, and adding the select data to a CSV export file. I would like the CSV to be comma separated. Right now the best I can get it tab separated, but that's not my current problem....

My issue in getting this query to run is my syntax (which I know is wrong as I have simply stolen snippets from various articles online). I did get each DB query to work separately (grabbing from one table with one query and another table with another query). Now I need to combine them to grab only the data I need.

Here's my current (non working) bash file:

!/bin/bash
mysql -u USERNAME --password=PASSWORD --database=xxxx_DBNAME --execute='SELECT xxxx_videotraining_user.user_id, xxxx_videotraining_user.training_title, xxxx_videotraining_user.status, xxxx_users.id, xxxx_users.name, xxxx_users.user_employer, xxxx_users.user_ss_number WHERE xxxx_videotraining_user.user_id = xxxx_users.id AND xxxx_videotraining_user.status = "Completed" AND xxxx_users.user_ss_number > "1" ORDER BY xxxx_videotraining_user.user_id LIMIT 0, 10000 AND ' -C > /home/xxxx/subs/vtc/DB_EXPORTS/xxxx_videotraining_completed.csv

I think you can see what I am trying to accomplish here - any help would be greatly appreciated!

NEXT: is there a way to email the results to a specific email address (or multiple addresses) using the bash file?

Thank you.
--Kevin

Script that recognises the name of a column in a CSV file and matches it to the correspondent column, with the same name, in a MySql database (no replies)

Sarah Raniero — Wed, 08 May 2013 14:40:33 +0000

Hi,

we are using PHP and MySql for our application.

we have many CSV files with different fields' order and some fields are empty, for example:

- CSV1 has the following fields order: name, description, date, address, phone
- CSV2 has the following fields order: address, description, name, date
- CSV3 has the following fields order: name, address

To upload these data in our database (and then online), we now need to have all the CSVs' fields always in the same order, so for example: name, descrition, phone, address, date. To do this we need to manually move the columns in the CSVs.

My question is: if the CSV files have the columns in a different order (but with the same field names), is it possible to match a CSV column to its right position in the database,
without having to edit manually the position of the columns in the CSV?

So basically a script that recognise the name of a column in a CSV file (for ex. address) and match it (and its content) to the correspondent column in our MySql database.

Many thanks,
Sarah

Import Data from CSV - Dont insert records that shows warning(s) or error(s) (no replies)

connect fc — Wed, 13 Feb 2013 10:49:58 +0000

Hello All,

I'm importing data from .csv file to Mysql db using "LOAD DATA LOCAL INFILE" query.
.csv contains foll:

ID | Name | Date | Price

01 | abc | 13-02-2013 | 1500
02 | nbd | blahblahbl | 1000
03 | kgj | 11-02-2012 | jghj

My Mysql Table contains following columns:
Id INTEGER
NAME Varchat(100)
InsertionTimeStamp Date
Price INTEGER

MySQL query to load .csv data to the table above :

LOAD DATA LOCAL INFILE 'UserDetails.csv' INTO TABLE userdetails
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(Id,Name,InsertionTimeStamp,Price)
set InsertionTimeStamp = str_to_date(@d,'%d-%m-%Y');

When I executed the query, 3 records got inserted into the table with 2 warnings

a) Incorrect datetime value : 'blahblahbl' for function str_to_date
b) Data truncate at row 3 (because of invalid INTEGER for Price column)

Question :

1. Is there any way for avoiding data to be inserted in table which shows warnings/errors or the row which has invalid data

I dont want Row 2 & Row 3 to be inserted as it contains invalid data

2. For WARNING of Incorrect datetime value above, can I get the row no also?
Basically I want to get exact warning/error with the row number.

Please help!

Best Regards!

data from Excel Sheets(in differtent formats) to Mysql server import (no replies)

dimonddick dimonddick — Fri, 09 Nov 2012 12:01:11 +0000

Hunted Season 1 Episode 4 Just about every period associated with Numerous Adjacent Best Product offers from in search oftough luck shows as well as starts with 15age 14 contestants. Every single episode, i dissenter is eradicated, Blue Bloods Season 3 Episode 7 nevertheless throughout rare conditions some sort of double liquidation or perhaps zero liquidation was presented with through opinion on the judgment control panel. Makeovers ar applied to help participants at the outset of the growing season (generally following your 1st or 2nd eradication within the finals), as well as a trip to a worldwide terminus can be timetabled with about ii-thirds of the way over the period (usually Haven Season 3 Episode 8 with 5-6 participants staying).

Americas Next Top Model Season 19 Episode 11 Online : Over who is going to smash the particular fiercest offer within the cascading down falls is victorious the particular honour of obtaining Americas Next Top Model Season 19 Episode 11
the woman image acceptance this surfaces involving Tyra's household.A brand new edition regarding "North americaazines Up coming Circus tent Modelling", produced and also organised aside Tyra Banks, dividends having fresh partners, fresh natural talent and a brand new decide the actual audience. The women will certainly battle against eachother to help create the esteemed subject associated with "The united statesersus Subsequent Leading Modelling" and also vie for the life history-establishing choice deal that also includes unexampled companions Smashbox Cosmetics, Nine Western world and also Pvc Newspaper. The actual indicate unearths the actual modification of everyday women into potentially ferocious supermodels, along with individuals Nikita Season 3 Episode 4 experiencing regular testing in which determine who can make shortened. Together with coaching aside supermodel Tyra Financial institutions along with contact with large-page mode-marketplace gurus, this finalists vie within a hugely more rapid custom modeling rendering boot camp - an accident program for you to supermodel reputation.

Sort, exactly who used to coach Team recruits just before conquering you to definitely decease pertaining to failing to kill his or her concentrate on, have been discovered to get kidnapped Fringe Season 5 Episode 6 a new girl as well as qualified the woman's as a kid-jewellry. He's the woman's grab a unpredictable volatile to finish that unsuccessful objective. Now Nikita along with Alex really need to get by way of her earlier she kills the Pakistani embassador. Mia, a good hole-and-corner(a) Split agent examining the actual home FTO, third Trend, is charged in the course of a good FBI foray into. Whenever Nikita and also Alex springtime her, Grimm Season 2 Episode 11 your lover turns on these individuals, unveiling to presenting lost belief inwards Section and have became a member of finally Wave's cause. But as Section detects, 3rd Wave's head is in category while using the oral sex of any security company, using the team to fight any Oughout.Ersus. Senator to generate more cash. Undercover Boss Season 4 Episode 2

Nikita Season 3 Episode 4 Online : Now i'm guess When i weren't alone obtaining a little foggy whenever Liza Shark Tank Season 4 Episode 9 reunited with her mom and dad -- affectingly collection in order to Florencia plus the Machine's "Never Let Me Proceed" -- as Alex and Nikita seemed on. As the occurrence doubtless emphasised the amount of Nikita has to offer while both equally a tutor and electric potential rear, it turned out great to discover Alex granted yet another deal using true aroused bodyweight, due to the fact Fonseca tends to bring these kinds of command and also shade to Jungle Gold Season 1 Episode 3 the occasions.

Can't load huge CSV file (no replies)

Fabian Pinzon — Thu, 16 Aug 2012 10:42:19 +0000

Hello,

My problem is a little bit complicated to understand. I have a csv file (27M) with more than 1048576 lines.

Firstly, I've tried to load it in a temporal table I've created in mySQl, but when I excecute the follow instruction in MySQL QUery Browser:

"LOAD DATA LOCAL INFILE 'file.csv'
INTO TABLE Temp FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n' IGNORE 1 LINES (column1,column2,column3,Column4,Column5,Column6)"

The result is "Query Returned no Resulset".

Secondly, I've opened the file with Excel (obviously, Excel has told me that is not possible to load the whole file) and I've saved it with only 1048576 lines. Then, when I tried to load the new file again, it works!!!

I've changed the max_allowed_packet=100M but definitely it doesn't work.

I've tried delete rows in notepad and I've let the file with 1048576 lines but still doesn't work. It seems that Excel "add something" to the file.

I hope you understand.

Copy an existing database to another machine (no replies)

Deep Lotia — Mon, 30 Jul 2012 06:05:09 +0000

I want to copy an existing database from one machine to another. How can i export the whole database to a zip file or anyother csv file.

Export Table to Excel Sheet (1 reply)

Victor Hernan Zevallos Puppi — Wed, 18 May 2011 06:39:50 +0000

Hello, im wondering, this:

I have one table, and i want to export it to a CSV file.I know how to use the select * from table INTO OUTFILE '/tmp/test.txt', BUT Thats not the problem.

For Example:

TABLE users
------------
id names
1 jerry
2 michael

i want this table ordered by fields in CSV, but all appears in the first COLUMN of the excel....

CSV FIELDS
-----------
A1=1 jerry
A2=2 michael

So, how can i put the data, ordered in each field of the excel ?.... like this:

CSV FIELDS
-----------
A1=1 B1=jerry
A2=2 B2=michael

My email is victorzp86@hotmail.com, THANK YOU VERY MUCH.

migrating Huge data from CSV File to mysql (no replies)

Mahesh Dyade — Thu, 24 Mar 2011 08:03:30 +0000

Hi,

i need to migrate a huge(3.6GB) CSV data file to mysql,
I tried with PHP script but it looks like won't work,

please any one can assist me a way to resolve this problem.

thanks :)

date format problem (no replies)

ch faisal — Mon, 21 Feb 2011 09:31:58 +0000

I have the following file in CSV format:

7,748857bb01f0731b58bbabd13a88bfad,http://www.coloradoboomerangs.com/,Colorado Boomerangs,12/15/2005 19:31

When I use import statement from mysqladmin in order to import this csv file into a table in mysql then populated date column show date and time in (0000-00-00 00:00:00) format, which is given below:

12 2f87c060c8ada01d4500 http://www.cs.stir.ac.uk/~kjt/ BibTeX Style Examples 0000-00-00 00:00:00

How can I solve this problem so date should be populate like this

12 2f87c060c8ada01d4500 http://www.cs.stir.ac.uk/~kjt/ BibTeX Style Examples 12/15/2005 19:31

Regards

import CSV file with Spanish Accents (no replies)

manikandan muthuraj — Mon, 13 Sep 2010 15:57:19 +0000

Hello,

I need to import a CSV file into my table with Spanish Accents. Tried to import, but the spanish accents are not inserting into the table. I've already applied "SET NAMES UTF-8" and the table Collation as utf8_general_ci. Here is my create query of the table,

CREATE TABLE `v_customer_master` (
`cus_master_id` int(10) NOT NULL auto_increment,
`cus_type` varchar(255) NOT NULL,
`company_name` varchar(255) NOT NULL,
`job_title` varchar(150) NOT NULL,
`name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`dob` varchar(30) NOT NULL,
`tax` varchar(30) NOT NULL,
`email` varchar(255) NOT NULL,
`alternate_email` varchar(255) NOT NULL,
`security_question` varchar(255) NOT NULL,
`answer` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`pwd` varchar(255) NOT NULL,
`country` varchar(150) NOT NULL,
`state` varchar(150) NOT NULL,
`city` varchar(255) NOT NULL,
`address1` varchar(255) NOT NULL,
`address2` varchar(255) NOT NULL,
`zipcode` varchar(20) NOT NULL,
`phone1` varchar(20) NOT NULL,
`phone2` varchar(20) NOT NULL,
`mobile` varchar(15) NOT NULL,
`fax` varchar(20) NOT NULL,
`website` varchar(255) NOT NULL,
`note` text NOT NULL,
`hear_about` varchar(255) NOT NULL,
`activity_type` varchar(255) NOT NULL,
`no_of_emp` varchar(255) NOT NULL,
`newsletter` varchar(250) NOT NULL,
`sp_discount` varchar(250) NOT NULL,
`friend_name` varchar(255) NOT NULL,
`friend_email` varchar(255) NOT NULL,
`account_type` varchar(250) NOT NULL,
`business_type` varchar(150) NOT NULL,
`tax_zone` varchar(250) NOT NULL,
`discount` float NOT NULL,
`currency` varchar(250) NOT NULL,
`language` varchar(250) NOT NULL,
`photo` varchar(255) NOT NULL,
`small_logo` varchar(255) NOT NULL,
`big_logo` varchar(255) NOT NULL,
`sign` varchar(255) NOT NULL,
`assigned_to_master_dist` varchar(250) NOT NULL,
`assigned_to_dist` varchar(250) NOT NULL,
`assigned_to_agent` varchar(250) NOT NULL,
`assigned_to_engineering` varchar(255) NOT NULL,
`customer_status` varchar(255) NOT NULL,
`reg_date_time` varchar(250) NOT NULL,
`last_login_date_time` varchar(250) NOT NULL,
PRIMARY KEY (`cus_master_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23101 DEFAULT CHARSET=utf8

Can anyone assist me how to go with that.. and explain me briefly how to import the spanish accents into my table.

Thanks in advance.

Source file with list of path (no replies)

D'Auria Francesco — Thu, 13 May 2010 15:51:42 +0000

Hi

I have this problem: i must to import a csv file with a list of path in my Mysql DB.
The file is like this:

"C:\Document\001\file1.jpg"
"C:\Document\001\file2.jpg"
"C:\Document\001\file3.jpg"
"C:\Document\001\file4.jpg"
"C:\Document\001\file5.jpg"

if i use the "load data" command in the table i have an output like this:

C:Document001file1.jpg
C:Document001file2.jpg
C:Document001file3.jpg
C:Document001file4.jpg
C:Document001file5.jpg

the character "\" is lost. I think the problem is that the character "\" is a special character for Mysql.
how can i do to solve this problem?

Thanks

Fixed-length fields (1 reply)

Patrick Zahra — Wed, 24 Feb 2010 16:27:48 +0000

I have a CSV file with fixed-width fields in it. I know how to import them normally:

FIELDS TERMINATED BY '' ENCLOSED BY ''

... unfortunately, each field also has a space separating it from the next. I don't want to have to create a mirror table with single-character fields between each one just to import the data, so is there another way? Like a placeholder variable or cast?

Also, the table fields are VarChar, will that cause an issue as well?

CSV Engine Undocumented Limitations? (1 reply)

Steve Martin — Fri, 18 Sep 2009 08:14:41 +0000

Can someone explain why I can 'see' ie. SELECT COUNT(*) FROM... only 2,209,217 rows of my 12,338,857 row CSV file? The file size is about 5GB (should that make a difference?), row size is around 450, all recs have the same len and all fields are fixed length CHARs. Are there some undocumented limitations at work here?

For what it's worth...

mysql> show table status\G
*************************** 1. row ***************************
Name: m199907
Engine: CSV
Version: 10
Row_format: Fixed
Rows: 2
Avg_row_length: 0
Data_length: 0
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

Issue when export a table to CSV file (1 reply)

Jingyu Qi — Sun, 01 Feb 2009 00:01:00 +0000

Hi all,

I have a table, in which a particular field may have a string with "\n" char. So when I try to export this table to csv file, the record with this field will be seperated to two lines, not like other records. I am using "\n" as LINES TERMINATED BY. I was trying to use "FIELDS ESCAPED BY '\'", but got errors.

I need find a way to replace the "\n" in the filed to white space.

Thanks,
Jingyu.

load data date fields (1 reply)

New eba — Thu, 08 Jan 2009 01:30:02 +0000

Please help me with syntax to load date format data as below.

table
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| dte | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

data file

aaa, 5-Jan-2009
aaa, 5-Feb-2009

what should i do to load this data into mysql table.

i am using below command/syntax but its loading all in this format.

LOAD DATA INFILE '/tmp/ld.txt' INTO TABLE test1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
(name,@dte)
SET dte = (str_to_date(@dte, '%d-%m-%y');

MySql> select * from test1;
+------+------------+
| name | dte |
+------+------------+
| aaa | 0000-00-00 |
| aaa | 0000-00-00 |

I want date to be in 2008-01-05 format, Please help me.

How to convert .ddl to .sql file (no replies)

vasu pampana — Thu, 23 Oct 2008 11:12:04 +0000

Hi everbody,

I am using MySQL database server for my project but i have database file in form of *.ddl...but to run following mysql dump command, i need *.sql file...

mysql dbname -uroot -ppass < test.sql

can anyone give me an idea how to convert *.ddl file into *.sql file...then i can use it easily...I tried by saving this ddl file with sql extension...but when i run mysql dump command it failed because some of the statements in that ddl file are not supported by MySQL..some of those are

CONNECT TO RXDATABASE;

CREATE SCHEMA GENRXDB;

SET SCHEMA=GENRXDB;

Please help me in this regard

Excessive warning messages on import (1 reply)

Craig Harrington — Tue, 21 Oct 2008 20:36:29 +0000

I am drowning in warning messages. The situation: I am importing millions of rows of data in different tables. The data comes from an external source. The data is enclosed in double quotes and comma separated, consistent with RFC 4180. (There are samples below of the table and the data.) MySQL issues a warning whenever it encounters an empty field ("") that is being imported into a decimal column. I do not want to populate the empty fields with numeric values because they are actually empty and populating them will skew the information. Because the data has decimal fields on every row that are empty, the import process is filling up the hard drive with warning messages.

A typical warning message is "1366: Incorrect decimal value '' for column 'NBR_FIELD_1' at row 2", where there is a unique warning message for column of each row.

Is there a way to stop the warning messages? Is there another alternative that I'm overlooking?

(FYI, MySQL issues an error and stops if the empty field is being imported into an INTEGER column.)

Table Structure
================

Field Type Collation Null
------------------------- -------------- ----------------- ----
NBR_FIELD_1 decimal(9,0) NO
VCHAR_FIELD_1 varchar(30) latin1_swedish_ci YES
NBR_FIELD_2 decimal(6,0) YES
VCHAR_FIELD_2 varchar(30) latin1_swedish_ci YES
NBR_FIELD_3 decimal(9,0) YES
IS_VIRTUAL char(1) latin1_swedish_ci YES
VCHAR_FIELD_3 varchar(30) latin1_swedish_ci YES
NBR_FIELD_4 decimal(6,0) YES
VCHAR_FIELD_4 varchar(30) latin1_swedish_ci YES
ARE_CONTIGUOUS char(1) latin1_swedish_ci YES
ARE_MULTIPLES char(1) latin1_swedish_ci YES
IS_AGGREGATE char(1) latin1_swedish_ci YES
NBR_FIELD_5 decimal(12,0) YES
ALLOW_OVERSUBSCRIPTION char(1) latin1_swedish_ci YES
NBR_FIELD_6 decimal(12,2) YES
NBR_FIELD_7 decimal(18,0) YES
NBR_FIELD_8 decimal(18,0) YES
USE_IN_PATHS char(1) latin1_swedish_ci YES
USE_IN_NETWORK char(1) latin1_swedish_ci YES
LOAD_TIMESTAMP datetime YES

Sample Data:
============
"1028","768K","","","","N","","","","","","N","768000","Y","0.00","768000","768000","Y","N","2008.10.14 18:00:15"
"1029","832K","","","","N","","","","","","N","832000","Y","0.00","832000","832000","Y","N","2008.10.14 18:00:15"
"1030","896K","","","","N","","","","","","N","896000","Y","0.00","896000","896000","Y","N","2008.10.14 18:00:15"

SQL to perform data load:
=========================
USE MY_DATABASE;
SET SESSION SQL_MODE="ALLOW_INVALID_DATES";
TRUNCATE TABLE MY_TABLE;
LOAD DATA INFILE "K:/MySampleData.csv"
INTO TABLE MY_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

DateTime from CSV file (no replies)

Jean-Benoit Lesage — Mon, 14 Jul 2008 14:29:36 +0000

I was wondering if someone knows how to do this. I have a very large CSV file (44 million+ records, over 1GB) that I have entered into a db using the replace file method described in the MySQL 5.1.23 article. I have a problem with the datetime format already in my file, which is "dd/mm/yy hh:mm:ss" all in one column. Is there a way to automatically format datetime to fit this format? -Thanks

csv row/records eliminated (no replies)

heather campagna — Wed, 02 Jul 2008 23:29:38 +0000

Hello

Can I get some help please to a seemingly simple problem. I have a excel/csv file. The data is in ROWS and Column format. I want to insert into mysql using the below sql code. It works but.....

The PROBLEM IS: it is broken up into rows/records in mysql, I need it to ignore the returns or breaks or whatever tells it to go to another row and continue to insert the data into the fields in one record. Did I explain that right?

LOAD DATA INFILE 'filename.csv'
INTO TABLE tablename
FIELDS TERMINATED BY ','
(`1`, `2`, `3`, `4`, `5`, `6`)

I dont know what I am doing so I may not have explained it properly please ask any questions that you may be confused about.

How to give another name to csv storage engine (no replies)

advait deodhar — Fri, 27 Jun 2008 05:54:41 +0000

I want to give another name to csv storage engine?
How to do it ?