Re: Query Browser: RegEx text importer
"Michael G. Zinner" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.mycc |
|---|---|
| Organization | MySQL AB |
| Message-ID | <[email protected]> |
Hi,
Let me describe how to use the RegEx Importer, quickly. It is not really
meant to do text import from simple CVS files. We will introduce a
special plugin for that, later. It is meant to extract data from more
complicated text sources.
So let's import some products from Amazon.
* First create the product table in the test database. Execute the
following create statement in the Query Area or from a script (press
Ctrl+Shift+T to get a new script tab sheet)
CREATE TABLE `product` (
`idproduct` int(10) unsigned NOT NULL auto_increment,
`name` varchar(120) NOT NULL default '',
`price` decimal(8,2) NOT NULL default '0.00',
PRIMARY KEY (`idproduct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
(You can simply use the Right Mousebutton on a table in the Schemata
Tree and select [Copy SQL to Clipboard] to get the SQL create statements
of the tables.)
* Now open Firefox and go to amazon.com, select DVD in the BROWSE bar on
the left. Then select Action & Adventure and in the Browse list choose
e.g. Thrillers.
This gives us a list of 20 DVDs. (For me it was starting with Kill Bill,
Volume 1, which I wouldn't consider to be a "Thriller" but anyway...)
* Now select the products. Make sure you start with the "1." of the
first DVD and drag down to below the 20th DVD, so all the text gets
selected, even the "Used & new from $8.98".
* Switch to QB and open Tools > RegEx Text Importer.
Paste the text into the larger area with "Source Text" on the top. This
will be our text we extract the data from.
To make sure we all work with the same kind of text I have included the
text below.
As you can see, every DVD is listed the same way. We will make use of
this. This is what a typical DVD record looks like:
1.
Kill Bill, Volume 1 (2003)
Uma Thurman, Lucy Liu, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.3 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $29.99
Buy new: $19.49
In-store Pickup: $19.99
Used & new from $9.39
* Now we construct our regular expression to parse the data.
Select "1." in the source text. Now click the right mousebutton and
choose [Extract RegEx] > [Extract Numbers]. This will generate the
following RegEx that you can see in the Regular Expression Area.
(\d+)\.
That will find all numbers "\d" with at least one digit "+" followed by
a dot "\.". As this will find ANY number followed by a dot, we will
limit it to numbers at the very beginning of a line by adding "^"
(^\d+)\.
* To test the regular expression press [Execute RegEx]. This will select
"1." since it is the first match. On the Parse Structure tree to the
left you can see two matches $0 and $1. $0 is always the complete match,
$1 stands for the value in the 1st brackets "()".
Press [Execute Next] to find the next match. It should highlight "2."
and you can also see that out matches are updated.
* Next we want to find the DVD's title. As there are some spaces and
line breaks before the title, we have to filter them out first, by
finding all whitespace characters "\s*"
(^\d+)\.\s*
To find the title we add "(.*?)\s*$". This will find all characters
until the next whitespace character before a line break.
(^\d+)\.\s*(.*?)\s*$
Check the matches again with [Execute RegEx] and [Execute Next]. This
should find all 20 DVD titles.
* Now we want to get the price. When looking at the text we will find
out that before the official price there is always the label "Buy new:"
or "new from" so we will search for that with "(Buy new:|new from)". To
ignore all text before that we add ".*?" and to ignore the whitespace
characters we add "\s+".
(^\d+)\.\s*(.*?)\s*$.*?(Buy new:|new from)\s+
This now finds "Buy new:" but stops before the actual price. To find the
price select add "\$(\d+\.\d+)"
(^\d+)\.\s*(.*?)\s*$.*?(Buy new:|new from)\s+\$(\d+\.\d+)
Now we have everything we want in the matches in the Parse Structure Tree.
$2 contains the name
$4 contains the price
* As the regular expression is now complete, we can name it. Select the
"RegEx1" node in the Parse Structure and press F2. Enter the name "Amazon".
* To insert the found data into the database we adapt the INSERT statement.
INSERT INTO test.product(name, price)
VALUES();
Now we want to add our $2 and $4 matches. Place the cursor between the
brackets "()" and drag the $2 match from the Parse Structure Tree onto
the INSERT statement. We will get
INSERT INTO test.product(name, price)
VALUES($Amazon.2);
now add " around "$Amazon.2" since it is a text value and ", " and drag
the match $4 as well. Now we have
INSERT INTO test.product(name, price)
VALUES("$Amazon.2", $Amazon.4);
Note that we use " instead of ' since there might be ' in the found text.
* To preview the INSERT statement press the [Preview] button. That will
display the following INSERT statements for the first view matches.
INSERT INTO test.product(name, price)
VALUES("Kill Bill, Volume 1 (2003)", 19.49);
INSERT INTO test.product(name, price)
VALUES("The Fugitive (Special Edition) (1993)", 11.22);
...
To execute all INSERTs press the [Execute SQL] button.
* Press [Save] to store the import to disk, so you can load it later on
with [Load].
This was a simple example of how to use the RegEx Text Importer. You can
do a lot more by using nested regular expressions which means that you
can define a new regular expression for every match you found.
Just play around with it and you will find that you can extract data
from almost every text you get.
Have fun!
Mike
The text I used in this example can be found here.
--------------- CUT HERE ------------------
1.
Kill Bill, Volume 1 (2003)
Uma Thurman, Lucy Liu, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.3 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $29.99
Buy new: $19.49
In-store Pickup: $19.99
Used & new from $9.39
2.
The Fugitive (Special Edition) (1993)
Harrison Ford, Tommy Lee Jones, et al.
DVD; Rated PG-13; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.7 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $14.96
Buy new: $11.22
In-store Pickup: $10.99
Used & new from $7.99
3.
Vanishing Point (1970)
Barry Newman, Cleavon Little, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.7 out of 5 stars
(Rate this item)
Usually ships in 24 hours
List Price: $14.98
Buy new: $11.24
Used & new from $9.70
4.
Bullitt (1968)
Steve McQueen, Jacqueline Bisset, et al.
DVD; Rated PG; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.2 out of 5 stars
(Rate this item)
Usually ships in 24 hours
List Price: $19.98
Buy new: $13.99
Used & new from $10.99
5.
Blade II (New Line Platinum Series) (2002)
Wesley Snipes, Kris Kristofferson, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.0 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $26.99
Buy new: $20.24
In-store Pickup: $23.99
Used & new from $7.50
6.
Jaws (25th Anniversary Widescreen Collector's Edition) (1975)
Roy Scheider, Robert Shaw, et al.
DVD; Rated PG; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.6 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $14.98
Buy new: $11.24
In-store Pickup: $12.99
Used & new from $6.99
7.
The Hunt for Red October (Special Edition) (1990)
Sean Connery, Alec Baldwin, et al.
DVD; Rated PG; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.2 out of 5 stars
(Rate this item)
Usually ships in 5 to 7 days
Also available for in-store pickup today.
List Price: $14.99
Buy new: $11.24
In-store Pickup: $12.99
Used & new from $8.99
8.
Leon - The Professional (Uncut International Version) (1994)
Jean Reno, Gary Oldman, et al.
DVD; Unrated; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.7 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $29.95
Buy new: $23.96
In-store Pickup: $25.99
Used & new from $17.98
9.
Underworld (Widescreen Special Edition) (2003)
Kate Beckinsale, Scott Speedman, et al.
DVD; Unrated; Region 1 encoding (US and Canada only)
Avg. Customer Review: 3.4 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $19.94
Buy new: $14.96
In-store Pickup: $14.99
Used & new from $7.99
10.
True Lies (1994)
Arnold Schwarzenegger, Jamie Lee Curtis, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.2 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $14.98
Buy new: $11.24
In-store Pickup: $12.99
Used & new from $7.18
11.
The French Connection (Five Star Collection) (1971)
Gene Hackman, Roy Scheider, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.2 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $26.98
Buy new: $21.58
In-store Pickup: $23.99
Used & new from $14.99
12.
The Crow (Miramax/Dimension Collector's Series) (1994)
Brandon Lee, Michael Wincott, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.7 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $19.99
Buy new: $14.99
In-store Pickup: $14.99
Used & new from $11.95
13.
The Bourne Identity (Widescreen Collector's Edition) (2002)
Matt Damon, Franka Potente, et al.
DVD; Rated PG-13; Region 1 encoding (US and Canada only)
Avg. Customer Review: 3.7 out of 5 stars
(Rate this item)
Usually ships within 1-2 business days
Used & new from $9.89
14.
The Rock (1996)
Sean Connery, Nicolas Cage, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.5 out of 5 stars
(Rate this item)
Usually ships in 24 hours
List Price: $19.99
Buy new: $14.99
Used & new from $9.89
15.
The Man With The Golden Gun (Special Edition) (1974)
Roger Moore, Christopher Lee, et al.
DVD; Rated PG; Region 1 encoding (US and Canada only)
Avg. Customer Review: 3.5 out of 5 stars
(Rate this item)
Usually ships in 1 to 4 weeks
List Price: $19.98
Buy new: $15.98
Used & new from $8.00
16.
Dirty Harry (1971)
Clint Eastwood, Andrew Robinson, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.8 out of 5 stars
(Rate this item)
Usually ships in 5 to 8 days
List Price: $19.97
Buy new: $15.98
Used & new from $12.95
17.
The Warriors (1979)
Michael Beck, James Remar, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.6 out of 5 stars
(Rate this item)
Usually ships in 24 hours
List Price: $14.99
Buy new: $11.24
Used & new from $8.75
18.
Streets of Fire (1984)
Michael Paré, Diane Lane, et al.
DVD; Rated PG; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.6 out of 5 stars
(Rate this item)
Usually ships in 24 hours
Also available for in-store pickup today.
List Price: $14.98
Buy new: $11.98
In-store Pickup: $12.99
Used & new from $9.15
19.
Con Air (1997)
Nicolas Cage, John Cusack, et al.
DVD; Rated R; Region 1 encoding (US and Canada only)
Avg. Customer Review: 3.9 out of 5 stars
(Rate this item)
Usually ships in 24 hours
List Price: $14.99
Buy new: $11.99
Used & new from $7.19
20.
The Saint (1997)
Val Kilmer, Elisabeth Shue, et al.
DVD; Rated PG-13; Region 1 encoding (US and Canada only)
Avg. Customer Review: 4.1 out of 5 stars
(Rate this item)
Usually ships in 6 to 8 days
List Price: $14.99
Buy new: $11.99
Used & new from $8.98
--------------- CUT HERE ------------------
Teemu Kuulasmaa wrote:
> Hi Mike,
>
> This is the functionality that I really need. I am looking forward
> future version of Query Browset in order to see progres on this feature.
> Do you hava any idea how long does it take to make this feature useable?
>
> Teemu
>
> Mike Lischke wrote:
>
>> Hi Teemu,
>>
>>> Could some one give me short explanation/tutorial how to use "RegEx
>>> Text Importer". I do know some regural expression but I didn't see
>>> how to use "RegEx Text Importer".
>>
>>
>>
>> This feature is rather an experimental one and is not yet documented.
>> So basically the menu choice should be disabled.
>> There are still some issues with the text importer (e.g. speed) and I
>> don't recommend to use it until it is officially
>> documented. The main task for that importer is to take any text file
>> (e.g. a CSV export from Excel) and extract the data
>> from it using regular expressions.
>>
>> Mike
>> --
>> Mike Lischke, Software Engineer GUI
>> MySQL AB, www.mysql.com
>>
>> Are you MySQL certified? www.mysql.com/certification
>
>
--
Michael Zinner, GUI Developer
MySQL AB, www.mysql.com
Office: +43 676 753 26 30
Are you MySQL certified? www.mysql.com/certification
--
MySQL GUI Tools Mailing List
For list archives: http://lists.mysql.com/gui-tools
To unsubscribe: http://lists.mysql.com/[email protected]