Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm having some trouble in exporting some data directly from phpMyAdmin. I'm selecting a table in phpMyAdmin, from a specific database, and then I'm exporting that table in Excel based CSV format.

The table I'm exporting has 2 columns in it:

Movie Name | Description

Movie Name contains a name, such as Argo. No problems in the movie name. The corresponding entry for Description contains this:

<h2>Product Details</h2>

<ul>
    <li><b>Actors:</b> Ben Affleck, Bryan Cranston, Alan Arkin, John Goodman</li>
    <li><b>Directors:</b> Ben Affleck</li>
    <li><b>Writers:</b> Chris Terrio</li>
    <li><b>Producers:</b> Ben Affleck, Grant Heslov, George Clooney, David Klawans, Nina Wolarsky</li>
    <li><b>Format:</b> AC-3, Blu-ray, Dolby, NTSC, Subtitled, Widescreen</li>
    <li><b>Language:</b> English, Spanish</li>
    <li><b>Subtitles:</b> Spanish, Portuguese, French</li>
    <li><b>Subtitles for the Hearing Impaired:</b> English</li>
    <li><b>Region:</b> Region A/1 (Read more about DVD/Blu-ray formats.)</li>
    <li><b>Aspect Ratio:</b> 2.40:1</li>
    <li><b>Number of discs:</b> 2</li>
    <li><b>Rated: </b> <span class=""medSprite s_medR ""><span>R (Restricted)</span></span></li>
    <li><b>Studio:</b> Warner Home Video</li>
    <li><b>DVD Release Date:</b> February 19, 2013</li>
    <li><b>Run Time:</b> 120 minutes</li>
</ul>

That is, the WHOLE HTML block above is the Description value for that Argo record.

But when I export the above from phpMyAdmin, this is what I see for the Description:

Argo;&lt;h2&gt;Product Details&lt;/h2&gt;                   

&lt;ul&gt;                  
    &lt;li&gt;&lt;b&gt;Actors:&lt;/b&gt; Ben Affleck     Bryan Cranston  Alan Arkin  John Goodman&lt;/li&gt;        
    &lt;li&gt;&lt;b&gt;Directors:&lt;/b&gt; Ben Affleck&lt;/li&gt;                  
    &lt;li&gt;&lt;b&gt;Writers:&lt;/b&gt; Chris Terrio&lt;/li&gt;                   
    &lt;li&gt;&lt;b&gt;Producers:&lt;/b&gt; Ben Affleck  Grant Heslov    George Clooney  David Klawans   Nina Wolarsky&lt;/li&gt;   
    &lt;li&gt;&lt;b&gt;Format:&lt;/b&gt; AC-3    Blu-ray     Dolby   NTSC    Subtitled   Widescreen&lt;/li&gt;
    &lt;li&gt;&lt;b&gt;Language:&lt;/b&gt; English   Spanish&lt;/li&gt;             
    &lt;li&gt;&lt;b&gt;Subtitles:&lt;/b&gt; Spanish  Portuguese  French&lt;/li&gt;          
    &lt;li&gt;&lt;b&gt;Subtitles for the Hearing Impaired:&lt;/b&gt; English&lt;/li&gt;                 
    &lt;li&gt;&lt;b&gt;Region:&lt;/b&gt; Region A/1 (Read more about DVD/Blu-ray formats.)&lt;/li&gt;                   
    &lt;li&gt;&lt;b&gt;Aspect Ratio:&lt;/b&gt; 2.40:1&lt;/li&gt;                    
    &lt;li&gt;&lt;b&gt;Number of discs:&lt;/b&gt; 2&lt;/li&gt;                  
    &lt;li&gt;&lt;b&gt;Rated: &lt;/b&gt; &lt;span class=&quot;medSprite s_medR &quot;&gt;&lt;span&gt;R (Restricted)&lt;/span&gt;&lt;/span&gt;&lt;/li&gt;                    
    &lt;li&gt;&lt;b&gt;Studio:&lt;/b&gt; Warner Home Video&lt;/li&gt;                   
    &lt;li&gt;&lt;b&gt;DVD Release Date:&lt;/b&gt; February 19   2013&lt;/li&gt;                
    &lt;li&gt;&lt;b&gt;Digital Copy Expiration Date:&lt;/b&gt; February 19   2015 (Click here for more information)&lt;/li&gt;              
    &lt;li&gt;&lt;b&gt;Run Time:&lt;/b&gt; 120 minutes&lt;/li&gt;                   
&lt;/ul&gt;                 

Secondly, the CSV file that I output from phpMyAdmin puts the individual lines from the above block into separate Excel rows and columns. This is not what I want.

How can I export data from phpMyAdmin such that the whole Description block appears in just ONE excel cell, next to the corresponding movie name ?

And also, if possible (although not required), have the Description shown in normal HTML tags (like in the top most code block shown in this thread) ?

This is what I want:

Argo | .....Complete Argo HTML here.....

Batman Begins | .....Complete Batman Begins HTML here.....

Flight | .....Complete Flight HTML here.....

Fight | .....Complete Fight HTML here.....

How can I export from phpMyAdmin so that the output is in the above mentioned format ?

share|improve this question
add comment

1 Answer

First: your PHPMyAdmin version would help.

In phpmyadmin, when you are on the export screen, be sure to select Custom. Then you will see Columns separated with: . This box tells how to distinguish between columns and this is how Excel will know what is a cell and not. Generally a comma is used here. In the Columns enclosed with: box I would recommend a " to encapsulate fields. This specifies what is in one field (so that commas can be added to a description). You will need to tell excel which choices you have used when opening the file to have the data in the correct cells.

As for the HTML special characters, the most likely situation is that the script adding content to the database is using an escape command to change the special characters (ie < is equal to &lt;). As far as I know phpmyadmin does not have a way of automatically changing this back on database dumps. Whoever is in charge of the database storage script will need to be sure that on INSERT statements they do not escape these characters.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.