guyfromfl Posted January 26, 2011 Share Posted January 26, 2011 I am having trouble parsing data that is separated by comas in an XLS file. The upload and parsing scripts work beautifully, but the problem I am having is the data is read in from one cell (all 5 fields for the row are in column A) I am exploding it by , but some of the cells contain comas. For example a cell might contain "jim,jones,12345678,jim@jones.com,More, Data,192.168.1.1" but the next one might be "Dave,Thomas,98765432,dave@wendys.com,something else, 255.255.255.0" The problem I am having is More, Data should be one cell. Not all position 4 will have a , so I can't just add it back because the IP address would be appended to more... Any ideas? I hope thats clear enough... Quote Link to comment Share on other sites More sharing options...
jaikob Posted January 26, 2011 Share Posted January 26, 2011 I am having trouble parsing data that is separated by comas in an XLS file. The upload and parsing scripts work beautifully, but the problem I am having is the data is read in from one cell (all 5 fields for the row are in column A) I am exploding it by , but some of the cells contain comas. For example a cell might contain "jim,jones,12345678,jim@jones.com,More, Data,192.168.1.1" but the next one might be "Dave,Thomas,98765432,dave@wendys.com,something else, 255.255.255.0" The problem I am having is More, Data should be one cell. Not all position 4 will have a , so I can't just add it back because the IP address would be appended to more... Any ideas? I hope thats clear enough... There is no possible way to separate the data without checking every character and setting standards. You could try a CSV. You can save as a CSV in Excel and PHP has a wide array of functions to natively read CSV files. Edit.... Unless, the excel file is separated by a comma and a tab. in theory I think you can use explode() and use ",\t" as your delimiter. Quote Link to comment Share on other sites More sharing options...
guyfromfl Posted January 31, 2011 Author Share Posted January 31, 2011 Yea thats what I was affraid of... This is another one of those "trying to make it so computer idiots can use it type of thing" The script detects if it is a CSV or XLS file and works appropriately. My next option was to make the script "smarter" by validating the type of data and putting it in the correct element accordingly. Sounds like the only somewhat doable way to get around this problem. Thanks for the input. Quote Link to comment Share on other sites More sharing options...
The Letter E Posted January 31, 2011 Share Posted January 31, 2011 Yea dude. I was parsing some crazy csv's that had data running in different directions on the same file. Also had tabs that linked to higher level csv tiers. Bad News... It's a mess, dude. A big freakin' mess. I had a crazy enigma of nested while loops running preg_matches and SQL queries. Good news...there is none. Sorry It would be better if you can teach whoever is making the files to just make csv's with columns being the field and rows being new records, just like mysql. Then it's cake Good luck, I mean it. E Quote Link to comment Share on other sites More sharing options...
guyfromfl Posted February 2, 2011 Author Share Posted February 2, 2011 It would be better if you can teach whoever is making the files to just make csv's with columns being the field and rows being new records, just like mysql. Then it's cake Good luck, I mean it. E E, yea man I wish I could but the CSV's come from vendors and customers, so I'm kinda tied there. Thanks for the input! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.