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>
Best Answer
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.