(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Export has extra RegEx code?
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Export has extra RegEx code? (Read 2529 times)
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Export has extra RegEx code?
Nov 23rd, 2018 at 5:59pm
Print Post Print Post  
Doing some tests to find certain characters that are rejected by an importing program.  Characters are tested in the fourth field, after PART field.  They all start with "qqq".  So the test for "!" is "qqq!".

Test2 and Test9 are adding an extra "\" that I recognize from RegEx.  So the "\" and """ chars are problems in the receiving program.

Code
Select All
"TEST1","Active","PART","qqq'","","RENTALS",2.0000,"",,"","",1,0,1,"","",,"","",,1.0000,"","","","","",,,,,,,,,"",,,"",,,"","",2154.00000000,"joe",2018/11/15,10:50:00,"joe",2018/11/16,01:05:00,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST2","Active","PART","qqq\"","","RENTALS",2.0000,"",,"","",1,0,1,"","",,"","",,1.0000,"","","","","",,,,,,,,,"",,,"",,,"","",2155.00000000,"joe",2018/11/15,10:52:00,"joe",2018/11/15,11:05:00,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST3","Active","PART","qqq:","","RENTALS",1.0000,"",,"","",1,0,1,"","",,"","",,1.0800,"","","","","",,,,,,,,,"",,,"",,,"","",2150.00000000,"joe",2018/11/14,15:44:00,"joe",2018/11/16,00:33:00,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST4","Active","PART","qqq;","","RENTALS",2.0000,"",,"","",1,0,1,"","",,"","",,1.0000,"","","","","",,,,,,,,,"",,,"",,,"","",2156.00000000,"joe",2018/11/15,10:53:00,"",,,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST5","Active","PART","qqq,","","RENTALS",1.0000,"",,"","",1,0,1,"","",,"","",,1.0800,"","","","","",,,,,,,,,"",,,"",,,"","",2157.00000000,"joe",2018/11/15,11:30:00,"",,,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST6","Active","PART","qqq-","","RENTALS",1.0000,"",,"","",1,0,1,"","",,"","",,1.0800,"","","","","",,,,,,,,,"",,,"",,,"","",2158.00000000,"joe",2018/11/15,11:32:00,"",,,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST7","Active","PART","qqq_","","RENTALS",1.0000,"",,"","",1,0,1,"","",,"","",,1.0800,"","","","","",,,,,,,,,"",,,"",,,"","",2159.00000000,"joe",2018/11/15,11:33:00,"joe",2018/11/15,11:34:00,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST8","Active","PART","qqq*","","RENTALS",1.0000,"",,"","",1,0,1,"","",,"","",,1.0800,"","","","","",,,,,,,,,"",,,"",,,"","",2160.00000000,"joe",2018/11/15,11:35:00,"",,,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST9","Active","PART","qqq\\","","RENTALS",1.0000,"",,"","",1,0,1,"","",,"","",,1.0800,"","","","","",,,,,,,,,"",,,"",,,"","",2161.00000000,"joe",2018/11/15,11:38:00,"joe",2018/11/16,00:33:00,2018/11/16,01:10:00,"",,"","INVITEM",""
"TEST10","Active","PART","qqq/","","RENTALS",1.0000,"",,"","",1,0,1,"","",,"","",,1.0800,"","","","","",,,,,,,,,"",,,"",,,"","",2162.00000000,"joe",2018/11/15,11:41:00,"",,,2018/11/16,01:10:00,"",,"","INVITEM",""


Is there a way to stop the "\" from being inserted?

I am sure that other characters may be treated the same way.   

I am using the default settings for Export.  Only options set are for Comma Separation, Delimit Strings, and Double Quote Delimiter.
  
Back to top
 
IP Logged
 
actiontech
Full Member
***
Offline



Posts: 173
Joined: Apr 10th, 2016
Re: Export has extra RegEx code?
Reply #1 - Nov 24th, 2018 at 11:32pm
Print Post Print Post  
You could run a For Next Loop, where you first get a count of your number of results. And run the loop through that entire count of results. And then in the string array you could use @Replace to replace "/" with "" or something. And store it all in a new string array or something like that.
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2482
Joined: Aug 20th, 2003
Re: Export has extra RegEx code?
Reply #2 - Nov 26th, 2018 at 2:55pm
Print Post Print Post  
obfusc88 wrote on Nov 23rd, 2018 at 5:59pm:
Is there a way to stop the "\" from being inserted?



No. The \ is an escape character and is inserted before the delimiter character when the delimiter character appears in the field data and also before a \ when it appears in the data.

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Export has extra RegEx code?
Reply #3 - Nov 27th, 2018 at 7:07am
Print Post Print Post  
I am trying to have users export records so they can bring them into OpenOffice Calc and Excel spreadsheets.   Having them take extra editing steps is not practical.

It is common for descriptions to use fractions and dimensions.   And columms defining Drive Paths all have backslashes.   

You state that there is no way to prevent the extra backslash in export files.
So I have to train a group of users to change the way they have worked for their lifetime.

I can set up some Mass Updates to modify tens of thousands of existing records.  For future records I can instruct them to use inches vs. " and use "/" vs.  "\" but they will still use the wrong chars out if habit.   New users will not be properly trained about these exceptions.  And I will be getting support calls about why their spreadsheets are messed up.

There must be an easier fix.   How do others deal with this?
  
Back to top
 
IP Logged
 
actiontech
Full Member
***
Offline



Posts: 173
Joined: Apr 10th, 2016
Re: Export has extra RegEx code?
Reply #4 - Nov 27th, 2018 at 6:48pm
Print Post Print Post  
This removes the "\". As far as the """ I don't see that in the code. So I can't figure out a way to fix that. You might have to convert the RegEx stringarray to a regular stringarray by changing the delimiters and modifying some things. Then convert it back.

Code
Select All
var vLoop1 as Int
var vLoop2 as Int
var vLoop3 as Int
var vRegEx as String
var vEntry as String
var vCharacter as String

vRegEx = "green;yellow\;blue;orange;black"

WriteLn("vRegEx Before = " + vRegEx)
WriteLn(@NewLine())

For vLoop1 = 1 to @CountStringArray(vRegEx)

	For vLoop2 = 1 to @LEN(@AccessStringArray(vRegEx, vLoop1))

		vCharacter = @Asc(@MID(@AccessStringArray(vRegEx, vLoop1), vLoop2, 1))
		vEntry = @AppendStringArray(vEntry, vCharacter)

		//Takes each entry in the RegEx string array and converts each character into
		//ASCII numbers in it's own string array per entry.

	Next

	WriteLn("Before = " + vEntry)
	vEntry = @DeleteStringArray(vEntry, @FindStringArray(vEntry, "92"))
	vEntry = @DeleteStringArray(vEntry, @FindStringArray(vEntry, "34"))
	WriteLn("After = " + vEntry)

		//Deletes the ASCII numbers for whichever corresponding characters you want to remove
		//from your individual entries in the string array.

	For vLoop3 = 1 to @CountStringArray(vEntry)

		vCharacter = @Chr(@AccessStringArray(vEntry, vLoop3))
		vEntry = @ReplaceStringArray(vEntry, vCharacter, vLoop3)

		//Converts the ASCII numbers back into characters after the unwanted characters have
		//been deleted.

	Next

	WriteLn("Before = " + vEntry)
	vEntry = @REPLACE(vEntry, ";", "")

	//Converts the string array back into a regular string by removing all occurrences of the ";"
	//delimiter.

	WriteLn("After = " + vEntry)
	WriteLn(@NewLine())

	vRegEx = @ReplaceStringArray(vRegEx, vEntry, vLoop1)

	//Replaces the entry in the vRegEx string array with the newly modified entry without the
	//unwanted characters.

	vEntry = ""

	//Resets vEntry variable for the next loop.

Next

WriteLn("vRegEx After = " + vRegEx)
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2482
Joined: Aug 20th, 2003
Re: Export has extra RegEx code?
Reply #5 - Nov 27th, 2018 at 7:07pm
Print Post Print Post  
Since the escape character is part of the built in Export, the answer is to not use the built in Export. Add a command button to the form and use programming like the following:

Code
Select All
#Include "sbasic_include.sbas"
Var vSpec as String
Var vElement as String
Var vElements as String
Var vValue as String
Var vHeader as String
Var vData as String
Var vLoop as Int
Var vCnt as Int
Var vELoop as Int
Var vECnt as Int
Var vFileName as String
Var vHandle as Int

	vFileName = @PromptForUserInput("Please insert file path", "Export.csv")
	If vFileName <> "" Then
	{
		If FileExists(vFileName) Then
		{
			FileDelete(vFileName)
		}

		vHandle = FileOpen(vFileName)
		If vHandle >= 0 Then
		{
			vSpec = @SpecCommand(SPEC_OPERATION_VIEW, SPEC_TYPE_EXPORT, "")
			While vSpec <> ""
			{
				vElement = Split(vSpec, ";")
				vElement = Split(vElement, "=")
				If vElements <> "" Then
				{
					vElements = vElements + ";"
					vHeader = vHeader + ","
				}
				vElements = vElements + vElement
				vHeader = vHeader + """" + vElement + """"
			}
			vECnt = @CountStringArray(vElements)
			FileWriteLn(vHandle, vHeader)

			vLoop = 1
			vCnt = @ResultSetTotal()
			While vLoop <= vCnt
			{
				vData = ""
				ResultSetCurrentPosition(vLoop)
				vELoop = 1
				While vELoop <= vECnt
				{
					vElement = @AccessStringArray(vElements, vELoop)
					SetThisElement(vElement)
					vValue = @AsFormattedByLE(ThisElement, 1, ThisElement)
					If vData <> "" Then
					{
						vData = vData + ","
					}
					vData = vData + """" + vValue + """"
					UnSetThisElement()
					vELoop = vELoop + 1
				}
				FileWriteLn(vHandle, vData)
				vLoop = vLoop + 1
			}
			FileClose(vHandle)
		}
	}


Note: This code uses the export spec so that the user can determine what fields they wish to export. I recommend setting a default export spec of the most common fields they want in the export. It does not do any replacing of special characters that may be in the field data that may require special formatting for importing of the data into the receiving program.

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Export has extra RegEx code?
Reply #6 - Nov 28th, 2018 at 1:56am
Print Post Print Post  
Two good possible options to evaluate.   Thanks you actiontech and Ray.   Will need some adaption for orders with unlimited line items.   Will start to try these out over next few days.

Thanks for the detailed samples.
  
Back to top
 
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge