Question with Excel and tab delimited

Question

I am facing what, in my opinion, is a strange problem for Excel (in this case, Excel 2007, but maybe also Excel 2003, but I do not have access to it as I write this).

I can reliably convert some server data to a tab delimited format (I have been doing this for many years) and then open it using Excel - no problem.

However, it seems that if I have an html <table> inside one of the fields, it looks like Excel 2007 believes that it should convert the table to rows and columns inside Excel (not what I want). As you can imagine, this drops the entire spreadsheet.

So the question is, is there a way to configure excel to NOT do this (maybe some settings in Excel that relate to reading tab delimited files), or am I missing something?

Thanks.

Answer 1

Save the file as .txt

Now open the file in excel using Drag and Drop (instead of double clicking on your hookey.xls)

A little more work on opening the file, but now the formatting of the text on the tab will be saved.

Answer 2

When you open a tab delimited file, an import mapping dialog box is displayed that allows you to select the data type of each column (date, text, currency, etc.). For columns containing HTML data, select text . This will mean that basically import as is, and not try to automatically analyze the data in a derived format.

Answer 3

Excel 2003 does the same. I don’t think there is a way to do this using the configuration, because Excel finds the delimiters in the html table and breaks the html in the cells and columns, as for other columns.

If the column containing html is always the same, you can use JYelton's suggestion to rename the file as csv and write a small VBA macro to load the file, which automatically selects the html column as text in the import mapping dialog, and you load the file, the calling macro, instead of double-clicking on the file.

Answer 4

If nothing else, import it into OpenOffice.org Calc, save it as a .xls file, then open it in Excel.