RE: Variations in CSV settings by region
+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 dcroz...@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 On Behalf Of Adam Buckland
Sent: 16 April 2021 15:01
To: ProFox Email List
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 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/listi
Re: Variations in CSV settings by region
> But I couldn't believe Excel didn't have CSV import options like > LibreOffice Calc does... it absolutely does. -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ 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/f8cf712b-deb4-488b-9381-48fee01ee...@www.fastmail.com ** 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.
RE: Variations in CSV settings by region
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 On Behalf Of Stephen Russell Sent: Saturday, April 17, 2021 2:38 PM To: profoxt...@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 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=i_hww...@mail.gmail.com ** 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_hww...@mail.gmail.com ___ 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/dm5pr1001mb21406231089243f429267742d2...@dm5pr1001mb2140.namprd10.prod.outlook.com ** 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.
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 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=i_hww...@mail.gmail.com ** 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.
RE: Variations in CSV settings by region
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 On Behalf Of Gianni Turri Sent: Saturday, April 17, 2021 9:51 AM To: profoxt...@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 +, Richard Kaye wrote: Thanks, Gianni. I can't really dictate to my customers what spreadsheet application to use. -Original Message- From: ProfoxTech On Behalf Of Gianni Turri Sent: Friday, April 16, 2021 6:30 PM To: profoxt...@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] ___ 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/mwhpr1001mb2144704197c8121d92feb4f5d2...@mwhpr1001mb2144.namprd10.prod.outlook.com ** 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.
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 +, Richard Kaye wrote: Thanks, Gianni. I can't really dictate to my customers what spreadsheet application to use. -Original Message- From: ProfoxTech On Behalf Of Gianni Turri Sent: Friday, April 16, 2021 6:30 PM To: profoxt...@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 ___ 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/26pl7glkb00uv40fltvgr1bchlupk38...@4ax.com ** 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.
RE: Variations in CSV settings by region
Thanks, Gianni. I can't really dictate to my customers what spreadsheet application to use. -- rk -Original Message- From: ProfoxTech On Behalf Of Gianni Turri Sent: Friday, April 16, 2021 6:30 PM To: profoxt...@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 ___ 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/mwhpr1001mb2144e89523aee6577f130ad5d2...@mwhpr1001mb2144.namprd10.prod.outlook.com ** 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.
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 On Fri, 16 Apr 2021 13:42:10 +, Richard Kaye 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? ___ 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/qn3k7gd9cq800uu0tdgarfev7il3e96...@4ax.com ** 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.
RE: Variations in CSV settings by region
Excellent point about the decimal separator.
--
rk
-Original Message-
From: ProfoxTech On Behalf Of António Tavares
Lopes
Sent: Friday, April 16, 2021 12:48 PM
To: profoxt...@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 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 On Behalf Of Frank
> Cazabon
> Sent: Friday, April 16, 2021 10:45 AM
> To: profoxt...@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/cadj74tfyyuuc1gd4uvksxfjcusnvvjr5sz8hhid3o1rdn-a...@mail.gmail.com
** 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-a...@mail.gmail.com
___
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/mwhpr1001mb2144924a00bb577608a6212ad2...@mwhpr1001mb2144.namprd10.prod.outlook.com
** 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.
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 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 On Behalf Of Frank Cazabon
> Sent: Friday, April 16, 2021 10:45 AM
> To: profoxt...@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/cadj74tfyyuuc1gd4uvksxfjcusnvvjr5sz8hhid3o1rdn-a...@mail.gmail.com
** 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.
RE: Variations in CSV settings by region
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 On Behalf Of Ted Roche Sent: Friday, April 16, 2021 12:01 PM To: profoxt...@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%20Hard%20to%20Do.pdf 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 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 On Behalf Of Ted Roche > Sent: Friday, April 16, 2021 10:26 AM > To: profoxt...@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 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/ar
RE: Variations in CSV settings by region
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 On Behalf Of Frank Cazabon Sent: Friday, April 16, 2021 10:45 AM To: profoxt...@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. ___ 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/mwhpr1001mb2144978207fbbfa485eccf11d2...@mwhpr1001mb2144.namprd10.prod.outlook.com ** 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.
RE: Variations in CSV settings by region
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 On Behalf Of Jean Laeremans Sent: Friday, April 16, 2021 11:10 AM To: profoxt...@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 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. ___ 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/mwhpr1001mb2144c1f035f0181c6d0a17b0d2...@mwhpr1001mb2144.namprd10.prod.outlook.com ** 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.
RE: Variations in CSV settings by region
No, it's an SCSV John Weller 07976 393631 > -Original Message- > From: ProfoxTech On Behalf Of Alan Bourke > Sent: 16 April 2021 16:47 > To: profoxt...@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 ;) > ___ 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/00a301d732dc$78bac920$6a305b60$@johnweller.co.uk ** 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.
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%20Hard%20to%20Do.pdf 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 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 On Behalf Of Ted Roche > Sent: Friday, April 16, 2021 10:26 AM > To: profoxt...@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 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-m1mn5pbi...@mail.gmail.com ** 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.
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 ;) -- Alan Bourke alanpbourke (at) fastmail (dot) fm ___ 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/070702a7-45ec-49b0-8e82-ce139e898...@www.fastmail.com ** 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.
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 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 On Behalf Of Ted Roche > > Sent: Friday, April 16, 2021 10:26 AM > > To: profoxt...@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 > 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/CAPqLOByWF9jwMhSQtYTB-P=cb+tr8vx+u2q_vsyed1nor5z...@mail.gmail.com ** 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.
Re: Variations in CSV settings by region
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 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/cadj74te0+-scvgr7teyree+s-so_uo_ilzyzaxbhodnbe+k...@mail.gmail.com ** 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.
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. 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 On Behalf Of Ted Roche Sent: Friday, April 16, 2021 10:26 AM To: profoxt...@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 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/e4b38e1b-832b-f552-fa42-163643519...@gmail.com ** 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.
RE: Variations in CSV settings by region
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 On Behalf Of Ted Roche Sent: Friday, April 16, 2021 10:26 AM To: profoxt...@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 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/mwhpr1001mb214494911bf1897994eb7568d2...@mwhpr1001mb2144.namprd10.prod.outlook.com ** 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.
RE: Variations in CSV settings by region
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-boun...@leafe.com] On Behalf Of Richard Kaye Sent: Friday, April 16, 2021 10:18 AM To: profoxt...@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 On Behalf Of Adam Buckland Sent: Friday, April 16, 2021 10:01 AM To: profoxt...@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 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/003401d732cc$f6a8e520$e3faaf60$@powerchurch.com ** 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.
RE: Variations in CSV settings by region
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 On Behalf Of Adam Buckland
Sent: Friday, April 16, 2021 10:21 AM
To: profoxt...@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
main()
{
char in[1024];
int i,k,l;
while ( gets([0]))
{
l = 0;
k = strlen([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 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 On Behalf Of Adam Buckland
Sent: Friday, April 16, 2021 10:01 AM
To: profoxt...@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 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-50feac9fe...@thebucklands.co.uk
** 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-50feac9fe...@thebucklands.co.uk
___
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/mwhpr1001mb214403380dcfd6d3e012ff35d2...@mwhpr1001mb2144.namprd10.prod.outlook.com
** 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.
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 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/cacw6n4tapy6pi8e6smtp1nq37vgjhovxngmccmbb4uovfvg...@mail.gmail.com ** 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.
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
main()
{
char in[1024];
int i,k,l;
while ( gets([0]))
{
l = 0;
k = strlen([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 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 On Behalf Of Adam Buckland
Sent: Friday, April 16, 2021 10:01 AM
To: profoxt...@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 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-50feac9fe...@thebucklands.co.uk
** 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.
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 On Behalf Of Adam Buckland Sent: Friday, April 16, 2021 10:01 AM To: profoxt...@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 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/mwhpr1001mb21441d33ac5568051bec296ad2...@mwhpr1001mb2144.namprd10.prod.outlook.com ** 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.
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 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/40830a43-f8a5-49a1-9e15-6c0b344f1...@thebucklands.co.uk ** 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.
