Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
When I worked for a data prep house (Late 1980s) we used to use pipe delimited files and wrote a C utility to strip files back removing the local oddities.
Excel etc can import using various versions if you are using the GUI, not sure about actually coding it but a short fox pro routing could read a line at a time and replace commas with escaped commas and then replace semi colons with commas.
On 16 Apr 2021, at 14:42, Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Thanks, Adam. I'm thinking we'll probably have to go down the road of pre-parsing the CSV to determine what delimiters are being used and then converting as needed.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Adam Buckland Sent: Friday, April 16, 2021 10:01 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
When I worked for a data prep house (Late 1980s) we used to use pipe delimited files and wrote a C utility to strip files back removing the local oddities.
Excel etc can import using various versions if you are using the GUI, not sure about actually coding it but a short fox pro routing could read a line at a time and replace commas with escaped commas and then replace semi colons with commas.
On 16 Apr 2021, at 14:42, Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Hi Richard,
Not my code but the program we used to parse csv to pipe in 1988..
#include <stdio.h>
main() { char in[1024]; int i,k,l;
while ( gets(&in[0])) { l = 0; k = strlen(&in[0]); for ( i = 0 ; i < k ; i++) switch(in[i]) { case '"' : if ( l == 0) l = 1; else l = 0; break;
case ',' : if (l==1) putchar(in[i]); else putchar('|'); break; default : putchar (in[i]); break; } putchar('\n'); } }
On 16 Apr 2021, at 15:17, Richard Kaye rkaye@invaluable.com wrote:
Thanks, Adam. I'm thinking we'll probably have to go down the road of pre-parsing the CSV to determine what delimiters are being used and then converting as needed.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Adam Buckland Sent: Friday, April 16, 2021 10:01 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
When I worked for a data prep house (Late 1980s) we used to use pipe delimited files and wrote a C utility to strip files back removing the local oddities.
Excel etc can import using various versions if you are using the GUI, not sure about actually coding it but a short fox pro routing could read a line at a time and replace commas with escaped commas and then replace semi colons with commas.
On 16 Apr 2021, at 14:42, Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Thanks. I'd be inclined to write it in pure VFP. 😊 There are plenty of text manipulation functions that could get the job done. It's just going to be a refactoring job for any place where we import from CSV if there are no other magic bullets besides changing Windows settings. It's a shame that Excel tightly couples those settings to the OS...
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Adam Buckland Sent: Friday, April 16, 2021 10:21 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
Hi Richard,
Not my code but the program we used to parse csv to pipe in 1988..
#include <stdio.h>
main() { char in[1024]; int i,k,l;
while ( gets(&in[0])) { l = 0; k = strlen(&in[0]); for ( i = 0 ; i < k ; i++) switch(in[i]) { case '"' : if ( l == 0) l = 1; else l = 0; break;
case ',' : if (l==1) putchar(in[i]); else putchar('|'); break; default : putchar (in[i]); break; } putchar('\n'); } }
On 16 Apr 2021, at 15:17, Richard Kaye rkaye@invaluable.com wrote:
Thanks, Adam. I'm thinking we'll probably have to go down the road of pre-parsing the CSV to determine what delimiters are being used and then converting as needed.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Adam Buckland Sent: Friday, April 16, 2021 10:01 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
When I worked for a data prep house (Late 1980s) we used to use pipe delimited files and wrote a C utility to strip files back removing the local oddities.
Excel etc can import using various versions if you are using the GUI, not sure about actually coding it but a short fox pro routing could read a line at a time and replace commas with escaped commas and then replace semi colons with commas.
On 16 Apr 2021, at 14:42, Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/B4B2C63D-209D-4CD0-A27F-50FEAC9FE3D3@thebuc... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Report [OT] Abuse: http://leafe.com/reportAbuse/B4B2C63D-209D-4CD0-A27F-50FEAC9FE3D3@thebucklan...
Richard,
I remember talking with Jean-Marie Laeremans about troubles he had with imports. Different rows of data had a different number of delimiters. He probably has some stories to tell about those days. Though I don't know how traumatic it was for him.
Tracy
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Friday, April 16, 2021 10:18 AM To: profoxtech@leafe.com Subject: RE: Variations in CSV settings by region
Thanks, Adam. I'm thinking we'll probably have to go down the road of pre-parsing the CSV to determine what delimiters are being used and then converting as needed.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Adam Buckland Sent: Friday, April 16, 2021 10:01 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
When I worked for a data prep house (Late 1980s) we used to use pipe delimited files and wrote a C utility to strip files back removing the local oddities.
Excel etc can import using various versions if you are using the GUI, not sure about actually coding it but a short fox pro routing could read a line at a time and replace commas with escaped commas and then replace semi colons with commas.
On 16 Apr 2021, at 14:42, Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
+1 for the pipe delimiter and as stated beware of embedded single or double quotes if you intend to format text strings using ' or".
This is a massive gotcha which can sometimes only appear in isolated circumstances but can cause havoc, as can embedded special character fields when the input data has been modified using "alt.. NNN" keyboard entries such as the French circumflex, acute and grave symbols.
XML is by far the best way to transfer data as it is well supported in most packages but be sure to get the schema correct first!
You could also look at serialising the data in JSON format!
Best of luck.
Dave Crozier Software Development Manager Flexipol Packaging Ltd.
Mob: 07967 671080
﴾⚆ᨎ⚆﴿
Flexipol® Packaging Ltd T 01706 222 792 E DCrozier@flexipol.co.uk W https://www.flexipol.co.uk/ Follow us: Unit 14 Bentwood Road, Carrs Industrial Estate, Haslingden, Lancashire, BB4 5HH
​This communication and the information it contains is intended for the person or organisation to whom it is addressed. Its contents are confidential and may be protected in law. If you have received this e-mail in error you must not copy, distribute or take any action in reliance on it. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to minimise the risk of virus transmission through email and therefore any files sent via e-mail will have been checked for known viruses. However, you are advised to run your own virus check before opening any attachments received as Flexipol Packaging Ltd will not in any event accept any liability whatsoever once an e-mail and/or any attachment is received.
It is the responsibility of the recipient to ensure that they have adequate virus protection.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ​​ ​Terms & Conditions: Notwithstanding delivery and the passing of risk in the goods, the property in the goods shall not pass to the buyer until the seller Flexipol Packaging Ltd. ("The Company") has received in cash or cleared funds payment in full of the price of the goods and all other goods agreed to be sold by the seller to the buyer for which payment is then due. Until such time as the property in the goods passes to the buyer, the buyer shall hold the goods as the seller's fiduciary agent and bailee and keep the goods separate from those of the buyer and third parties and properly stored protected and insured and identified as the seller's property but shall be entitled to resell or use the goods in the ordinary course of its business. Until such time as the property in the goods passes to the buyer the seller shall be entitled at any time -----Original Message----- From: ProFox profox-bounces@leafe.com On Behalf Of Adam Buckland Sent: 16 April 2021 15:01 To: ProFox Email List profox@leafe.com Subject: Re: Variations in CSV settings by region
When I worked for a data prep house (Late 1980s) we used to use pipe delimited files and wrote a C utility to strip files back removing the local oddities.
Excel etc can import using various versions if you are using the GUI, not sure about actually coding it but a short fox pro routing could read a line at a time and replace commas with escaped commas and then replace semi colons with commas.
On 16 Apr 2021, at 14:42, Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
We did a lot of this in the 90s and aughts, with customers as far away as Louisiana and Indiana ;) and no one agrees on what CSV "standard" means. You could go the "AI" way of performing a FileToStr() and trying to parse out what the creator intended, but I think that might be overkill, and dates are ambiguous, even on a good day. "3/8/20" means... ?
Our customers were in a similar situation: their customers were shipping them price lists and inventory lists in every format known. When they would explain they needed the format to be stable, the next month's sheets would show up with new columns added (and sometimes hidden!) and others re-arranged. We ended up leaving it to our customers to use Excel to import what they were sent, and reformat it as needed to a standard format, and exporting THAT as a strict CSV to import into the system. It was too much work to reinvent the wheel already built into Excel.
"Be liberal in what you accept, but strict in what you emit" -- Postel's Law, roughly. (https://en.wikipedia.org/wiki/Robustness_principle)
On Fri, Apr 16, 2021 at 9:42 AM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Hey Ted! It's snowing here in lovely MA right now... Happy April!
I agree but it's not so much the users getting creative with the required formats (well, generally speaking...). The issue is what when the Belgian user exports the data from Excel, it's going to default to semis and no quotes and then our current import routine will choke. Ultimately I think I'm going to have to build a pre-import parsing method.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: Friday, April 16, 2021 10:26 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
We did a lot of this in the 90s and aughts, with customers as far away as Louisiana and Indiana ;) and no one agrees on what CSV "standard" means. You could go the "AI" way of performing a FileToStr() and trying to parse out what the creator intended, but I think that might be overkill, and dates are ambiguous, even on a good day. "3/8/20" means... ?
Our customers were in a similar situation: their customers were shipping them price lists and inventory lists in every format known. When they would explain they needed the format to be stable, the next month's sheets would show up with new columns added (and sometimes hidden!) and others re-arranged. We ended up leaving it to our customers to use Excel to import what they were sent, and reformat it as needed to a standard format, and exporting THAT as a strict CSV to import into the system. It was too much work to reinvent the wheel already built into Excel.
"Be liberal in what you accept, but strict in what you emit" -- Postel's Law, roughly. (https://en.wikipedia.org/wiki/Robustness_principle)
On Fri, Apr 16, 2021 at 9:42 AM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
IÂ have never seen a standard when it comes to CSV. I have stopped using Excel and use Libre Office and when I try to save a file as CSV it prompts for Character set, field delimiter and String delimiter.
Would the issue go away if you specified a tab as your field delimiter and no string delimiter? Or do your customers have no control over the production of the files?
Frank.
Frank Cazabon
On 16/04/2021 10:33 am, Richard Kaye wrote:
Hey Ted! It's snowing here in lovely MA right now... Happy April!
I agree but it's not so much the users getting creative with the required formats (well, generally speaking...). The issue is what when the Belgian user exports the data from Excel, it's going to default to semis and no quotes and then our current import routine will choke. Ultimately I think I'm going to have to build a pre-import parsing method.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: Friday, April 16, 2021 10:26 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
We did a lot of this in the 90s and aughts, with customers as far away as Louisiana and Indiana ;) and no one agrees on what CSV "standard" means. You could go the "AI" way of performing a FileToStr() and trying to parse out what the creator intended, but I think that might be overkill, and dates are ambiguous, even on a good day. "3/8/20" means... ?
Our customers were in a similar situation: their customers were shipping them price lists and inventory lists in every format known. When they would explain they needed the format to be stable, the next month's sheets would show up with new columns added (and sometimes hidden!) and others re-arranged. We ended up leaving it to our customers to use Excel to import what they were sent, and reformat it as needed to a standard format, and exporting THAT as a strict CSV to import into the system. It was too much work to reinvent the wheel already built into Excel.
"Be liberal in what you accept, but strict in what you emit" -- Postel's Law, roughly. (https://en.wikipedia.org/wiki/Robustness_principle)
On Fri, Apr 16, 2021 at 9:42 AM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
HI Tracy,
Those were the days. Files came from a mf and those guys lived in a different universe. I ended up writing a proc. which examined the complete file and rejected for the slighted non compliance with my guidelines. They quickly learned. btw there is NO Belgian version of cvs files. I ended up using ~
On Fri, Apr 16, 2021 at 4:46 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I have never seen a standard when it comes to CSV. I have stopped using Excel and use Libre Office and when I try to save a file as CSV it prompts for Character set, field delimiter and String delimiter.
Would the issue go away if you specified a tab as your field delimiter and no string delimiter? Or do your customers have no control over the production of the files?
Frank.
Frank Cazabon
On 16/04/2021 10:33 am, Richard Kaye wrote:
Hey Ted! It's snowing here in lovely MA right now... Happy April!
I agree but it's not so much the users getting creative with the
required formats (well, generally speaking...). The issue is what when the Belgian user exports the data from Excel, it's going to default to semis and no quotes and then our current import routine will choke. Ultimately I think I'm going to have to build a pre-import parsing method.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: Friday, April 16, 2021 10:26 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
We did a lot of this in the 90s and aughts, with customers as far away
as Louisiana and Indiana ;) and no one agrees on what CSV "standard" means.
You could go the "AI" way of performing a FileToStr() and trying to
parse out what the creator intended, but I think that might be overkill, and dates are ambiguous, even on a good day. "3/8/20" means... ?
Our customers were in a similar situation: their customers were shipping
them price lists and inventory lists in every format known. When they would explain they needed the format to be stable, the next month's sheets would show up with new columns added (and sometimes hidden!) and others re-arranged. We ended up leaving it to our customers to use Excel to import what they were sent, and reformat it as needed to a standard format, and exporting THAT as a strict CSV to import into the system. It was too much work to reinvent the wheel already built into Excel.
"Be liberal in what you accept, but strict in what you emit" -- Postel's
Law, roughly. (https://en.wikipedia.org/wiki/Robustness_principle)
On Fri, Apr 16, 2021 at 9:42 AM Richard Kaye rkaye@invaluable.com
wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes
around text elements.
But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you
use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Thanks for chiming in, Jeans. I believe you are more of an authority than me on what's Belgian... 😊
Having said that, I've done Zoom calls with my tech savvy client there. When he picks CSV as the output type in Excel, it uses semi-colons and no double quotes in the resulting file. Change the regional settings in Windows to US, commas and double quotes come out.
Writing a validator class is probably the best way to go and thanks to everyone for adding their tips and tricks. As always, ProFox is the place to hang out.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Jean Laeremans Sent: Friday, April 16, 2021 11:10 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
HI Tracy,
Those were the days. Files came from a mf and those guys lived in a different universe. I ended up writing a proc. which examined the complete file and rejected for the slighted non compliance with my guidelines. They quickly learned. btw there is NO Belgian version of cvs files. I ended up using ~
On Fri, Apr 16, 2021 at 4:46 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I have never seen a standard when it comes to CSV. I have stopped using Excel and use Libre Office and when I try to save a file as CSV it prompts for Character set, field delimiter and String delimiter.
Would the issue go away if you specified a tab as your field delimiter and no string delimiter? Or do your customers have no control over the production of the files?
Frank.
Well, technically there is a defined standard for CSV but how well various applications conform to it is another question, Frank. The problem is the customer has full control over what they do but generally none of them are tech geeks like us. 😊
We have well-defined import column definitions in our application or in some cases we are consuming known formats coming from 3rd parties. But we are very US-centric and that doesn't really help our international customers in regions where the field and text delimiters are different. Most of them have enough Excel skills to do a Save As. Yes, we could tell them to use different formats, like TAB delimited or straight-up XLSX, etc. But then I'm rewriting the import stuff which was built to use the allegedly standard CSV format. Before doing that I figured ask the ProFox community about what how they have handled this.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Frank Cazabon Sent: Friday, April 16, 2021 10:45 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
IÂ have never seen a standard when it comes to CSV. I have stopped using Excel and use Libre Office and when I try to save a file as CSV it prompts for Character set, field delimiter and String delimiter.
Would the issue go away if you specified a tab as your field delimiter and no string delimiter? Or do your customers have no control over the production of the files?
Frank.
The problem is mainly due to the symbol used as a decimal separator. In most of the European countries, that will be the comma, not a period. Separating values with commas won't work well in this scenario, hence the need to use a different symbol to separate values (HTab, semi-colons, something else). Imagine how a "C"SV document would look like if the value separator was a period.
This type of problem is addressed in the CSVProcessor class. To handle a typical European scenario:
m.CSV = CREATEOBJECT("CSVProcessor") m.CSV.ValueSeparator = ";" m.CSV.DecimalPoint = "," * other properties that may be important * .WorkArea, .HeaderRow, .SkipRows, .DatePattern, .NullValue, ... m.Result = m.CSV.Import(GETFILE("csv"))
On Fri, Apr 16, 2021 at 5:27 PM Richard Kaye rkaye@invaluable.com wrote:
Well, technically there is a defined standard for CSV but how well various applications conform to it is another question, Frank. The problem is the customer has full control over what they do but generally none of them are tech geeks like us. 😊
We have well-defined import column definitions in our application or in some cases we are consuming known formats coming from 3rd parties. But we are very US-centric and that doesn't really help our international customers in regions where the field and text delimiters are different. Most of them have enough Excel skills to do a Save As. Yes, we could tell them to use different formats, like TAB delimited or straight-up XLSX, etc. But then I'm rewriting the import stuff which was built to use the allegedly standard CSV format. Before doing that I figured ask the ProFox community about what how they have handled this.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Frank Cazabon Sent: Friday, April 16, 2021 10:45 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
I have never seen a standard when it comes to CSV. I have stopped using Excel and use Libre Office and when I try to save a file as CSV it prompts for Character set, field delimiter and String delimiter.
Would the issue go away if you specified a tab as your field delimiter and no string delimiter? Or do your customers have no control over the production of the files?
Frank.
[excessive quoting removed by server]
Excellent point about the decimal separator.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of António Tavares Lopes Sent: Friday, April 16, 2021 12:48 PM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
The problem is mainly due to the symbol used as a decimal separator. In most of the European countries, that will be the comma, not a period. Separating values with commas won't work well in this scenario, hence the need to use a different symbol to separate values (HTab, semi-colons, something else). Imagine how a "C"SV document would look like if the value separator was a period.
This type of problem is addressed in the CSVProcessor class. To handle a typical European scenario:
m.CSV = CREATEOBJECT("CSVProcessor") m.CSV.ValueSeparator = ";" m.CSV.DecimalPoint = "," * other properties that may be important * .WorkArea, .HeaderRow, .SkipRows, .DatePattern, .NullValue, ... m.Result = m.CSV.Import(GETFILE("csv"))
On Fri, Apr 16, 2021 at 5:27 PM Richard Kaye rkaye@invaluable.com wrote:
Well, technically there is a defined standard for CSV but how well various applications conform to it is another question, Frank. The problem is the customer has full control over what they do but generally none of them are tech geeks like us. 😊
We have well-defined import column definitions in our application or in some cases we are consuming known formats coming from 3rd parties. But we are very US-centric and that doesn't really help our international customers in regions where the field and text delimiters are different. Most of them have enough Excel skills to do a Save As. Yes, we could tell them to use different formats, like TAB delimited or straight-up XLSX, etc. But then I'm rewriting the import stuff which was built to use the allegedly standard CSV format. Before doing that I figured ask the ProFox community about what how they have handled this.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Frank Cazabon Sent: Friday, April 16, 2021 10:45 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
I have never seen a standard when it comes to CSV. I have stopped using Excel and use Libre Office and when I try to save a file as CSV it prompts for Character set, field delimiter and String delimiter.
Would the issue go away if you specified a tab as your field delimiter and no string delimiter? Or do your customers have no control over the production of the files?
Frank.
[excessive quoting removed by server]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/CADj74tFyYuuC1gD4UvksXfjCuSNvVjr5Sz8hHid3O1... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. Report [OT] Abuse: http://leafe.com/reportAbuse/CADj74tFyYuuC1gD4UvksXfjCuSNvVjr5Sz8hHid3O1rDN-...
Same here an hour north of the Mass border, freezing rain & snow all night, accumulating as the temp drops today. Ah, nothing like a New England spring!
I should have done a web search on Belgian Excel CSV and semi-colons, it's a mess, as you indicated.
It's called "comma-separated values" for a reason, but the MS Excel devs knew better, of course. Whoever was in charge of Begian regionalization of Excel got carried away, it appears.
Highly recommended: Tamar's article on parsing, "Breaking Up is Not Hard To Do" (groan!): http://www.tomorrowssolutionsllc.com/Articles/Breaking%20Up%20is%20Not%20Har...
VFP has plenty of good text-parsing tools, as long as you can find consistent rules to work out, or at worst, FileToStr() the file and parse the string a character at a time.
On Fri, Apr 16, 2021 at 10:33 AM Richard Kaye rkaye@invaluable.com wrote:
Hey Ted! It's snowing here in lovely MA right now... Happy April!
I agree but it's not so much the users getting creative with the required formats (well, generally speaking...). The issue is what when the Belgian user exports the data from Excel, it's going to default to semis and no quotes and then our current import routine will choke. Ultimately I think I'm going to have to build a pre-import parsing method.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: Friday, April 16, 2021 10:26 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
We did a lot of this in the 90s and aughts, with customers as far away as Louisiana and Indiana ;) and no one agrees on what CSV "standard" means. You could go the "AI" way of performing a FileToStr() and trying to parse out what the creator intended, but I think that might be overkill, and dates are ambiguous, even on a good day. "3/8/20" means... ?
Our customers were in a similar situation: their customers were shipping them price lists and inventory lists in every format known. When they would explain they needed the format to be stable, the next month's sheets would show up with new columns added (and sometimes hidden!) and others re-arranged. We ended up leaving it to our customers to use Excel to import what they were sent, and reformat it as needed to a standard format, and exporting THAT as a strict CSV to import into the system. It was too much work to reinvent the wheel already built into Excel.
"Be liberal in what you accept, but strict in what you emit" -- Postel's Law, roughly. (https://en.wikipedia.org/wiki/Robustness_principle)
On Fri, Apr 16, 2021 at 9:42 AM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes
around text elements.
But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you
use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
There was probably a big internal push on at Microsoft at the time to create as many dependencies between Office applications and Windows as possible... 😉
I'll check Tamar's article. Thanks for the link.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: Friday, April 16, 2021 12:01 PM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
Same here an hour north of the Mass border, freezing rain & snow all night, accumulating as the temp drops today. Ah, nothing like a New England spring!
I should have done a web search on Belgian Excel CSV and semi-colons, it's a mess, as you indicated.
It's called "comma-separated values" for a reason, but the MS Excel devs knew better, of course. Whoever was in charge of Begian regionalization of Excel got carried away, it appears.
Highly recommended: Tamar's article on parsing, "Breaking Up is Not Hard To Do" (groan!): http://www.tomorrowssolutionsllc.com/Articles/Breaking%20Up%20is%20Not%20Har...
VFP has plenty of good text-parsing tools, as long as you can find consistent rules to work out, or at worst, FileToStr() the file and parse the string a character at a time.
On Fri, Apr 16, 2021 at 10:33 AM Richard Kaye rkaye@invaluable.com wrote:
Hey Ted! It's snowing here in lovely MA right now... Happy April!
I agree but it's not so much the users getting creative with the required formats (well, generally speaking...). The issue is what when the Belgian user exports the data from Excel, it's going to default to semis and no quotes and then our current import routine will choke. Ultimately I think I'm going to have to build a pre-import parsing method.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: Friday, April 16, 2021 10:26 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
We did a lot of this in the 90s and aughts, with customers as far away as Louisiana and Indiana ;) and no one agrees on what CSV "standard" means. You could go the "AI" way of performing a FileToStr() and trying to parse out what the creator intended, but I think that might be overkill, and dates are ambiguous, even on a good day. "3/8/20" means... ?
Our customers were in a similar situation: their customers were shipping them price lists and inventory lists in every format known. When they would explain they needed the format to be stable, the next month's sheets would show up with new columns added (and sometimes hidden!) and others re-arranged. We ended up leaving it to our customers to use Excel to import what they were sent, and reformat it as needed to a standard format, and exporting THAT as a strict CSV to import into the system. It was too much work to reinvent the wheel already built into Excel.
"Be liberal in what you accept, but strict in what you emit" -- Postel's Law, roughly. (https://en.wikipedia.org/wiki/Robustness_principle)
On Fri, Apr 16, 2021 at 9:42 AM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes
around text elements.
But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you
use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/CACW6n4s1a_cb=LXwWF_X2gN=tKx=x36HNqMAXk-M1M... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Report [OT] Abuse: http://leafe.com/reportAbuse/CACW6n4s1a_cb=LXwWF_X2gN=tKx=x36HNqMAXk-M1MN5pB...
Richard,
You may have a look at the CSVProcessor class. It's capable of handling regional differences in the format, the contents, and even the encoding of CSV files (plus a few other features like import more than 254 columns, or column mapping and filtering, ...).
Source and docs at https://github.com/atlopes/csv
On Fri, Apr 16, 2021 at 2:42 PM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
No, it's an SCSV 😊
John Weller 07976 393631
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Alan Bourke Sent: 16 April 2021 16:47 To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
On Fri, 16 Apr 2021, at 2:42 PM, Richard Kaye wrote:
But in Belgium, the delimiter is the semi-colon
It's not a CSV file then ;)
Use LibreOffice Calc instead of Excel: when opening/importing a CSV or pasting text with delimited data, opens a panel with plenty of options.
Gianni
On Fri, 16 Apr 2021 13:42:10 +0000, Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
Thanks, Gianni. I can't really dictate to my customers what spreadsheet application to use.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Gianni Turri Sent: Friday, April 16, 2021 6:30 PM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
Use LibreOffice Calc instead of Excel: when opening/importing a CSV or pasting text with delimited data, opens a panel with plenty of options.
Gianni
Hi Kaye,
you are right.
But I couldn't believe Excel didn't have CSV import options like LibreOffice Calc does... So I looked better and found them!
These options are not available when opening a CSV file directly, like in LibreOffice Calc, instead you have to go through this:
- select the "Data" tab on the ribbon - select "From Text/CSV" in the "Get & Transform Data" section
Gianni
On Sat, 17 Apr 2021 10:39:00 +0000, Richard Kaye rkaye@invaluable.com wrote:
Thanks, Gianni. I can't really dictate to my customers what spreadsheet application to use.
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Gianni Turri Sent: Friday, April 16, 2021 6:30 PM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
Use LibreOffice Calc instead of Excel: when opening/importing a CSV or pasting text with delimited data, opens a panel with plenty of options.
Gianni
Thanks for poking at this. There's another nuance here in that the user could have a file with the proper columns, etc. received from a 3rd party source but it has the delimiters appropriate to their region, and they simply submit it directly to our import process without first going through Excel. In that case I still need to recognize it's using semi-colons. The ideal process for the user is I just convert it as needed without making them pre-process via Excel or Libre, etc.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Gianni Turri Sent: Saturday, April 17, 2021 9:51 AM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
Hi Kaye,
you are right.
But I couldn't believe Excel didn't have CSV import options like LibreOffice Calc does... So I looked better and found them!
These options are not available when opening a CSV file directly, like in LibreOffice Calc, instead you have to go through this:
- select the "Data" tab on the ribbon - select "From Text/CSV" in the "Get & Transform Data" section
Gianni
On Sat, 17 Apr 2021 10:39:00 +0000, Richard Kaye rkaye@invaluable.com wrote:
Thanks, Gianni. I can't really dictate to my customers what spreadsheet application to use.
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Gianni Turri Sent: Friday, April 16, 2021 6:30 PM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
Use LibreOffice Calc instead of Excel: when opening/importing a CSV or pasting text with delimited data, opens a panel with plenty of options.
Gianni
[excessive quoting removed by server]
I'd take the CSV file and save it as XML. Then rip from there.
Not sure if there is a json reader/writer for VFP yet.
if you look to sql server you could import the data through the IMPORT wizard as well. You tell it the delimiters etc per file.
On Fri, Apr 16, 2021 at 8:42 AM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Thanks, Stephen. Another nuance missed here; the import routines are part of the application. I can do all and any of these things myself very easily in my dev environment. The quest for ideas was around programmatic approaches that could be integrated into my application workflows. I want the computer to do the work. 😊
Yes, there are plenty of options for handling json in VFP. That's been part of the Westwind framework/toolkits for years. There are also some open source classes available from the VFPx Github repo.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Saturday, April 17, 2021 2:38 PM To: profoxtech@leafe.com Subject: Re: Variations in CSV settings by region
I'd take the CSV file and save it as XML. Then rip from there.
Not sure if there is a json reader/writer for VFP yet.
if you look to sql server you could import the data through the IMPORT wizard as well. You tell it the delimiters etc per file.
On Fri, Apr 16, 2021 at 8:42 AM Richard Kaye rkaye@invaluable.com wrote:
Throwing this one out to the collective wisdom. We're doing a lot with CSV import/experts these days with our web-based WWC application and are running into issues with regionalization. Here in the US, a "standard" CSV means commas between data elements and double quotes around text elements. But in Belgium, the delimiter is the semi-colon and text elements are not wrapped in double quotes. As best I can tell, Excel determines what format to use by the OS settings and not its own application settings. This makes importing from a CSV a bit of a dance for our Belgian clients as they have to change their regional settings, import the file that was received in US format, and then change their settings back. And, of course, changing region affects date and currency formats. For those of you working with clients from multiple locations where the standards may be different, what strategies do you use to deal with this?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/CAJidMYJGvsup_znY_zeQTb=RAkEgeRQ2kBqg8zvrL=... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Report [OT] Abuse: http://leafe.com/reportAbuse/CAJidMYJGvsup_znY_zeQTb=RAkEgeRQ2kBqg8zvrL=i_Hw...
-
Adam Buckland -
Alan Bourke -
António Tavares Lopes -
Dave Crozier -
Frank Cazabon -
Gianni Turri -
Jean Laeremans -
John Weller -
Richard Kaye -
Stephen Russell -
Ted Roche -
Tracy Pearson