POS fudges importing its own export file.
Posted: Mon Sep 05, 2011 12:01 am
Hey Dale,
I came across this today while trying to set up a new register in my store. Seems if you export the stock table, then try and import it (into an empty table on other register) the 'Vendor Stock Number' field gets corrupt with a quote mark for any record that did not have a value in 'Vendor Stock Number' field. This only happens in the 'Vendor Stock Number' field (The 'Description' field is well behaved and not a problem).
I checked how Excel saves CSV files and it seems that it only places quotes around text that contains commas. If no commas, it just places the text by its self. So there are no empty quotes.
Example of Excel 'Save as' CSV.
Example of POS Export 'Save as'. Notice blank 'Vendor Stock Number' gets exported with empty quotesOn import the (empty) double quotes will place a quote in the 'Vendor Stock Number' field.
Having no meaningful life I played around with this infamous quotes with DHPOS Export feature to see how else it differed from the standard CSV format.
I noticed that if quotes are used in a text field, DHPOS Export feature converts the quotes(ANSI 34) to TWO apostrophes(ANSI 39). This does not follow a standard SCV format of doubling the quotes and then wrap the text in quotes. A description of WIDGET "BIG" gets exported to a standard SCV format as "WIDGET ""BIG""". However DHPOS exports the same data as "WIDGET ''BIG''". So if you export data with quotes out of POS and try to import it back in, what was quote, now imports as 2 apostrophes. Doesn't seem good that what just came out won't go back in the same way.
Warning! This causes work for Dale:
Because the DHPOS Import works correctly with an Excel CSV formatted file, It seems that the fix may be to change the DHPOS Export to match the rules of standard CSV format (like Excel follows) by not placing text inside double quote unless it contains commas. And if it contains quotes then double them up and then place within a set of quotes. This keeps a 'Save As' in DHPOS the same as a 'Save As' in Excel and both will import properly. Then DHPOS Import would need to be adjusted to handle the double quoted words.
This causes LESS work for Dale:
Fix the DHPOS Import to handle the DHPOS format of empty 'Vendor Stock Number' as it does for the 'Description' field. so an empty field imports as blank and not a quote mark. Then rant to yourself how stupid users are for placing quotes in a field.
This causes NO work for Dale:
First, Ignore the problem that an export file from POS with blank 'Vendor Stock Number' will import back as a quote mark instead of blank.
Second, Tell users they are stupid for using quotes in a description and therefore they deserve the aggravation trying to export/import the data.
Thanks Dale for all you do.
I came across this today while trying to set up a new register in my store. Seems if you export the stock table, then try and import it (into an empty table on other register) the 'Vendor Stock Number' field gets corrupt with a quote mark for any record that did not have a value in 'Vendor Stock Number' field. This only happens in the 'Vendor Stock Number' field (The 'Description' field is well behaved and not a problem).
I checked how Excel saves CSV files and it seems that it only places quotes around text that contains commas. If no commas, it just places the text by its self. So there are no empty quotes.
Example of Excel 'Save as' CSV.
Code: Select all
111,One,1,,1,,,,,1,1,1,1,asd
222,Two,1,,1,,,,,1,1,1,1,
333,,1,,1,,,,,1,1,1,1,"fgh, ffff"Code: Select all
111,"One",1,,1,,,,,1,1,1,1,"asd"
222,"Two",1,,1,,,,,1,1,1,1,""
333,"",1,,1,,,,,1,1,1,1,"fgh, ffff"Having no meaningful life I played around with this infamous quotes with DHPOS Export feature to see how else it differed from the standard CSV format.
I noticed that if quotes are used in a text field, DHPOS Export feature converts the quotes(ANSI 34) to TWO apostrophes(ANSI 39). This does not follow a standard SCV format of doubling the quotes and then wrap the text in quotes. A description of WIDGET "BIG" gets exported to a standard SCV format as "WIDGET ""BIG""". However DHPOS exports the same data as "WIDGET ''BIG''". So if you export data with quotes out of POS and try to import it back in, what was quote, now imports as 2 apostrophes. Doesn't seem good that what just came out won't go back in the same way.
Warning! This causes work for Dale:
Because the DHPOS Import works correctly with an Excel CSV formatted file, It seems that the fix may be to change the DHPOS Export to match the rules of standard CSV format (like Excel follows) by not placing text inside double quote unless it contains commas. And if it contains quotes then double them up and then place within a set of quotes. This keeps a 'Save As' in DHPOS the same as a 'Save As' in Excel and both will import properly. Then DHPOS Import would need to be adjusted to handle the double quoted words.
This causes LESS work for Dale:
Fix the DHPOS Import to handle the DHPOS format of empty 'Vendor Stock Number' as it does for the 'Description' field. so an empty field imports as blank and not a quote mark. Then rant to yourself how stupid users are for placing quotes in a field.
This causes NO work for Dale:
First, Ignore the problem that an export file from POS with blank 'Vendor Stock Number' will import back as a quote mark instead of blank.
Second, Tell users they are stupid for using quotes in a description and therefore they deserve the aggravation trying to export/import the data.
Thanks Dale for all you do.