1. Dhanya K
  2. PowerBuilder
  3. Friday, 22 April 2022 11:31 AM UTC

Hello Team,

I am using PowerBuilder 2019 in my application. The application has an option to save the files as .csv(Comma Separated Value File Type). Our Client wants to change the delimiter from 'comma' to some other special character. Tried to change the regional settings with the desired character. But it did not work. Please help us with any suggestions that we can do before saving the file as .csv either from Powerbuilder or in windows so as to replace the default comma separated file to user defined character separated file.

Note: It is already existing functionality and the csv export is not done using UNLOAD CSV FILE DELIMITED BY Statement.

 

Thank you so much in advance

Accepted Answer
mike S Accepted Answer Pending Moderation
  1. Friday, 22 April 2022 14:20 PM UTC
  2. PowerBuilder
  3. # Permalink

see 

 SaveAsAscii - - DataWindow Reference (appeon.com)

 SaveAsFormattedText - - DataWindow Reference (appeon.com)

 

in both functions you can specify the field delimiter and the line delimiter

Comment
  1. Roland Smith
  2. Friday, 22 April 2022 14:39 PM UTC
Much better than my solution.
  1. Helpful
There are no comments made yet.
Hannu Pikkarainen Accepted Answer Pending Moderation
  1. Wednesday, 27 September 2023 21:11 PM UTC
  2. PowerBuilder
  3. # 1

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

Comment
There are no comments made yet.
Hannu Pikkarainen Accepted Answer Pending Moderation
  1. Wednesday, 27 September 2023 20:58 PM UTC
  2. PowerBuilder
  3. # 2

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!)

Comment
There are no comments made yet.
Hannu Pikkarainen Accepted Answer Pending Moderation
  1. Wednesday, 27 September 2023 20:32 PM UTC
  2. PowerBuilder
  3. # 3

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!)

Comment
There are no comments made yet.
Hannu Pikkarainen Accepted Answer Pending Moderation
  1. Wednesday, 27 September 2023 20:15 PM UTC
  2. PowerBuilder
  3. # 4

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.

Comment
There are no comments made yet.
Hannu Pikkarainen Accepted Answer Pending Moderation
  1. Wednesday, 27 September 2023 19:57 PM UTC
  2. PowerBuilder
  3. # 5

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.

 

 

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 22 April 2022 13:59 PM UTC
  2. PowerBuilder
  3. # 6

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
Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Friday, 22 April 2022 13:51 PM UTC
  2. PowerBuilder
  3. # 7

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

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.