Anybody with experience in Excel? (converting excel to database)

[Marc]

I have a client who has about 200 megs of information and pictures stored pretty neatly in about 20 different excel files, and each excel file has about 15 or 20 different sheets.

I have to get this on his website (I use php). But first I need to convert the excel files to a database... I'm thinking access is the easiest possibility. Excel does have a function to convert excel files to an access database, but it is going to be certainly very time consuming, as I have tried and there are a few problems with that:

1) He left hundreds of spaces in the files for certain spots, and the convertor has problems with those that I currently have to fix manually.

2) There are so many different worksheets, almost 60 in all, and the conversion function can't merge databases (at least not to my knowledge).

3) Access ain't great for multiple connections and isn't particularly good to run on a website, although I suppose I could do it (php does have support for it). I was thinking once it's all in the access database I could write a simple script to copy all the data into a mysql database or something like that.

4) In the excel files, he left little notes for certain columns that are only visible when you hover over certain cells with the mouse, and it pops up in a little info box.. I need to convert that information as well.

Thanks for your help, any suggestions or ideas are appreciated

[sylow]

if I were you
I would sit down and design a good database structure from excell sheets before I do anything to convert into.
20 excell files and 15 excell sheets for each file means that you have huge database there.

I am not help to you about converting the excell sheets without having them at hand to analys first.

About question 3: Check mysqlfront for converting Access files to MySQL database. You will gain time instead of writing your own manual code for one time conversion.

Wish you good luck, because you will really need it.

[Marc]

Originally posted by sylow
Wish you good luck, because you will really need it.

hehe.

Thanks for your points, they're all quite valid.

[compwizard]

you could export it to a csv (comma seperated value) file and then import it into mysql. Also, you could a macro to do it.

[Marc]

Doh, I've run into another problem -

There are many cells that have a hyperlink to a picture. The picture is there, but the hyperlink only displays the text "photo" and no matter what method I use to convert, nothing sees the actual link address, it only converts the text "photo" without the hyperlink.

Any ideas on that..?

[nagrom]

It sounds like the images are stored in binary format. You'll need to do something like (ColdFusion example):
ToBinary(field) and then ToString(field) when reinserting.

This isn't a good practice though, unless you have some bizarre security problem, you shouldn't store actual images in a DB, just the link to them. (i know you've been stuck with this one)

you can easily convert from access to mysql using DBTools. http://www.dbtools.com.br/

[Goof]

Man, I hope you're getting paid well for this.

Unfortunately, I don't have much light to shed on these kinds of problems, but I would definately suggest migrating the data (at least initially) to Access. Keep in mind that from there you could push it to an MSSQL database (or maybe even MySQL - I'm not sure if this is possible, although this pdf says it is).

Anyway, best of luck. Just keep thinking towards the month-long vacation you'll be able to take after you get paid .

Goof

[Marc]

Actually, I'm really selling myself short on this project... but I'm also not under a lot of pressure to do it well or do it fast.

Sorry, I gave the wrong impression in my post before, I said "The picture is there" when what I mean is, there is a hyperlink TO the location of the picture, so when you click it, the picture opens up in whatever default graphics program you use.

I've found a cool program that can convert the csv files excel generates into a mysql database (link).

But I'm still left with two main problems -

How to convert the comments associated with cells (from the insert>comment menu).

How to convert the addresss of the hyperlinks to pictures.

I think I'm going to have to end up either not converting them, or using some kind of VBA script. Not that I know VBA or anything... oh well.


Not gettin paid enough...

[compwizard]

if you could post one of the xls files up here (if you don't mind us seeing the data) maybe i could help write a script or macro to fix it.

[Marc]

Sure, if you could do anything that would be awesome! If you could somehow get the address of the links into a new column or in the same column inside square brackets or something that would work too probably. The same for the text of the comments... whatever you can do is very much appreciated.

I had some trouble uploading it through sitepoint so after a brief battle with my dial up line I got it up here:
USSR-Russia.xls

This is just one of 20 excel files, the smallest of which is 13.5kb and the largest is 7.67 mb!

Thanks again very much for any help or ideas!

[compwizard]

I took a look at the file....its going to be near impossible to do much automatically with it, as its so inconsistent (blank spaces, different data types) sorry...good luck though

[Goof]

I've looked at the file briefly, and it doesn't look as bad as I thought it did:

1) Can't you just insert NULL into a field where the spreadsheet is blank? If not, try to do a massive replace on the spreadsheets to replace an empty cell with "[NULL!]" or something that won't logically be in the database. You could then convert to the Access database and replace each of these fields with Null.
2) Each sheet will probably have to be a seperate table in the database (maybe make each spreadsheet its own database). That means probably 400 conversions (20 spreadsheets * 20 sheets/file). Yeah, that does suck.
3) Import all of the data in Access, then export it to another database (MSSQL or MySQL) that performs better with larger databases getting hit more often.
4) Either you or the client will most likely have to manually convert the notes - that's what happens when people don't organize their data in a robust fashion (translation: charge them more).
5) (a bonus!) Make sure you logically name the tables/files in a manner that you could somehow access them easily when this info goes on the web (meaning, if you put all of these tables in the same database, think of a prefix or postfix that you could add to each file so they are somehow grouped by which spreadsheet they came from - or some other logical grouping so you don't have to do an 800 line CASE statement when getting the data for the web)

Now, obviously this is a lot of work (you're probably looking at a full week of 8 hour days just to convert the data). If this is something that you didn't know about when you took the job, I'd seriously consider going back to the client and charging more. This is some hefty work that it sounds like you're getting ripped off for. Data migrations are no easy task, and don't be afraid to tell the boss or the client that. Hopefully you're charging by the hour anyway.

Hope that helps,
Goof

[Marc]

Hey guys, thanks a lot for the suggestions (sorry about the slow reply I was away).

I will probably try that Null idea of filling all the blank spaces..

And I think I will have to talk to my client about the price, because this is turning out to be much harder than I thought :P

I actually may be on to a way of using some custom VBA code to extract the comments and the hyperlinks location... but it's still going to be some obscene work.

Thanks very much for your help, once I figure out exactly how to do it, I'll post back here in case you're interested