Results 1 to 18 of 18

Thread: Create CSV File for European Number Format

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2021
    Posts
    18

    Create CSV File for European Number Format

    I have a program that outputs invoice data to a CSV file for importing into Excel. This works great in the US. The problem is that the Netherlands number format is different. They use a "." as a thousand separator and a "," for their decimal point. Example: 1,000.50 in the US is written as 1.000,50 in the Netherlands. When I write to the data file, it is writing the value in the US number format (i.e. 1,000.50). Is there a way to write the value to the to the CSV file in the Netherlands format (i.e. 1.000,50)? This brings me to my 2nd problem. If I use a "," as the delimiter in my CSV file, this does not import correctly in Excel in the Netherlands. Is there a way to use a ";" as the delimiter? My existing code is as follows:

    Code:
    stInvoiceExport = "InvoiceExport.csv"    
            
    'Write the first line as the column headers
    Open stInvoiceExport For Output As #1
         Write #1, "Invoice Number", "Item Number", "Description", "Size", "Unit", "Quantity", "Rate"
    Close #1
    
    'Loop thru records to set the define variables then write the records to file
    sqlItem = "select * from [InvoiceItem]"
    sqlItem = sqlItem & "where [Ticket] = '" & stInvoiceNum & "'"
    Set rsItem = db1.OpenRecordset(sqlItem, dbOpenSnapshot)
    
    'Check for at lease one record in database
    If rsItem.RecordCount > 0 Then
    
    'Go to first record
    rsItem.MoveFirst
    Do While Not rsItem.EOF
    stItemNum = "" & rsItem!ItemNum
    stDescription = "" & rsItem!Description
    stSize = "" & rsItem!Size
    stUnit = "" & rsItem!Unit
    inQty = rsItem![Quantity]
    inRate = rsItem![Rate]
    Open stInvoiceExport For Append As #1
    Write #1, stInvoiceNum, stItemNum, stDescription, stSize, stUnit, inQty, inRate
    Close #1
    rsItem.MoveNext
    Loop
    End If

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Create CSV File for European Number Format

    I ran into this exact problem on an installation in Montreal (French speaking). I solved it by forcing the Windows decimal separator to a period, but that might not work for you, IDK.

    Also, several will tell you that it's exceptionally rude to do that (and they may be correct), but it worked in my situation. In Montreal, they just kept setting their computer's codepage to French, but they didn't really care about the decimal separator (which my software did). So, I think each situation is going to be a bit different.

    Bottom line, software that's going to be marketed world-wide should just be programmed to deal with this.

    But, in any event, here's the code I use:

    Code:
    Public Sub ForceSystemDecimalToPeriod()
        ' We MUST use the ANSI API version so it's an ANSI character that's used for the actual decimal character.
        Const LOCALE_SDECIMAL   As Long = &HE&
        Const LOCALE_SGROUPING  As Long = &H10&
        Const Eng_LCID          As Long = 1033&
        Dim s                   As String
        '
        s = String$(GetLocaleInfoA(Eng_LCID, LOCALE_SDECIMAL, vbNullString, 0&), 0)
        GetLocaleInfoA Eng_LCID, LOCALE_SDECIMAL, s, Len(s)
        If RTrimNull(s) <> "." Then
            SetLocaleInfoA Eng_LCID, LOCALE_SDECIMAL, "."
            SetLocaleInfoA Eng_LCID, LOCALE_SGROUPING, ","
        End If
    End Sub
    And the API declarations:

    Code:
    Private Declare Function GetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
    Private Declare Function SetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Long
    And:

    HTML Code:
    Public Function RTrimNull(s As String) As String
        RTrimNull = s
        Do
            If Right$(RTrimNull, 1&) <> vbNullChar Then Exit Do
            RTrimNull = Left$(RTrimNull, Len(RTrimNull) - 1&)
        Loop
    End Function
    Last edited by Elroy; Jun 30th, 2022 at 09:58 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Create CSV File for European Number Format

    While you'd need to look up how to make it work with ancient and abandoned DAO I am sure it can still be done.

    Jet already has a facility for exporting as text and by default it makes use of the current language settings. This is easy enough via the Jet and ACE OLEDB Providers but I'm sure there was a hack to make possible via one of the arms or legs that were tacked onto DAO.

    So if you need to be compatible with the settings for the machine and user there is nothing goofy you need to do.

    Programs calling SetLocaleInfo() should raise the hair on the back of your neck. I'd categorize them as malware myself.

    Caution Because this function modifies values for all applications, it should only be called by the regional and language options functionality of Control Panel, or a similar utility. If making an international change to system parameters, the calling application must broadcast the WM_SETTINGCHANGE message to avoid causing instabilities in other applications.

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Create CSV File for European Number Format

    BTW:

    Your basic problem is using VB's Write # statements. Files created that way are only meant to be read back via Input # statements.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2021
    Posts
    18

    Re: Create CSV File for European Number Format

    Quote Originally Posted by dilettante View Post
    BTW:

    Your basic problem is using VB's Write # statements. Files created that way are only meant to be read back via Input # statements.

    I initially created this program about 20 years ago. It runs our main invoicing and inventory control (as well as a host of other things). It has worked great all these years. It has about 2,000 pages of code, so I have been reluctant to modify it.

    I am working on a version that will allow use of Access 365 data tables. However, it is still in the DAO format (using Microsoft DAO 3.6 Object Library).

    We have a multi-country accounting system (Microsoft NAV) that we utilize for primary accounting. Our goal is to allow importing of the invoice records into NAV. This is where the numeric formatting causes issues.

    The csv files the program creates work perfect in the US (can open them in Excel with no problem).

    The invoicing program checks the Regional setting before running. It has to be "Dutch-Netherlands" to run.

    I was thinking about using the format statement to format the numeric variable into the European format. It would look something like: inQty = Format(inQty, "#.##0,00")

    However, this doesn't solve my "," delimited problem. Does anyone know of a way to define what character you want to use for the delimiter? Thanks!!!!!!!

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Create CSV File for European Number Format


  7. #7

    Thread Starter
    Junior Member
    Join Date
    Nov 2021
    Posts
    18

    Re: Create CSV File for European Number Format

    Well I tried the format statement with no success (example: I had a 1stRate value in the database of 3.08. I set the value to the variable and modified the numeric format as follows: in1stRate = Format(in1stRate, "#.##0,00")

    When I print the variable value to the screen, it shows the proper format as 3,08

    However, when it writes to the file, it writes it as 3.08 (maybe it's because I have the variable defined as currency)

    It sure would be easier if everyone used the same number and date formats

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Nov 2021
    Posts
    18

    Re: Create CSV File for European Number Format

    Quote Originally Posted by jdc2000 View Post
    I really appreciate the suggestion. Unfortunately, I'm not having a problem in Excel. The problem I am having is writing the numbers to the csv file in the European format (i.e. #.##0,00) and then changing the delimiter in the csv file to a ";"

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Create CSV File for European Number Format

    Part of your answer:

    Code:
    Option Explicit
    
    Private Const LOCALE_SLIST As Long = &HC& 'list item separator
    
    Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" ( _
        ByVal Locale As Long, _
        ByVal LCType As Long, _
        ByVal lpLCData As Long, _
        ByVal cchData As Long) As Long
    
    Private Const LOCALE_USER_DEFAULT As Long = &H400&
    Private Const LOCALE_DUTCH_NETHERLANDS As Long = &H413&
    
    Private Sub Main()
        Dim CharCount As Long
        Dim Sep As String
    
        CharCount = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SLIST, 0, 0)
        Sep = Space$(CharCount - 1)
        GetLocaleInfo LOCALE_USER_DEFAULT, LOCALE_SLIST, StrPtr(Sep), CharCount
        Debug.Print Sep
    
        CharCount = GetLocaleInfo(LOCALE_DUTCH_NETHERLANDS, LOCALE_SLIST, 0, 0)
        Sep = Space$(CharCount - 1)
        GetLocaleInfo LOCALE_DUTCH_NETHERLANDS, LOCALE_SLIST, StrPtr(Sep), CharCount
        Debug.Print Sep
    End Sub

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Create CSV File for European Number Format

    First of all, I'd avoid VBs built-in FileRoutines and use something that's capable to write UTF8-text.

    Secondly, why not invest your CSV-coding-efforts into something that works generically
    (with any passed Recordset).

    E.g. something like that (which should work with either ADO-Rs or DAO-Rs):
    Code:
    Private Sub WriteUTF8CSVTo(FileName As String, Rs As Recordset)
      Dim S As Object, ColDel As String, i As Long, Cols() As String, V
      Set S = CreateObject("ADODB.Stream")
          S.Open: S.Charset = "utf-8"
     
      ColDel = IIf(Format$(0, ".") = ",", ";", ",") 'determine the Column-Separator dynamically
     
      ReDim Cols(0 To Rs.Fields.Count - 1)
      For i = 0 To Rs.Fields.Count - 1  'fld-loop to gather all Header-Names in the Cols-Arr
          Cols(i) = """" & Replace(Rs.Fields(i).Name, """", """""") & """"
      Next
      S.WriteText Join(Cols, ColDel) & vbCrLf  'write-out the joined Header-line
      
      Do Until Rs.EOF 'after the headerline is in place, we try to find records
         For i = 0 To Rs.Fields.Count - 1 'fld-loop to gather all Values in the Cols-Arr
              V = Rs.Fields(i).Value
              Select Case VarType(V)
                 Case vbNull:   Cols(i) = ""
                 Case vbString: Cols(i) = """" & Replace(V, """", """""") & """"
                 Case Else:     Cols(i) = V
              End Select
         Next
         S.WriteText Join(Cols, ColDel) & vbCrLf  'write-out each joined Values-line
         Rs.MoveNext 'before moving to the next record
      Loop
      
      'finally, we write the utf-8 stream to a file
      S.SaveToFile FileName, 2 '<- adSaveCreateOverWrite
    End Sub
    For such a generic routine to work, you will have to properly reformat your SQL-Select first -
    so that the returned Field-Names will match the Col-Names in the Header-Line of your CSV-Export:

    Here's complete code (including the above routine again):
    Code:
    Private Sub Main()
      ExportInvoiceDetails "12345"
    End Sub
    
    Private Sub ExportInvoiceDetails(stInvoiceNum As String)
      Dim SQL As String
          SQL = "Select Ticket As [Invoice Number], ItemNum As [Item Number], " & _
                "       Description, Size, Unit, Quantity, Rate" & _
                " From InvoiceItem Where Ticket='" & stInvoiceNum & "'"
                
      WriteUTF8CSVTo App.Path & "\InvoiceExport.csv", db1.OpenRecordset(SQL, dbOpenSnapshot)
    End Sub
     
    Private Sub WriteUTF8CSVTo(FileName As String, Rs As Recordset)
      Dim S As Object, ColDel As String, i As Long, Cols() As String, V
      Set S = CreateObject("ADODB.Stream")
          S.Open: S.Charset = "utf-8"
     
      ColDel = IIf(Format$(0, ".") = ",", ";", ",")
     
      ReDim Cols(0 To Rs.Fields.Count - 1)
      For i = 0 To Rs.Fields.Count - 1  'fld-loop to gather all Header-Names in the Cols-Arr
          Cols(i) = """" & Replace(Rs.Fields(i).Name, """", """""") & """"
      Next
      S.WriteText Join(Cols, ColDel) & vbCrLf  'write-out the Header-line
      
      Do Until Rs.EOF 'after the headerline is in place, we try to find records
         For i = 0 To Rs.Fields.Count - 1 'fld-loop to gather all Values in the Cols-Arr
              V = Rs.Fields(i).Value
              Select Case VarType(V)
                 Case vbNull:   Cols(i) = ""
                 Case vbString: Cols(i) = """" & Replace(V, """", """""") & """"
                 Case Else:     Cols(i) = V
              End Select
         Next
         S.WriteText Join(Cols, ColDel) & vbCrLf  'write-out each Values-line
         Rs.MoveNext
      Loop
      
      'finally, we write the utf-8 stream to a file
      S.SaveToFile FileName, 2 '<- adSaveCreateOverWrite
    End Sub
    HTH

    Olaf
    Last edited by Schmidt; Jun 30th, 2022 at 03:05 PM.

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Nov 2021
    Posts
    18

    Re: Create CSV File for European Number Format

    Olaf,

    Thank you so much. I'll search through the code and try to understand it. Unfortunately, my background is nuclear engineering, not programming. As you can tell, I'm self taught and do the best I can.

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Create CSV File for European Number Format

    Quote Originally Posted by MudManiac View Post
    Olaf,

    Thank you so much. I'll search through the code and try to understand it. Unfortunately, my background is nuclear engineering, not programming. As you can tell, I'm self taught and do the best I can.
    No problem... feel free to ask, in case you cannot make sense of certain Lines...

    The few things which pop into my mind, which might be "alien" to you...:

    - the "re-naming" of certain SQL-Columns via "OriginalField As [Some other FieldName]"
    ... (to new ones, which differ from the FieldNames of the original SQL-Table-Definition)
    - the dynamic detection of the current, localized "List-Separator" via: IIf(Format$(0, ".") = ",", ";", ",")
    ... (which is just a "oneliner-poor-mans-version" of what dilettante posted in #9)
    - and perhaps the ADO.Stream-Object (which is used as a kind of "UTF8-capable-StringBuilder-Class")
    - and finally perhaps the needed Double-Quoted Replace-construct (in case of String-Values)

    HTH

    Olaf

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Nov 2021
    Posts
    18

    Re: Create CSV File for European Number Format

    Olaf, I have not completed rewriting all the VB6 code for Access 365 (primarily how it prints to Crystal Reports 11 - was previously using Crystal Report 5). Due to this, the present data tables are actually Access 97 (very archaic, but they work good). I do not think Access 97 formatted tables will support an ADO call (but not sure).

  14. #14
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Create CSV File for European Number Format

    The ACE OLEDB Providers are just rewarmed private versions of the Jet 4.0 OLEDB Provider that ships in Windows.

    Jet 4.0 can support old "Access" formats even older than the Jet 3.x formats that MS Access 97 used.

  15. #15
    Addicted Member gilman's Avatar
    Join Date
    Jan 2017
    Location
    Bilbao
    Posts
    176

    Re: Create CSV File for European Number Format


  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Create CSV File for European Number Format

    Quote Originally Posted by MudManiac View Post
    ...the present data tables are actually Access 97
    (very archaic, but they work good).

    I do not think Access 97 formatted tables will support an ADO call (but not sure).
    Please try it out at least ...
    And no, there is no ADO-Projectreference to set (if you have a DAO-one currently)...

    The ADO.Stream-Object is not really related to "any DataBase" - it is only used for Text-Processing
    and TextFile-writing - and it is created via CreateObject(...) - a call that is supported even
    in very old VBA- and VB-Versions.

    And as said already - the larger routine I've posted (WriteUTF8CSVTo) is generic -
    and should "just work" with both (passed DAO-Recordset or passed ADO-Recordset,
    depending on, what Project-Reference was set in the Project).

    The actual (still DAO-specific) Rs-retrieval-code is in the other (smaller) routine I've posted -
    here it is again (the DAO-Rs-retrieval is colored red):
    Code:
    Private Sub ExportInvoiceDetails(stInvoiceNum As String)
      Dim SQL As String
          SQL = "Select Ticket As [Invoice Number], ItemNum As [Item Number], " & _
                "       Description, Size, Unit, Quantity, Rate" & _
                " From InvoiceItem Where Ticket='" & stInvoiceNum & "'"
                
      WriteUTF8CSVTo App.Path & "\InvoiceExport.csv", db1.OpenRecordset(SQL, dbOpenSnapshot)
    End Sub
    Olaf
    Last edited by Schmidt; Jul 1st, 2022 at 02:14 AM.

  17. #17
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Create CSV File for European Number Format

    Quote Originally Posted by MudManiac View Post
    I initially created this program about 20 years ago. It runs our main invoicing and inventory control (as well as a host of other things). It has worked great all these years. It has about 2,000 pages of code, so I have been reluctant to modify it.

    I am working on a version that will allow use of Access 365 data tables. However, it is still in the DAO format (using Microsoft DAO 3.6 Object Library).

    We have a multi-country accounting system (Microsoft NAV) that we utilize for primary accounting. Our goal is to allow importing of the invoice records into NAV. This is where the numeric formatting causes issues.

    The csv files the program creates work perfect in the US (can open them in Excel with no problem).

    The invoicing program checks the Regional setting before running. It has to be "Dutch-Netherlands" to run.

    I was thinking about using the format statement to format the numeric variable into the European format. It would look something like: inQty = Format(inQty, "#.##0,00")

    However, this doesn't solve my "," delimited problem. Does anyone know of a way to define what character you want to use for the delimiter? Thanks!!!!!!!
    Quote Originally Posted by MudManiac View Post
    I really appreciate the suggestion. Unfortunately, I'm not having a problem in Excel. The problem I am having is writing the numbers to the csv file in the European format (i.e. #.##0,00) and then changing the delimiter in the csv file to a ";"
    Put quotes around the value ... then you can still leave it as a CSV with "," as the field delimiter.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Nov 2021
    Posts
    18

    Re: Create CSV File for European Number Format

    I really appreciate the help. The locale shouldn't be a problem because the user can't even enter the program until the Regional settings are set to Dutch (Netherlands). I actually pull the data that is required in the csv file from 3 separate tables (Customer, InvoiceHead and InvoiceItem. I think I may create a new table in the database named "InvoiceExports". I would delete all records in the new table and then use my existing looping routines to populate the desired records in that table.

    I could then use something like either yours or Olaf's examples to write the csv files.

    In another frustrating twist, my end user has now requested, if possible, to output it to an xml file. I really have no idea how to do that. I figured if all the fields and desired records are already in one table, it may be easier to find a way to do it.

    Thanks for all your suggestions!!!!!!!!!!!!!!!!!!!!!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width