tfewster500
asked on
Remove "Non Text / Numeric" values from String
I've developed a VB.NET application that does the following
1) Opens up a Text File
2) Searches through the text file line by line to find specific strings of information in the row.
3) After searching through the row, it inserts information into an SQL Server database.
4) From SQL Server, I export the data into a fixed width format text file.
Here is my problem. When I export the file to the fixed width format from SQL Server, the fixed width layout is not maintained. For example, the first field in my export has a width of 50. When I export to the fixed width, there are records where this field will have a width of 51 which throws the rest of the layout off by 1 character.
I believe the reason for this is that there is some type of "non-text" character in the string such that when I export to text file it adds a space to the width.
Is there a way that I can remove these types of characters from the string value within VB.NET?
I think that Regular Expressions would be the way to go, but I'm not sure how to do this.
1) Opens up a Text File
2) Searches through the text file line by line to find specific strings of information in the row.
3) After searching through the row, it inserts information into an SQL Server database.
4) From SQL Server, I export the data into a fixed width format text file.
Here is my problem. When I export the file to the fixed width format from SQL Server, the fixed width layout is not maintained. For example, the first field in my export has a width of 50. When I export to the fixed width, there are records where this field will have a width of 51 which throws the rest of the layout off by 1 character.
I believe the reason for this is that there is some type of "non-text" character in the string such that when I export to text file it adds a space to the width.
Is there a way that I can remove these types of characters from the string value within VB.NET?
I think that Regular Expressions would be the way to go, but I'm not sure how to do this.
I think you will have to check the max length for every column and set the fixed width to length + 1 to get a proper format
This is too general, we need more info. Is there an extra space in the 51 string or a different one each time, are the values right in the sql db or not, are the values right before inserting to sql or not, how do you do the export?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How are you exporting the data to the text file?
>> Is there a way that I can remove these types of characters from the string value within VB.NET?
Sure. I've started the pattern for you, but if you need to allow any other characters, add them inside the square brackets--please be sure to leave the dash at the end (unless you remove it).
Sure. I've started the pattern for you, but if you need to allow any other characters, add them inside the square brackets--please be sure to leave the dash at the end (unless you remove it).
Dim result As String = System.Text.RegularExpressions.Regex.Replace(your_text, "[^a-zA-Z0-9 $%#@!*&()[\]""':;-]")
Forgot the replacement!
Dim result As String = System.Text.RegularExpressions.Regex.Replace(your_text, "[^a-zA-Z0-9 $%#@!*&()[\]""':;-]", String.Empty)
P.S.
Do not remove the caret ( ^ ) immediately following the opening square bracket as that would change the meaning of the pattern.
Do not remove the caret ( ^ ) immediately following the opening square bracket as that would change the meaning of the pattern.