Import Dodgy CSV

c9679

Hello,

Could someone please please help me with this, ive been wracking my poor brains on this for ages and I just cant do it...

My site basically relies on a product data feed from a company, this datafeed is a csv file which is on the other companies website, I need to download this unzip it and then import it into mysql database and I need to do this every 2 hours or my site and products are out of date, lol, the problem is the CSV files of which there are 2 main ones I need arent really formatted properly, so im currently having to

1, Download & unzip
2, Open in Excel, save as Excel work sheet
3, go to Control Panel, Languages, Change List Seperator from , to |
4, Open work sheet in excel
5, Remove first 2 rows, (contain rubbish)
5, Save as CSV
6, Go to PhpMyAdmin
7, Select DB, and Table.
8, Import CSV file, upload it, Seperator |

There u go its done every 2 hours, lol...

Anyway the problem with the files is most fields are seperated by , commas however some fields also contain commas in them but these fields are enclosed in " Quotes. Also on some rows where not all fields are used they are just left blank with no ending commas

eg Product file contains 100 fields
eg Product spec file contains 51 fields

Spec file - Fields: Model, S1, S2, S3, S4,...
Content
M1, s, s, s, s, s, s, s, s...
M2, r, t
M3, d, d, r, ddds, "dsd, sssfd", 43

So basically im trying to make a script that downloads the file from the website to my site, it then extracts the csv files from the Zip, then reads them and formats them add trailing ,,, if they fall short of the minimum for that csv (eg 100 or 51) then splits the line by commas, trying to ignore the ones within quotes, then add all that to mysql...

lol, I have managed to get it to download, then unzip the files, I can read the file but odd things keep happening with the reads and with the csv formatting I just cant get my head around the import to mysql part, it either doesnt read the file fully, even though I know the file is downloaded and extracted properly, eg it stops at 52 records when I know there are over 2,000 items, lol it then doesnt import to mysql or it imports one line then dies

Please please someone help im seeing commas flying around everywhere and im going nuts...

NogDog

c9679 wrote:
...Anyway the problem with the files is most fields are seperated by , commas however some fields also contain commas in them but these fields are enclosed in " Quotes.

This is proper CSV formatting. If you are using a function such as PHP's fgetcsv() to read in the text from the file, this should not be a problem.

Also on some rows where not all fields are used they are just left blank with no ending commas

eg Product file contains 100 fields
eg Product spec file contains 51 fields

Spec file - Fields: Model, S1, S2, S3, S4,...
Content
M1, s, s, s, s, s, s, s, s...
M2, r, t
M3, d, d, r, ddds, "dsd, sssfd", 43
...

You could do a count() of the array returned by fgetcsv(), and if it's less than the number of fields you require, do a for loop for that number of iterations to add that many NULL elements to the array.

c9679

Hello,

I did try using fgetcsv() however this stopped after a few records for some reason, it reads some of the file then seems to just stop... I also did a for loop using count to add the number of missing commas, this did add the number of columns however for some reason it does still stop after a while and the problem is some of the quoted fields have commas in them too. So the field count sometimes exceeds the max field count.

I tried searching for occurances of ',"' and '",' and then splitting the line by taking out that field all together

Split string into $left, $right, $middle

strip " from $middle

insert into database $left, $right, $middle