Solved

Fast MySQL import

Posted on 2003-12-06
7
755 Views
Last Modified: 2007-12-19
How do I post following text file to the MySql database?

1apple    245red   12.5    
2banana   19 yellow25.4    
3pineapple789       1.0    
4orange      orange      
5banana   12 yellow 25

Data format is always same:

Primarykey ddddd (4 digits) name nnnnnnnnn (9 Chars); count ddd (3 digits); color nnnnnn (6 chars); dddd (4 digits with decimal)
Each record is terminated with an end-of-line character.

Looking for a command that will post above sample info
(actual sample.txt has appx 30.000 records, and it needs to be able to do this in few seconds

Text file name: sample.txt
Database name: fruits ; Username: Admin ; Password: pass
Tablenames: name; count; color; weight

(I have tried Navicat, but it takes 5 minutes to do the import)
0
Comment
Question by:hoster19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Expert Comment

by:bagger99
ID: 9893197
if you can replace the text-file with a csv (comma seperatde value) file, you could use the mysqlimport tool. Can the generating application only handle fixed-length strucures ?
0
 

Author Comment

by:hoster19
ID: 9894126
I've just found this...
http://theoryx5.uwinnipeg.ca/CPAN/data/Text-FixedLength-Extra/Extra.html

Whatever method gets this done in few secs, i'll accept.
Perl/mysql/php......

the issue here is the size 1 meg file with 30.000 records. I need to update it, since the file will change every minute (Its actually mod from stock program, but need this to be solved)
real data has 15 fields with 70 chars of data...
Thanks
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 9898718
The mysqlimport tool that bagger99 mentioned is a command-line interface to LOAD DATA INFILE SQL statement.  It doesn't handle fixed-width columns per se, but your file might work anyway.  Or, you could use sed or awk to add the delimiters and pipe the output to mysqlimport.


http://www.mysql.com/doc/en/mysqlimport.html
http://www.mysql.com/doc/en/LOAD_DATA.html
http://www.student.northpark.edu/pemente/sed/sedfaq4.html#s4.13 (a sed/awk FAQ that addresses fixed column widths)
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:hoster19
ID: 9900567
Ok would these solutions be able to do all of this in few secs? 30.000 lines of data... I doubt.

Looking for a full solution for this.

Trying to increase point value...
Thanks
0
 
LVL 17

Accepted Solution

by:
Squeebee earned 500 total points
ID: 9900926
This is actually extremely easy, when you do not specify FIELDS ENCLOSED BY or FIELDS SEPERATED BY in a LOAD DATA statement you get a fixed width handler.

Quoted from http://www.mysql.com/doc/en/LOAD_DATA.html:

If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are written and read using the ``display'' widths of the columns. For example, if a column is declared as INT(7), values for the column are written using 7-character fields. On input, values for the column are obtained by reading 7 characters. LINES TERMINATED BY is still used to separate lines. If a line doesn't contain all fields, the rest of the fields will be set to their default values. If you don't have a line terminator, you should set this to ''. In this case the text file must contain all fields for each row. Fixed-row format also affects handling of NULL values; see below. Note that fixed-size format will not work if you are using a multi-byte character set.

So... Simple create a table defined the same as your fixed width file and perform LOAD DATA

Primarykey ddddd (4 digits) name nnnnnnnnn (9 Chars); count ddd (3 digits); color nnnnnn (6 chars); dddd (4 digits with decimal)

CREATE TABLE loader
(
      id INT(4) NOT NULL PRIMARY KEY,
      name CHAR(4),
      count INT(3),
      color CHAR(6),
      decval DECIMAL(4,2)
);

LOAD DATA INFILE '/path/to/sample.txt'
INTO TABLE tablename
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '\n';
0
 

Author Comment

by:hoster19
ID: 9901167
Thanks Squeebee for simple and effective answer.

Is there a way to ask private question? DIrected to directly to you Squeebee?
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9901253
That would be against Experts-Exchange policy. That being said many experts have contact information in their profiles.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question