Tuesday, March 1, 2011

Excel and Tab Delimited Files Question

I am encountering what I believe to be a strange issue with Excel (in this case, Excel 2007, but maybe also Excel 2003, but don't have access to it as I write this).

I can reliably convert some server data over into a tab-delimited format (been doing this for years) and then open it using Excel - no issue.

However, what seems to be happening is if I have an html <table> inside one of the fields, it looks like Excel 2007 thinks it should be converting the table into rows and columns inside Excel (not what I want). As you might imagine, this throws off the entire spreadsheet.

So question is, is there any way to set up excel to NOT do this (perhaps some setting in Excel that pertains to reading tab delimited files), or am I missing something?

Thanks.

From stackoverflow
  • When you open the tab-delimited file, you are shown an import mapping dialog that lets you pick each columns' data type (date, text, currency, etc.). For the columns that have HTML data present, choose text. This will tell it basically to import as-is and not try to automatically parse the data into a derived format.

    OneNerd : the thing is, i am not shown the 'import mapping' dialog -- it opens right up and does its thing. How are you getting that dialog to appear? Thanks -
    JYelton : What is the extension on the file you are opening? Some file types Excel will open automatically where others provide the dialog. Try changing the file extension to CSV (comma separated values). You'll still be able to choose tab as the delimiter.
    OneNerd : i see - wont work for what I am trying to do. I am used to saving the tab delimited file as an .xls file, and excel always opened it up properly. Adding that extra step kills the ease-of-use. Is there another delimited format that will 'just work' without having to tell excel about it? Am wondering if this is an excel-2007-only issue.
    JYelton : If you save it with an .xls extension, then Excel uses some internal defaults for mapping the data. How to control those defaults... is unfortunately beyond me. Sorry.
  • If nothing else, import it into OpenOffice.org Calc, save as an .xls file, then open in Excel.

  • Excel 2003 does the same. I don't think there is a way to do it with a config because Excel finds delimiters in the html table and breaks the html in cells and columns as it does for the other columns.

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

  • Save your file as .txt

    Now open the file in excel using Drag and Drop (rather than double clicking your hookey .xls)

    Slightly more work to open the file, but your tab text formatting will now be respected.

0 comments:

Post a Comment