Re: having hard time with xls->mysql issues

On Thu, 21 Dec 2000, Siim Einfeldt aka Itpunk wrote: > > > I need to convert an .xls(Excel) file into a database table. I have one > xls file with about 20-30 sheets, in every sheet I have about up to 150 > rows of text which I need to write to the database. > (1) On row one (covering 5-6 columns) I have the hotel name > (2) Starting from the 6th row I have 4 columns which are important to > me(ABCD): > L-P 24.02 16.02 15 dbl <-- this is what type of stuff is written to > the cols:-) > Or actually the first one isn´t that important, but the other ones > are. The second and third columns should go to different columns in mysql > as well, the fourth column should be split up into two pieces, the first > piece, the number(15 in this case) should go to one column and the other > part (dbl in this case) should be written to another column. The second > and third column, these are dates, which will have to be written to the > database in the *right* way. EG: 24.02 should be coverted to 2001-02-24 > before inserting to the database. And all the other dates the exact same > way and year is 2001 everywhere. > The database table itself could look something like this: > id > hotelname > firstdate > seconddate > number > type > primary key > + some additional fields which I will add later:-) > > Please, if anyone could give me some advice or direct me to some code, I`d > really appreciate it. I promised to get my project done by 24. dec and > this missing 'module' is slowing my work down quite much. > > > Thank you in advance, > Siim Einfeldt > You don't say if htiss will be a one-shot or on-going conversion process. If it's one-shot, then you can convert the xls to csv and use fgetcsv(). If not, you'll have to use either some sort of Excel macro or VBA script to convert the xls first, or use the Win32 version of PHP with COM. The splitting of the dates and 4th column is easy enough using explode(), then date() or the database date functions to fuse the dates back together. -- Ignacio Vazquez-Abrams <ignacio@openservices.net>