Solved

Remove "Non Text / Numeric" values from String

Posted on 2010-09-02
7
785 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:tfewster500
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33586257
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
0
 
LVL 11

Expert Comment

by:madgino
ID: 33586278
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?
0
 
LVL 5

Accepted Solution

by:
roxviper earned 500 total points
ID: 33586301
Hi,

Maybe the most elegant way to do it is with Regex,as you said.

For that check those links:

http://visualbasic.about.com/od/usingvbnet/a/RegExNET_2.htm

and this. A gallery with some usual regex

http://wiki.tcl.tk/989
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33586437
How are you exporting the data to the text file?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33591804
>>  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).
Dim result As String = System.Text.RegularExpressions.Regex.Replace(your_text, "[^a-zA-Z0-9 $%#@!*&()[\]""':;-]")

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33591817
Forgot the replacement!
Dim result As String = System.Text.RegularExpressions.Regex.Replace(your_text, "[^a-zA-Z0-9 $%#@!*&()[\]""':;-]", String.Empty)

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33591830
P.S.

Do not remove the caret ( ^ ) immediately following the opening square bracket as that would change the meaning of the pattern.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
c#, case, if 4 38
Adjust the codes 3 64
Learning About The VB.NET TableLayoutPanel Control 5 42
SSIS Column mapping 5 42
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question