Re: Semicolon delimited to Comma delimited

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.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

Comment on Re: Semicolon delimited to Comma delimited

Replies are listed 'Best First'.
Re^2: Semicolon delimited to Comma delimited by swatzz (Novice) on Apr 24, 2015 at 14:09 UTC
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;` [download] 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!	[reply] [d/l]
Re^3: Semicolon delimited to Comma delimited by afoken (Chancellor) on Apr 24, 2015 at 16:09 UTC
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 -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re^3: Semicolon delimited to Comma delimited by afoken (Chancellor) on Apr 24, 2015 at 17:56 UTC
`#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` [download] 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 -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]