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 need to copy around 15,000 rows of data from an MS SQL query to an Excel 2007 spreadsheet. Some of the cells contain HTML as plain text, and most of these include HTML tables. I want the HTML to stay plain text in Excel, tags and all; but no matter what I do, Excel sees the tags and renders the table, which completely messes up the sheet. Formatting the cells as text doesn't work. Paste Special > Text doesn't work. Copying the text of the individual cell containing HTML directly into the formula bar DOES work, but that's not really a feasible solution for 15,000 rows.

I know it's possible to keep HTML as plain text because I managed it once; I just don't remember how. I've also seen other questions from people having the opposite problem, so the functionality definitely exists.

I'm incredibly frustrated and would deeply appreciate your help.

Edit:

@variant: Pasting into Notepad and copying the text from there produces the same results as copying the query results directly. Also, data pasted into Notepad becomes tab-delimited, and there are indenting tabs in the HTML, so even if it got rid of the formatting, I'd think that would mess up the columns. The text also contains commas, so saving as a .csv doesn't help either. (I did try it.)

@Jay: Here's an example of the HTML, with irrelevant text replaced. Sorry about the horizontal scroll - since this is a formatting question, I thought it would be best to paste the text as-is. (I know the <li> tags are unclosed; I'm not concerned with fixing bad HTML in this case.)

    <center>  <table border="1" width="400" style="border: 1 solid #808080" cellpadding="2" cellspacing="0" bordercolor="#C0C0C0">      <tr align="center">      <td bordercolor="#800000" width="100%" height="14" style="background-color:#800000;"><font color="white">Header text</font></td>      </tr>      <tr>        <td width="100%" height="14">Paragraph of text</td>      </tr>      <tr align="center">        <td bordercolor="#800000" width="100%" height="14" style="background-color:#800000;"><font color="white">More text</font></td>      </tr>      <tr>        <td width="100%" height="14">  <ul><li>Bullet point   <li>Bullet point   <li>Bullet point   <li>Bullet point   <li>Bullet point   <li>Bullet point   <li>Bullet point</ul></td>      </tr>      <tr align="center">        <td bordercolor="#800000" width="100%" height="14" style="background-color:#800000;"><font color="white">More text</font></td>      </tr>      <tr>        <td width="100%" height="14">Some final text</td>      </tr>    </table>  </center>
share|improve this question
    
It's a bit of a backward way of doing things, but sometimes it's useful to filter things via notepad (ie, pasting in to strip out formats then copying it again from there) before dropping them into Excel. –  variant May 18 '11 at 20:16
    
Can you examples of text you are trying to copy/paste to this question? I tried this: <html><head></head><body><b>bold</b></body></html> and I was able to copy/paste it from cell to cell. –  Jay Elston May 18 '11 at 23:39
    
@JayElston see Mouse's update to his question. –  studiohack May 19 '11 at 22:17

3 Answers 3

up vote 2 down vote accepted

What eventually worked was rewriting the SQL query to add a pipe ( | ) between all fields (since the pipe didn't appear in any of the text), then saving the results to a text file and importing that as pipe-delimited data, formatting every column as text in the import dialog.

share|improve this answer
    
Bugger me, that's a PITA for just a copy/paste! I've been scratching my head with this as well today, so thank you! –  franglais Jun 3 at 11:58

Check if you have HTML smart tags. Click the Microsoft Office button > Excel Options > Proofing > AutoCorrect Options > Smart Tags. Uncheck Embed smart tags in this workbook. Save your file and restart Excel. See if this helps.

share|improve this answer
    
The "Embed smart tags" option is already unchecked. –  Mouse May 19 '11 at 15:05

Excel is trying to be helpful. You have the table tag, so it assumes all the <td> values are cells.

Before you copy the cells, select them, perform a Format Cells operation (from the right mouse menu), and in the Number tab, select text.

I did not test it on you string, but I did test it on this string:

<table><tr><td>1</td><td>2</td></tr></table>

With general formatting, it pastes two cells with values of 1 and 2 With formatting set to text, it pastes the entire text string int a single cell.

share|improve this answer
    
I should have been clearer initially: I'm copying the data from Microsoft SQL Server Management Studio, not from Excel, so there are no formatting options. Based on your suggestion, though, I tried pasting the data into Excel, formatting the cells as text (no change), and copying and pasting the newly formatted cells. Still nothing. Edit: @Jay –  Mouse May 20 '11 at 15:40
    
@Mouse -- Oh, I should have read your question more carefully. I can duplicate the text entry problem. When I paste the above simple table from a text file, it pastes the raw html. If I select the cell and do either a cut or copy and then paste, it again pastes the raw html. If I selected the text in the formula bar and copy it, then paste it into a cell (even one that I have changed the cell format on) it pastes it as two separate cells with the cell values only. –  Jay Elston May 20 '11 at 22:22

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.