Hi David
2 questions if I may.
1) I am loading data into into a table from a CSV file into a VARCHAR table but the data contains , and ( etc. Magic parser then allocates some lines to different cells as it sees the, as a seperator and maybe the brackets. The data is an extract from a databse in excel and then loaded in. How would you suggest the best way forward.
2) The second question is if i have a spreadsheet and after any change in column one to the product code it has subtotals. Is there ayway to get magicparser to exclude these on import and only take the data.
Do you have any instrucions on the site that show how to include and exclude what you can import and exclude.
F12069 BRAZILS DARK 8 X 180G TRAY M124
F12069 Total
F12123 MINT CREAM 4X3KG BULK P248
F12123 Total
F12125 POPPETS TOFFEE 4X3KG BULK BAGS L150
Thanks
Roy
Thanks David
Great software and Great Support.
Roy
Hi Roy,
Re: 1) - You need to check that you are using the correct "Format String" for the CSV file created by Excel. By default, Excel creates comma separated values with Quoted text, and so the format string would be "csv|44|0|34" (or "csv|44|1|34" if you are exporting the column names as a header row). You would use this in your code as such:
MagicParser_parse("file.csv","myRecordHandler","csv|44|0|34");If you still experience problems with this aproach, I would recommend exporting from Excel using a different separator character, such as TAB or (better still) the pipe ("|") character as this doesn't appear in the cell values and so won't cause the same sort of problems.
Re: 2) - If you have no method to exclude the subtotal rows from your export then this is best handled within your myRecordHandler() function, where you check that the record is valid before importing.
In your example; the valid rows would always have a value in the 3rd field in each record; whereas for the subtotal rows the 3rd value would be empty. Therefore, depending on what key value you are using in your code to access the 3rd parameter (this depends on the file format and your format string), you might achieve this as such:
function myRecordHandler($record){
// don't use record if not a valid row
if (!$record["FIELD3"]) return;
// rest of code
}
Hope this helps!
David.