Article ID	1693
Article Title	INFO: CSV file format problems
Article URL	http://www.dataaccess.com/kbasepublic/kbprint.asp?ArticleID=1693
KBase Category	Visual DataFlex
Date Created	10/01/2002
Last Edit Date	02/01/2005

Article Text
The "CSV" abbreviation as data exchange format stands for Comma Separated Values. In such a file the character data (strings) should be enclosed by double or single quotes. Numeric data can have a decimal separator, which is a decimal point (dot) in the USA, and a comma in Europe. So, when the system reads a comma, should this be considered a value terminator or a decimal separator?
For this the value separator character has been made configurable in Windows and is set to semi-colon in Europe. The DataFlex READ command, however, does always expect a comma as value terminator and does not use the Windows Locale setting called ListSeparator. This means that if you want to use the READ command your data should contain a dot as decimal separator and the DF_DECIMAL_SEPARATOR should be set to (Ascii (".")) before reading. Thousand separators should not be present in CSV files.

If your CSV file does not use the comma as value separator, you can use a function like:

Function ReadData Integer iChannel Returns String
    String sData sCharacter sListSeparator sLiteralTerminator
    Boolean bEndOfValue bFirstChar bInLiteral

    // Get the Windows Locale Listseparator (see Article link).
    Get LocaleListSeparator To sListSeparator

    Move True To bFirstChar // For TRUE/FALSE see also article link

    Repeat
        // Read character by character until the listseparator has been found.
        Read_Block Channel iChannel sCharacter 1
        If (bInLiteral And sCharacter = sLiteralTerminator) Begin
           Move False To bInLiteral
        End
        If (bFirstChar) Begin
           Move False To bFirstChar
           If (sCharacter = '"' Or sCharacter = "'") Begin
              Move sCharacter To sLiteralTerminator
              Move True To bInLiteral
           End
        End
        If (Not (bInLiteral)) Begin
           Move (sCharacter = sListSeparator) To bEndOfValue
        End
        If (Not (bEndOfValue) And sCharacter <> sLiteralTerminator) Begin
           Move (sData + sCharacter) To sData
        End
    Until (bEndOfValue)

    Function_Return sData
End_Function // ReadData

Your code can be changed from:

Get_Attribute DF_DECIMAL_SEPARATOR To sDecimalSep
Set_Attribute DF_DECIMAL_SEPARATOR To (Ascii ("."))
Repeat
    Read Channel 1 SomeFile.Code
    Read Channel 1 SomeFile.Descr
    Read Channel 1 SomeFile.Amount
    Read Channel 1 SomeFile.Descr2
Until (bEofData)
Set_Attribute DF_DECIMAL_SEPARATOR To sDecimalSep

to:

Get_Attribute DF_DECIMAL_SEPARATOR To sDecimalSep
Set_Attribute DF_DECIMAL_SEPARATOR To (Ascii ("."))
Repeat
    Get ReadData 1 To SomeFile.Code
    Get ReadData 1 To SomeFile.Descr
    Get ReadData 1 To SomeFile.Amount
    Get ReadData 1 To SomeFile.Descr2
Until (bEofData)
Set_Attribute DF_DECIMAL_SEPARATOR To sDecimalSep


Contributed By:
Vincent Oorsprong
Company: Data Access Worldwide
email: vincent.oorsprong@dataaccess.eu
Web Site: http://www.dataaccess.eu

Links Related to this Article
DAW Knowledge Base article 1388: HOWTO: Getting the list separator for your Windows locale
DAW Knowledge Base article 1631: INFO: The history of True and False according to DataFlex

Email this Article
Email this Article to a Colleague
Send Feedback on this Article to Data Access Worldwide
Copyright ©2024 Data Access Corporation. All rights reserved.

The information provided in the Data Access Technical Knowledge Base is provided "as is" without warranty of any kind. Data Access Corporation disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall Data Access Corporation or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if Data Access Corporation or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply.