?
Solved

Strip comma, tabs, carriage returns, tabs etc from text field

Posted on 2008-11-11
12
Medium Priority
?
1,183 Views
Last Modified: 2013-11-27
I have a ver large database that I need to export to a tab delimited file for import into another system.  The problem I have is there is a note field, and others, that have commas, tabs, quotes, tabs, carraige returns etc. in it.  naturally this reaks havoc on a tab delimited file.

Is there a way to query or code against these fields to either remove them or replace them?
0
Comment
Question by:keschuster
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 10

Expert Comment

by:LennyGray
ID: 22934237
It appears that you only need to edit-out the tabs within the data elements of the table. The problem is that the tabs are your only problem. So, simply do a search and replace in each data column that contains tabs and replace the tabs with some other special character. You can then replace the special character with the tab after the export.
0
 

Author Comment

by:keschuster
ID: 22934556
I've figured this much out
UPDATE Parcel SET Parcel.[Property Name] = Replace([Property Name],Chr(13),"|")
checks for one character and replaces.  there are 4 characters I need to check for across 4 or more fields.

Instead of running 4 queries against each of the 4 fields I'd like to code tthis into a function that I could have update all of these characters via a call from one query.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 22934574
Would you want to use | instead of tab to seperate your fields.

| is not a very common character and it is located on the same key as \.

| looks like I o l but it is neither.

Mike
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:keschuster
ID: 22934641
normally I would but tab is a requirment
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 22934973
Add |

Replace all unwanted ones

Replace | with tabs.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 22934985
sorry, I didn't read your post fully. You already had | in mind.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1500 total points
ID: 22935742
Hi Mike :)

kes, I have a vba func which you could use. It is used to strip out the crap that you dont want

Public Function GetRidOfThoseNastyChars(sMemoText As String) As String

    Dim sReturn As String
    Dim x
   
    sReturn = ""
    For x = 1 To Len(sMemoText)
        If (Asc(Mid(sMemoText, x, 1)) >= 48 And Asc(Mid(sMemoText, x, 1)) < 57) Or _
        (Asc(Mid(sMemoText, x, 1)) >= 97 And Asc(Mid(sMemoText, x, 1)) < 122) Or _
        (Asc(Mid(sMemoText, x, 1)) >= 65 And Asc(Mid(sMemoText, x, 1)) < 90) Then
            sReturn = sReturn & Mid(sMemoText, x, 1)
        End If
    Next
   
    GetRidOfThoseNastyChars = sReturn

End Function



Now you can use this in a query eg

update mytable set myfield = GetRidOfThoseNastyChars(myfield)

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 22935745
Simple solution, basically iterate thru each char in the specified string and build up a new string by accepting those 0-9, a-z or A-Z ranges only.
0
 

Author Comment

by:keschuster
ID: 22935871
That looks about what I was thinking.  How do you think this will perfom on 80k records with each record having 4 columns to be evaluated.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 22935920
Well only one way to find out

My personal choice would be to create the whole code in vba. Create a recordset and iterate thru that.
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22936202
you did not allow for spaces (Ascii 20)
0
 

Author Comment

by:keschuster
ID: 22936220
I ran the code and the speed was just fine.  I do want to modify to look for specific characters and replace them with a space.  But this is the right way to go
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

809 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