Excel File

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Excel File

Post by Jim_Bo »

Hi

Re: Attached file

I get sent a excel file as attached each day updating products and stock levels and prices..

I am trying to find away to dump that file into my database without having to restructure the excel file. It needs to be in the data base in such away that I can create a popout menu with the headings you see in the file i.e

CPUs :: Intel
CPUs :: AMD

etc etc

to filter the procucts in my shopping cart..

I have 3 tables atm, maincat, subcat and products.. But it takes way to long to try and manually sort the changes.

Any Ideas on how I can do it by just dumping the excel file?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Excel File

Post by JakeJ »

Is the excel file built in an orderly way? Can you attach a redacted sample that won't give away any proprietary information?
User avatar
arrielmabale
Forum Newbie
Posts: 15
Joined: Fri Aug 13, 2010 3:57 pm
Location: Dubai

Re: Excel File

Post by arrielmabale »

A sample would be a good reference if your structure would stay intack
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Re: Excel File

Post by Jim_Bo »

Do you mean post the actual excel file?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Excel File

Post by JakeJ »

Yes, the actual excel file. You can attach documents here. Just be sure to remove any proprietary information first.
eFishy
Forum Newbie
Posts: 9
Joined: Tue Jan 05, 2010 12:26 pm

Re: Excel File

Post by eFishy »

would be a lot easier if you could save the Excel file as CSV and then import that...
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Re: Excel File

Post by Jim_Bo »

The forum doesnt appear to accept xls files..
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Excel File

Post by McInfo »

Compress it into an archive first. (".zip", etc.)
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Re: Excel File

Post by Jim_Bo »

Here is the excel file ziped as rar. I have deleted heaps of the lisitngs to make a shorter file, but thats the layout of the document.

Thanks
Attachments
pricelist.rar
(5.53 KiB) Downloaded 27 times
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Excel File

Post by JakeJ »

First of all.. do you already have a database complete with all of your product names? If so, based on the formatting, it should be pretty easy to filter out the headers.

You might also consider making another excel spreadsheet specifically to filter out the section headers and then export to CSV.

I did a google search and there are some tools that will export excel to mysql for you.

The good news is though, you've got a pretty predictable structure so you won't have too much trouble.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Re: Excel File

Post by Jim_Bo »

Here is my existing table structure:

Code: Select all

CREATE TABLE maincat (
  maincatid int(5) unsigned NOT NULL auto_increment,
  maincatname varchar(55) default NULL,
  PRIMARY KEY (maincatid)
) TYPE=MyISAM;

CREATE TABLE subcat (
  subcatid int(5) unsigned NOT NULL auto_increment,
  maincatid int(5) unsigned NOT NULL,
  subcatname varchar(55) default NULL,
  PRIMARY KEY (subcatid)
) TYPE=MyISAM;

CREATE TABLE products (
  pid int(25) NOT NULL auto_increment,
  subcatid int(5) NOT NULL default '0',
  partnumber varchar(15) NOT NULL default '',
  manufacturerscode varchar(100) NOT NULL default '',
  productname varchar(55) NOT NULL default '',
  stock varchar(3) NOT NULL default '',
  price decimal(11,2) NOT NULL default '0.00',
  PRIMARY KEY (pid)
) TYPE=MyISAM;
I am trying to take the manual work out of updating the database.. There is no garantee that excel file listings wont change order as items are added and removed, so the "pid" may not always match. So was thinking that I just clear the database and re dump each time.

Im at a lost on a fast way to do daily updates, so open to any options even if I have to create a whole new table structure.

I have a tool to do excel to sql, but am trying to find a method where I dont have to spend an hour restructuring the excel file before dumping it.


Thanks
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Re: Excel File

Post by Jim_Bo »

Any ideas?, I still cant think up a good way to take all the manual work out of updating the databse.

Thanks
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Excel File

Post by JakeJ »

Unless your files have some consistency to them, there's not much you can do in regards to automation. Things don't have to be exactly in order, but if the stuff you have to remove is predictable, you should be able to handle it in VBA code. For example, if the headers always have a gray background, write a vba macro that removes all lines with a gray background.

You could also do a a field count on individual rows. If a row has a certain number of fields, then it's a product number and should be kept, all else would get deleted. Then your file would be ripe for export to CSV or directly importable from php to mysql.

You've got to look for the commonalities and capitalize on them. It will seem like a lot of work up front, but it will save you many hours in the long run.
Post Reply