Preventing Excel applying time formatting to imported data

December 5, 2005 5:49 AM

I hate Excel. I have a load of data in an HTML table. One of the columns has the data in the format xx:xx.x (e.g. 03:45.7). THIS IS NOT A TIME. IT JUST LOOKS LIKE ONE. Whenever and however I import this into Excel, it will always import it as a time causing me no end of aggrevation.

Whenever I then use this data, for example to import into Access, it will always mangle it. I cannot remove time formatting. The only solution I have ever found is to import the data, make another column "text" and then retype all 400+ fields and hope I don't make a mistake. There must, surely, be a better way. Please help me AskMefi.

posted by Hartster to Computers & Internet (17 answers total)

What is the data? Is it a scientific format?
posted by lampshade at 5:55 AM on December 5, 2005

Response by poster: OK, I lied. My bad. It is sort of a time format, where 07:45.8 is seven minutes and 45.8 seconds (the time it takes someone to complete a task). However, it is important that the time stays in this exact format. As second prize, a formula to convert it into tenths of a second (e.g 07:45.8 = 4658) would be good.

(The HTML table is provided by third parties and I have to deal with this every month or so, so a solution would save me a lot of time and aggro).
posted by Hartster at 6:02 AM on December 5, 2005

What you could do is add your own cell format. Right click on any cell and choose 'format cells', click on the number tag and go to the bottom of the list and choose 'custom'. Add your own number format and see if Excel recognizes it.
posted by Alison at 6:06 AM on December 5, 2005

You can set the format of the data in a cell iirc. Click the column header where it will end up getting imported, go to format cells, select Text. Hopefully it will now not mangle your data for you.
posted by spinoza at 6:07 AM on December 5, 2005

Wait, the format you want already exists. Go to format cells, and choose 'time' from the category list. Then choose the one from the type list that says '30:55.2'.
posted by Alison at 6:11 AM on December 5, 2005

Response by poster: Thanks Alison. Unfortunately, when I then import the data into Access, it will still rewrite 07:45.8 to 00:07:45. Maybe this is a fault with Access, or rather the import wizard rather than Excel? (I am perfectly happy to transfer my mistrust and dislike to whatever most deserves it)
posted by Hartster at 6:24 AM on December 5, 2005

You could preface each entry with a neutral apostrophe ('), which will force literal saving of the text you type.
posted by joeclark at 6:30 AM on December 5, 2005 [1 favorite]

If you highlight the cells you need to change and choose the right '30:55.2' format, it should change to what you want. If not, I'm out of ideas.
posted by Alison at 6:33 AM on December 5, 2005

Excel itself uses apostrophes at the beginning of ambiguous field values to stop things like this happening.
For instance: a column of "00" secret agents would become '001; '002; ...; '007 so that Excel itself wouldn't convert this column of almost-numbers into a column of actual numbers (1; 2; ...; 7).
It hides the apostrophe when just displaying the value, but if you edit it the apostrophe is shown.

It's ugly, it's a hassle, it's a bodge, it's the MS way. Excel is just trying to be too helpful and automating processes that you didn't intend to do anyway, unfortunately in Excel I haven't found a way of telling excel to be less "helpful".

If you do find a better way of doing this, please confirm or report here, because I'd be very interested in the answer.
posted by NinjaPirate at 6:37 AM on December 5, 2005

Oh, which joeclark just said.
Never mind.
posted by NinjaPirate at 6:38 AM on December 5, 2005

Best answer: The problem is that Excel tries to apply some smartness when it imports your HTML table, so it imports your times into the internal Excel time format, rather than just keeping it in the format you want. The ideal solution would be to somehow tell Excel not to do this at the import stage, but I can't find a way to do this.

So, if you want to brute-force that time format back into your preferred literal text format, the following ugly formula should do the trick:

=(TEXT(INT(A1*24*60),"00")) & ":" & (TEXT(MOD(FLOOR(A1*24*60*60,0.1),60),"00.0"))

assuming that your time is in cell A1.
posted by chrismear at 6:38 AM on December 5, 2005

There is a way to force things (if your data is in CSV, and you can save Excel to CSV) and if the final data is going to Access, this may help:

'schema.ini'

I've used this successfully to force datatypes in the past. Just make sure it is in the same folder as the CSV file.
posted by jkaczor at 6:44 AM on December 5, 2005

I haven't used Excel in a while, but you have the ability to change how things are imported. When you do the import, Excel will guess what the data in each column you, and ask you to approve the guess - Excel chooses General most often, with a smattering of Date, and so on.

You should pick your troublesome column and tell Excel it isn't a Date, it's Text.

Sorry I can't be more specific about the exact clicks to make, but do the import again and this time pay attention during the process, don't just blindly click Accept at each stage. You can change how things are imported and import them correctly.

Excel also does this with scientific data: i.e., if you have "0.7500" in a field (precision to ten-thousandths), Excel will helpfully treat this as a Number field and convert it to .75 for you. Again, treat the field as text when you import it.
posted by jellicle at 6:52 AM on December 5, 2005

You do get all those options when you import, say, a CSV file. But when you're opening an HTML file, you don't get any dialog box -- Excel just opens it and makes all the decisions behind the scenes. That's the problem here.
posted by chrismear at 6:58 AM on December 5, 2005

Go with jellicle's option. Import, and specify column types as text. I've had to deal with this when importing activity profiles (number of actions per 5 minute bin over the course of weeks of constant recording). I export the data to a Matlab package, and the times must be in the exact format required by the package and not in the default Excel format... having to manually specify each required column as plain text is a pain but it works.
posted by caution live frogs at 7:06 AM on December 5, 2005

Are you opening a local HTML file or one from a website (using "web query" under Data | Import External Data | New Web Query)?

Because if you are performing a web query, there is an option ("options" button in upper right corner) for "Disable Date Recognition" which might work for you. I don't have the appropriate sample data to test that this with... (you can also browse to a local file from the built-in browser this thing uses)

Or, seeing your reply to Jellicle RE: no import dialog, maybe there is an intermediate step you can use to get your html tables into csv? (open in word as a word table, save as CSV??)
posted by misterbrandt at 8:09 AM on December 5, 2005

Response by poster: Thanks a lot for all your help! As I was under a bit of time pressure, I ended up retyping them this time. I've quickly run through a couple of solutions here, however, for the future, and it looks like chrismears idea of using
=(TEXT(INT(A1*24*60),"00")) & ":" & (TEXT(MOD(FLOOR(A1*24*60*60,0.1),60),"00.0"))
works brilliantly, converting another column into a text formatted version of the original. Importing this into Access now works fine.

For some of the other solutions, the problem is, as has been pointed out, that Excel, being helpful, does everything silently, certainly when importing from HTML, so there is no way to change the datatype during importing. I suspect that for misterbrandt's solution of converting via an intermediate step of csv is probably a good workaround.

(Incidentally; I am taking HTML, created from Excel, importing it back into Excel, adding some columns of my own, and then importing it into Access to append to an online MySQL database so that it can be served up as HTML again, an activity that feels vaguely Kafkaesque)
posted by Hartster at 9:02 AM on December 5, 2005