Re: Semicolon delimited to Comma delimited

[CountZero]

If I understand you well, your input is a .csv file and your script takes this .csv file to fill an Excel spreadsheet. Due to the fact that .csv files are badly defined and can use any odd character as a delimiter, your Indian colleagues (whose Excel program expects a different delimiter) cannot use your script.

The solution is fairly simple. Use Text::CSV to transform the ';' delimiter to ',' before you feed the .csv file to your script. Text::CSV makes it extremely simple to do. Tux already explained to you how to do it. Your Indian colleagues will have to run this "transformation" script once on each "European" .csv file they get, but that is only a small effort.

[swatzz]

Hey CountZero!

Thanks! I already did think of that solution but i must also mention it was my last choice! You can see why in my reply to 'marinersk'. But someone gave me a nice idea which could work(i have not tested this yet but could definitely be a light in the dark tunnel of CSV/Excel features!)

#Get system language id to know German/US format of excels
Win32::API->Import('kernel32.dll', 'long GetSystemDefaultLangID()') or
+ die "Can't import GetSystemDefaultLangID: $^E\n";
$langid = GetSystemDefaultLangID() & 0xFFFF or die "ERROR: LANGID Retu
+rned <undef>\n"; # Mask out the garbage in high-order bytes
#my $langid = GetSystemDefaultLangID();
warn "my language id is :" .$langid;

This could be used at the start of a Perl script to identify if the list separator that comes along with a *.csv is going to be a semi-colon or comma! I do hope this comes in handy to anyone who faces this problem in the future!

Thank you fellow monks for your delightful replies!! I shall post one final comment if this idea works out. If not (i do hope it would), guess the discussion is still open for ideas!

[afoken]

Have you considered using one of Excels native file formats instead of fighting with CSV? Spreadsheet::WriteExcel creates the old binary Excel 97 format (*.xls), Excel::Writer::XLSX creates the new zipped XML format (*.xlsx). You should use the new format unless you have to use Excel versions from the last century.

Alexander

[afoken]

#Get system language id to know German/US format of excels
Win32::API->Import('kernel32.dll', 'long GetSystemDefaultLangID()') or
+ die "Can't import GetSystemDefaultLangID: $^E\n";
$langid = GetSystemDefaultLangID() & 0xFFFF or die "ERROR: LANGID Retu
+rned <undef>\n"; # Mask out the garbage in high-order bytes

Nice idea, but it is not going to work reliably. Quoting the documentation of Text::CSV_XS (by Tux):

The import/export from Microsoft Excel is a risky task, according to the documentation in Text::CSV::Separator. Microsoft uses the system's list separator defined in the regional settings, which happens to be a semicolon for Dutch, German and Spanish (and probably some others as well). For the English locale, the default is a comma. In Windows however, the user is free to choose a predefined locale, and then change every individual setting in it, so checking the locale is no solution.

Alexander