Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litLIB's CSV function and other separators (Read 2628 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litLIB's CSV function and other separators
09.02.10 at 10:40:55
 
litLIB's CSV function and other separators
 
The csv function in the litLIB library of extra functions for Excel, comma separated data can be displayed. If the csv function is used as an array function each data value is displayed in a separate cell. Very convenient if you have to deal with csv files for example. litLIB can display these files without bringing them into Excel first. It reads them directly from the disk or even from the web!
 
But how does it work when you have a system that uses a different field separator or decimal separator? In this topic I will demonstrate that this is no problem for the csv function.
 
In for instance the Dutch (The Netherlands) language fields are separated by a ; in a list. The decimal separator is a comma so 3/2 is written ad 1,5 and not as 1.5. You can imagine that this may be a problem for csv functions as 1,5 will probably be seen as two fields and not as one value.
 
To test this I have set my computer so it uses a , as a decimal separator and a ; as the list separator.
 
I created a spreadsheet which I then saved as csv. There is no standard for csv files so taking what Excel uses is probably not a bad idea.
 
I have typed some decimal values and some fields with text that include quotation marks to see how they are handled too.
 
Saving the file as book1.csv I created this file:

;0,666666667;1,6;;
hello;hello world;"Hello ""to the"" world";;Bye

 
Now, let's see how the csv function displays this data. I entered the following formula in cells A1:D12 (just a block of cells):
=CSV("c:\book1.csv";"file";";")
 
Explanation:
This function should take the contents of the file book1.csv and parse it using the field separator ;. Note that Excel now also uses the ; to delimit the fields of this function.
 
The function result was that neatly all values are displayed separately. Numbers, although having a , as a decimal separator, as correctly aligned to the right. Words with spaces are handled correctly and the double quotes inside a string are also displayed correctly. The leading and trailing double quotes are removed. This is correct as Excel put them around the text.
Back to top
 
« Last Edit: 09.02.10 at 11:27:19 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: litLIB's CSV function and other separators
Reply #1 - 09.02.10 at 11:32:27
 
Using the litLIB csv function with mixed locales
 
When using csv data in the litLIB csv function on a computer system that uses a different decimal separator than the decimal separator used in the csv data a problem could occur. Testing a csv file in which the decimal separator as a comma on a PC that uses English settings, i.e. a comma as thousands separator and a dot as decimal separator, a number with decimals was not correctly displayed. The number 0,666 was displayed as 666 as the comma was seen as a thousands separator.
 
This issue was fixed in litLIB version 2.0.6
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1