Page 1 of 1

Excel File

Posted: Sat Sep 04, 2010 4:23 pm
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?

Re: Excel File

Posted: Sat Sep 04, 2010 6:46 pm
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?

Re: Excel File

Posted: Sun Sep 05, 2010 5:15 am
by arrielmabale
A sample would be a good reference if your structure would stay intack

Re: Excel File

Posted: Sun Sep 05, 2010 6:45 am
by Jim_Bo
Do you mean post the actual excel file?

Re: Excel File

Posted: Sun Sep 05, 2010 1:22 pm
by JakeJ
Yes, the actual excel file. You can attach documents here. Just be sure to remove any proprietary information first.

Re: Excel File

Posted: Sun Sep 05, 2010 1:31 pm
by eFishy
would be a lot easier if you could save the Excel file as CSV and then import that...

Re: Excel File

Posted: Sun Sep 05, 2010 3:39 pm
by Jim_Bo
The forum doesnt appear to accept xls files..

Re: Excel File

Posted: Sun Sep 05, 2010 4:41 pm
by McInfo
Compress it into an archive first. (".zip", etc.)

Re: Excel File

Posted: Sun Sep 05, 2010 5:03 pm
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

Re: Excel File

Posted: Sun Sep 05, 2010 5:59 pm
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.

Re: Excel File

Posted: Sun Sep 05, 2010 6:13 pm
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

Re: Excel File

Posted: Tue Sep 07, 2010 10:01 pm
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

Re: Excel File

Posted: Tue Sep 07, 2010 10:17 pm
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.