MySQL Forums - CSV Storage Engine

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 ?

Import data from CSV file in a hosted mySQL enviroment (2 replies)

Johan Skånberg — Sat, 20 Jun 2009 08:14:14 +0000

I need to import data to a table in a mySQL database from a CSV file. The problem is that my database resides on a ISP:s server so I don't have full access to the mySQL console. Also I need to schedule this import to be run every night. Any ideas ? (cause I have tried everything and is now fairly desperate)

instant flush of csv table (no replies)

Mat private — Wed, 07 May 2008 10:41:39 +0000

Hi,

I just started using a symbolic-linked database with a csv table. It all works fine, but if I edit the csv file and run a select statement within mysql, I don't get the results that I just inserted manually in the csv file. I need to do a flush table. Is it possible to do this instantly. So if the file is edited, mysql flushes the table. Can this be don by, for instance, a trigger? If so, does anyone know how?
Kind regards,

Matthijs

problem import data (5 replies)

Gert V — Mon, 11 Aug 2008 08:39:20 +0000

Hello

I also made a topic in the Newbie section, but until now nobody has give me the right solution.

I want to import data from a file (txt, csv, ...) into a mysql database. The table has several columns and one of them is a column of the datatype enum.

...

mysql> load data infile 'D:\\PRODUCT\\product2.csv'
-> into table PRODUCT
-> fields terminated by ';'
-> optionally enclosed by '"';
ERROR 1265 (01000): Data truncated for column 'Type' at row 1

example of csv-file:
000001;omschrijving1;19;7;7;"4"
000002;omschrijving2;19;8;7;"4"
000003;omschrijving3;19;9;21;"2"
000004;omschrijving4;19;9;7;"4"
000005;omschrijving5;19;7;9;"4"
000006;omschrijving6;19;7;11;"1"

Does somebody know a way to import a delimited file in a table with an enum-column?

Can someone give me an example of a working example?
Is it posssible to display an example of the flat file and the describe of the table and the executed command

Thx

Gert

well what is the FIELDS ENCLOSED BY value in the excel file ? (1 reply)

Leonidas Savvides — Sat, 01 Dec 2007 21:30:29 +0000

I try to save excel file as csv and import to phpMyAdmin: but excel says there will be some changes de to special char ...I save anyway .
well what is the FIELDS ENCLOSED BY value in the excel file ? How change this ? what is the default ?

Storing the CSV file in my desired location. (no replies)

Soumik Basu — Tue, 04 Sep 2007 12:27:28 +0000

I am using MySQL as a backend tool in my application.I have to export the resultset in CSV format so I am using the query:
"Select * into OUTFILE 'batch' FIELDS TERMINATED BY ',' from Orders ";
I am storing my resultset in the file "batch" but I cannot give a path to store the file(batch) in my desired location,by default it is getting stored in my database folder only.I want to store this file in a separate folder other than my database folder.

I need urgent help regarding this issue.
Thanks in regards.

CSV and Date values (1 reply)

Ernesto Oliva — Fri, 10 Aug 2007 04:49:57 +0000

Hi,I'm trying to Load a data file to MySQL 5.0 I'm having problems with a DATE value in the csv file becouse don't accept the EUROPE format
e.g I have a value 06/05/2007 and MySQL throws me an error :
Incorrect date value: '06/05/2007' for column 'fecha' at row 1
If I put the right format 2007-05-06 work and when I list the table show me the value 06/05/2007 .
What I must do for MySql accept this format(dd/mm/yyyy) from an csv file becouse I test with functions and not accepts.

i am not able to use BEGIN,END,DECLARE to create stored procedures (no replies)

sandeshas naik — Wed, 10 Jan 2007 10:53:41 +0000

hi All..
i am trying to create a stored procedure using my sql 5.0 and client as 3.2,
stored procedures are creating if i didnt declare any variables and with out using BEGIN and END keyword, but if use any key words like BEGIN,END,DECLARE then it is giving error "you have an error in sql syntax,check manually near line 1".
what i have to do to add begin,end and declare keyword...
below code is excuting

create procedure
LOAD DATA
INFILE 'C:/temp/data.csv'
INTO TABLE student_info
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

but if i use the same code to create sp with begin and end key word its giving error

i am not able to use begin,end ,declare..
please help me any one to resolve this issue,

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

vandana ahuja — Wed, 06 Dec 2006 05:34:41 +0000

i m having a database in excel sheet ,and it is a large bulk of data ,the problem is all the excel sheets having the same data but in different format,,i just want ot convert it on MYSQL database format,, please help me out....tell the easiest way to convert it on Mysql database

CSV Storage Engine and no mmap support (1 reply)

Paul Smedley — Sun, 16 Jul 2006 15:05:12 +0000

Hi All,
I'm trying to build MySQL 5.1.11 on OS/2 - I've built previous versions (lastly 5.0.21) with little trouble but with 5.1.11, the CSV engine is now included by default.

As my gcc doesn't include support for mmap, compilation fails in ha_tina.cc with munmap undeclared.

As the CSV engine is now mandatory - there doesn't seem to be a clean way around thing.

I notice in ha_tina.cc an #ifdef for mmap on Netware - does MySQL 5.1.11 build on Netware at all?

Any ideas appreciated.

Cheers,

Paul.

Problem with default csv file size (no replies)

mel lewis — Wed, 21 Jun 2006 21:05:22 +0000

Hello

I am using phpmyadmin to upload a csv file to a MySQL database. The problem is that the maximum file size that I can upload is set to 2048kb which is insufficient.

Can anyone assist me by advising how to increase filesize?

thanks

Mel Lewis
UK

CSV LOAD DATA Question: line term in quoted field (6 replies)

douglas_reith — Tue, 02 May 2006 13:21:12 +0000

Hi there,
I have a windows generated CSV file which I have little or no control over and it has some fields which are optionally enclosed by quotes.

I use this command as per the instructions:

LOAD DATA LOCAL INFILE '/tmp/import/my_file.csv'
INTO TABLE import_my_file
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3);

This works well in the sense that if it finds a field that is quoted it ignores FIELD TERMINATORS (commas) within that field until it reaches the closing quote. However the difficulty I'm having is that it is not ignoring the LINE TERMINATORS within a quoted field.

For example (illustrative only):
col1, col2, col3\r\n
01,"this comma, is ignored",dougreith\r\n
02,"the comma, is fine but this windows carriage return\r\n
will cause problems",dougreith\r\n

I'm hoping somebody else may have come across this issue or just simply know of a neat work around.

thanks in advance.