Desclaimer :

This a one workaround to the PB malfunction, you can use it as a one way tackling it. I had to rewrite the code while writing (replacing the parameters, adding comments etc) so there can be errors. If you just copy and paste, you'll have to check if it's OK.

I hope this could provide help for all you developers strugling with this same problem.

I would appreciate, if you could share here all the ways you have solved this problem since 1989 when PowerBuilder was released?

BR, Hannu, don quijote

For Export :

SetPointer(HourGlass!)
if format = 'TAB' then
   //use standard method
   rows = dw_data.SaveAs(path,Text!,true)

elseif format = 'CSV' then

   //replace commas with a "." and copy result to clipboard
   result = Clipboard(of_replaceall(dw_data.Describe("DataWindow.Data"),",","."))

   //bring it back to dw_import, one column with a string(10000)
   rows = dw_import.importclipboard(Csv!)

   for i = 1 to rows

       //replace ""." with a ","
      result2 = SetItem(dw_import,i,1,(of_replaceall(GetItemString(dw_import,i,1),'.',',')))

       //repplace tab with a ";"
      result2 = SetItem(dw_import,i,1,(of_replaceall(GetItemString(dw_import,i,1),'~t',';')))
   next

  // then we need the column headers
   columns = long(dw_data.Describe("DataWindow.Column.Count"))
   headers = ''
   for c = 1 to columns
      ls_colname = dw_data.describe("#" + string(c) + ".name")
      headers += dw_data.describe(ls_colname + "_t.text")
      if c < columns then headers += ';'
   next
  

   // add these column headers to the datawindow as a first row we export

   dw_import.insertrow(1)
   SetItem(dw_import,1,1,headers)
   rows = dw_import.SaveAs(path,text!,false)

   //and we are done
else //Excel
   //use standard method
   rows = dw_data.SaveAs(path,XLSX!,true)
end if
SetPointer(Arrow!)

For import I used this :

SetPointer(HourGlass!)
   if remove_headers = 1 then
      rows = dw_import.ImportFile(Text!, path, 2)
   else
      rows = dw_import.ImportFile(Text!, path)
   end if

for i = 1 to rows

   //replace all ";" with a tab

   //replace possible 1000 sepators with a blank, all " " with a "" and all "." with ""
  result2 = SetItem(dw_import,i,1,of_replaceall(of_replaceall(of_replaceall(GetItemString(dw_import,i,1),';','~t')," ",""),".",""))
next

//copy data to clipboard where it will be tab separated, one column containing the whole row, with the tabs
result = Clipboard(dw_import.Describe("DataWindow.Data"))

//bring it back to original datawindow having all the columns
result2 = dw_data.importclipboard(text!)

//and we are done

SetPointer(Arrow!)

I still needed to provide the usage of CSV-files for users in Europe in my current project so I created two workarounds for it.

First you need to create one datastore or datawindow having only one column type string(10000), in my examples it is dw_import. Then you use it, clipboard and the original datawindow that should have the data, in my examples it is dw_data.

You need to create new responce windows for importing and exporting data, where you ask the format (tab-separated and CSV for import and tab-separated, CSV and Excel 12 for export) where you get the format, file and path and handling of header column in import.

I just again reported thed same bug, that PB does not follow the regional settings of list separator and thousand separator of the users regional settings, altough it respects the setting of decimal separator. That causes that in countries using comma as decimal separator (in Europe 29 countries, in South-America 9 countries and other regions 3 countries, totalling of over 900 million inhabitants, can't use PB's SaveAs(csv!) or ImportFile(csv!). Or if you use these, Excel won't accept the csv-file you save in PB and PB can't read the file Excel saves.

Appeon still can't admit this is a bug, they say it is a feature working as planned and it has been working this way all the time same way, in all versions of PB. That is right, it has been working like this all along, I have been using PB over 26 years, but I still think this is a BUG. Programs in Window environment should work as Microsoft programs do. And PB does not.

Here is a ReplaceAll function. You could use dw_1.Describe("DataWindow.Data") to get a tab delimited string of all the data, replace all tabs with the desired character, and then write the modified string to a file in one step using FileWriteEx.

public function string replaceall (string as_oldstring, string as_findstr, string as_replace);// ReplaceAll - Replaces all occurrences of a string in another.

Long ll_findstr, ll_replace, ll_pos
String ls_newstring

// get length of strings
ll_findstr = Len(as_findstr)
ll_replace = Len(as_replace)

// find first occurrence
ls_newstring = as_oldstring
ll_pos = Pos(ls_newstring, as_findstr)

Do While ll_pos > 0
	// replace old with new
	ls_newstring = Replace(ls_newstring, ll_pos, ll_findstr, as_replace)
	// find next occurrence
	ll_pos = Pos(ls_newstring, as_findstr, (ll_pos + ll_replace))
Loop

Return ls_newstring

end function

As far as I know there is no option/configuration to alter this behavior.

Conceivably, you could save the CSV normally, then read/parse/alter/write the CSV file contents using an alternate delimiter. However, depending on the data values, the logic might have to be smart enough to recognize and not translate a comma inside of any quoted string value, such as "Acme, Inc.". If you choose to go this route, I'd also change the file extension from .csv to something else, so as to not confuse other software, for example, Excel. Of course, any customer is free to create this kind of "post-processing" functionality on their own.

I fully understand that some customers are not always, let's say, rational beings, but you might try to explain to your customer that CSV stands for Comma-Separated-Value, not Conditional-Separated-Value or Completely-arbitrary-Separated-Value. The CSV format is a long-standing, time-honored data format. If none of the many alternative standard Save As types supported by PB are acceptable to your customer, you may wish to consider writing a custom "save as" function for them.

Best regards, John