Complex CSV database hijack conundrum...

Cinquecento

This looks nasty....

My client has his entire product range held by another contractor using coldfusion. This is the ONLY complete catalog of items - and he doesn't own the data - he's very much tied in for life unless he want to spend $$$$$$ getting 100,000 product lines re-entered.

The system does however give him the right to export CSV files of each product category.

Now then, has anyone ever managed to write code that can parse a CSV file and build relational mySql tables?

In my head it sounds perfectly feasible to get PHP to identify each unique cell value in each column, build tables containing 'attributes' and 'variables' and key them to a unique product code - building mySql tables on the fly. However in reality this might prove to be a dumb way to do it.

BTW - there is no way I can approach this other company to release the data!

Eventually, I need to then export XML to generate printed catalog artwork... but that's another story!

Norman_Graham

Hi Cinquecento

Unless I've misunderstood the question, you have no problems. A CSV file can be entered into a MySQL DB without difficulty. You set up the tables and columns you want and then just read the CSV file into the DB using the LOAD DATA INFILE syntax (http://www.mysql.com/doc/en/LOAD_DATA.html), making sure you specify the field-termination parameters correctly according to the structure of the CSV file.

Problem solved?

Norm

Cinquecento

I didn't know about that - thanks.

But say we have 100 components that have an attribute 'smells of garlic' and another 100 that 'smells of socks'.... I ought to be creating and 'attribute' table that has two rows 'garlic' and 'socks' to which each of the 200 components has a key...

I ought to read that command before posting this - maybe it already does it....

Ian

Norman_Graham

Hi Cinquecento

You'll need to decide how you want to store the data. If you want garlic and socks rows then, yes, by all means split the data up, possibly into different tables - you can have as many tables as you want.

You will need to do some sorting and restructuring with the CSV file, probably creating several sub-files, in order to prepare the data for entry into the table(s). However, simple copying and pasting should sort this out. I sometimes open csv files in WORD and do some searching and replacing before saving the file as a .txt once again. The WORD search and replace options are extensive.

If you want to be really clever, you could also load the entire data into a huge MySQL table with many columns and then write some PHP to sort the data into a series of new tables. This isn't as complicated as it sounds and is an elegant way of approaching the task.

Best o luck

Norm

Cinquecento

Yes - I can see that it might be better to load the whole thing into mysql first - I'm not sure that PHP alone could handle an array with 100,000 elements and search through each element of each row looking for unique values....

Thanks for your expertise...

Ian

Norman_Graham

Hi Ian

MySQL can handle millions of entries. The default maximum size for a database is 4GB, but I believe that can be altered.

100,000 entries shouldn't be a problem - it's usually the hardware that causes limitations, not the software.

Norm