-
Jun 30th, 2022, 09:26 AM
#1
Thread Starter
Junior Member
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 recordrsItem.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
-
Jun 30th, 2022, 09:55 AM
#2
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.
-
Jun 30th, 2022, 10:50 AM
#3
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.
-
Jun 30th, 2022, 10:53 AM
#4
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.
-
Jun 30th, 2022, 11:58 AM
#5
Thread Starter
Junior Member
Re: Create CSV File for European Number Format
Originally Posted by dilettante
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!!!!!!!
-
Jun 30th, 2022, 12:03 PM
#6
Re: Create CSV File for European Number Format
-
Jun 30th, 2022, 01:03 PM
#7
Thread Starter
Junior Member
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
-
Jun 30th, 2022, 01:07 PM
#8
Thread Starter
Junior Member
Re: Create CSV File for European Number Format
Originally Posted by jdc2000
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 ";"
-
Jun 30th, 2022, 02:46 PM
#9
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
-
Jun 30th, 2022, 02:53 PM
#10
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.
-
Jun 30th, 2022, 03:21 PM
#11
Thread Starter
Junior Member
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.
-
Jun 30th, 2022, 04:01 PM
#12
Re: Create CSV File for European Number Format
Originally Posted by MudManiac
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
-
Jun 30th, 2022, 08:05 PM
#13
Thread Starter
Junior Member
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).
-
Jun 30th, 2022, 08:55 PM
#14
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.
-
Jul 1st, 2022, 12:45 AM
#15
Addicted Member
Re: Create CSV File for European Number Format
-
Jul 1st, 2022, 02:05 AM
#16
Re: Create CSV File for European Number Format
Originally Posted by MudManiac
...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.
-
Jul 1st, 2022, 06:56 AM
#17
Re: Create CSV File for European Number Format
Originally Posted by MudManiac
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!!!!!!!
Originally Posted by MudManiac
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
-
Jul 1st, 2022, 01:34 PM
#18
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|