2

I have some HTML files that contain tables, which I need to perform some analysis on.

I can open them in Excel, and it preserves all the table formatting and layout (which is what I want).

The problem is that it, by default, formats all cells as "general". This means Excel's "smart" data conversion kicks in, which, as has been noted my many on stackexchange in the past, causes all kinds of problems when codes and names show up as dates and get converted to a number.

There are ways to get around this when importing from plaintext, forcing Excel to bring up a wizard which allows you to change the import format from "general" to "text". How do I make Excel treat everything as text for an HTML file?

Is there perhaps some way I can change a global Excel setting that stops the general format from converting dates? Or is there some way specifically for opening html files that will stop the "general" format from being applied?

1

I would use the Power Query Add-In for this. Power Query can read HTML files (local or web). It looks for tables so there will need to be some consistency in the HTML structure. Once the HTML table has been read, it will try to automatically detect dates - you can override this and convert columns manually.

1

1 year later, you can use a web query and change the options to disable date recognition, as specified here: https://support.microsoft.com/en-gb/kb/287027

Rather than opening the HTML file, point the web query to a local address (file:///C:/Users/.../file.html)

To prevent Excel from automatically converting numbers to dates, follow these steps when you create a new Web query: In Microsoft Office Excel 2003 or in Microsoft Excel 2002, point to Import External Data on the Data menu, and then click New Web Query.

In Microsoft Office Excel 2007, click From Web in the Get External Data group on the Data tab.

In the Address box, type the address of the Web page that contains the table that you want to import, and then click Go.

Click the appropriate table marker to select the table that you want to import.

Click Options.

Under Other Import settings, click to select the Disable date recognition check box.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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