Need to remove leading character

[Ray W]

I have imported a .csv file and find that all records in the FIRSTNAME field
have an open quote mark at the beginning of the field ("Jones). I also have a
closed quote mark at the end of each record in my LOCATION field (Elm St")
What is the easiest way to purge the " from each of these fields?

 

[Jeff Boyce]

Ray

Since you are importing .csv data, there's a pretty good chance that the
data is not well-normalized.

To get the best use of Access, you need well-normalized data (tables).

Were this mine, I'd first import (or even link to!) the .csv file, then
create queries to "parse" the data from its raw version into my more
permanent, well-normalized Access table structure. During that parsing, I'd
use Left(), Mid() and/or Right() functions to strip away the unnecessary
characters before inserting/updating.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mikal via AccessMonster.com]

I have imported a .csv file and find that all records in the FIRSTNAME field ..snip..
What is the easiest way to purge the " from each of these fields?

Try this on a copy.
1. Open it in Word and do a find and replace. find " and replace it with a
space
2. Import the file into Access and use the TRIM() function to remove the
space.

HTH
Mike

 

[fredg]

I have imported a .csv file and find that all records in the FIRSTNAME field
have an open quote mark at the beginning of the field ("Jones). I also have a
closed quote mark at the end of each record in my LOCATION field (Elm St")
What is the easiest way to purge the " from each of these fields?

Back up your data first.
To remove the " only if it is the first character or the last
character in the field you can use:
Update YourTable Set YourTable.FirstName = Mid([FirstName],2) Where
Left([FirstName],1) = '"';

To remove the " only if it is at the end of the Location field, you
could use:
Update YourTable Set YourTable.Location =
Left([Location],Len([Location])-1) Where Right([Location],1) = '"';

Just for clarity, the criteria quotes above are ' " '

Or..
To remove all " in the field, regardless of how many there are, you
can use just one update query:
Update YourTable Set YourTable.[FirstName] =
Replace([FirstName],"""",""), YourTable.[Location] =
Replace([Location],"""","")

 

[John W. Vinson]

I have imported a .csv file and find that all records in the FIRSTNAME field
have an open quote mark at the beginning of the field ("Jones). I also have a
closed quote mark at the end of each record in my LOCATION field (Elm St")
What is the easiest way to purge the " from each of these fields?

Certainly persue Jeff's suggestions about normalization; but for a "quick and
dirty" cleanup you can remove ALL " marks from the field with an UPDATE query:

UPDATE yourtable
SET FIRSTNAME = Replace('"', ''), LOCATION = Replace('"', '')
WHERE FIRSTNAME LIKE '*["]*' OR LOCATION LIKE '*["]*';

 

[DavidBoyle via AccessMonster.com]

The requirement to replace quotes comes up frequently enough here, because
vba and sql handling of literal quotes is somewhat difficult to fathom for
the un-initiated.

The issue in vba, sql and all other programming language is that both double
and single quotes are commonly used as string delimiters, thus "Hello World!"
is actuially a representaion literally of Hello World! without the quotes
MsgBox("Hello World!") therefore does not display any quotes. Use then
function without delimiting your string with quotes and VBA would interpret
Hello World! as a variable name and give aqn error message as a variable
can't contain spaces. I realise that you will probably have worked that much
out by now, however you will realise that the special meaning of the "
character may cause problems when handling strings which contain literal
quotes. In common with many new users you have struggled to work out how to
replace literal quotes.

It is the tendency of developers not to explain things fully and to fully
understand the comments by others below, I feel it may help you to clarify
somewhat.

To follow up on some of your replies, the update query will work as given
however you may be curious regarding the expression replace(string,"""","")
which can be used in an update query to remove literal quotes stored in
tables. That is what you should do, pass four quotes and two quotes to the
replace function, but why use this obscure syntax?

The quotes, as stated previously are a string delimiter in VBA therefore ""
represents an empty zero length string. You are replacing occurences of
literal quotes with this empty string, the replace function works, by
replacing occurrences of the second argument with the third where they occur
in the string in the first argument. An argument in this context is not what
it would be to non geeks - a disagreement, it is simply a value passed to a
function.

To represent a quote in VBA you need to use two quotes "", however as stated
above this is interpretted as an empty string. A doubled quote within a
string expression is interpretted as a single one, therefore a single quote
is represented by four double quotes thus """".

If you wanted a message to say "Hello World!" including the quotes then your
code would say

Msgbox("""Hello World!""")

the doubled quotes being output as singles.

David

 

[Ray W]

While all of the posts were very informative. I found Mike's to be the
quickest, down and dirty, solution.

I certainly understand the need for a normalized table and I will keep that
in mind for the future.

In this case, the name field in the original file included both the Last and
First name seperated by a semi colon. In order to seperate the names, I
imported the file first by using the semi colon as the delimiter. This did
what I wanted it to do, but it also created a table that had only two fields,
the Last name, and then the Firstname with all of the other data which were
seperated with commas.

I then exported that and imported the new file with commas as the delimiter,
this worked, however, the " came along with the "first name and the last
field or Location".

Thank you all for being here, for novices like myself, your awesome.

 

[renhai]

I tried this but it did not work. Please advise what is not right, I want to
remove all " in table PERF_OS_TEMP, column DateTime.

UPDATE PERF_OS_TEMP
SET DateTime = Replace([DateTime],"""","")

Msg 1038, Level 15, State 4, Line 4
An object or column name is missing or empty. For SELECT INTO statements,
verify each column has a name. For other statements, look for empty alias
names. Aliases defined as "" or [] are not allowed. Add a name or single
space as the alias name.

I have imported a .csv file and find that all records in the FIRSTNAME field
have an open quote mark at the beginning of the field ("Jones). I also have a
closed quote mark at the end of each record in my LOCATION field (Elm St")
What is the easiest way to purge the " from each of these fields?

Back up your data first.
To remove the " only if it is the first character or the last
character in the field you can use:
Update YourTable Set YourTable.FirstName = Mid([FirstName],2) Where
Left([FirstName],1) = '"';

To remove the " only if it is at the end of the Location field, you
could use:
Update YourTable Set YourTable.Location =
Left([Location],Len([Location])-1) Where Right([Location],1) = '"';

Just for clarity, the criteria quotes above are ' " '

Or..
To remove all " in the field, regardless of how many there are, you
can use just one update query:
Update YourTable Set YourTable.[FirstName] =
Replace([FirstName],"""",""), YourTable.[Location] =
Replace([Location],"""","")

 

[John Spencer]

That looks like a message you would get with ANSI-Compliant SQL.

The following M_I_G_H_T work. Although I'm not sure you can use the
replace function.

UPDATE Perf_OS_Temp
SET Perf_OS_Temp.DateTime = REPLACE(Perf_OS_Temp.DateTime,Chr(34),"")

Also, what type of field is DateTime?

And DateTime seems like a bad name for a field. I would guess that
DateTime is a reserved word in Access.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I tried this but it did not work. Please advise what is not right, I want to
remove all " in table PERF_OS_TEMP, column DateTime.

UPDATE PERF_OS_TEMP
SET DateTime = Replace([DateTime],"""","")

Msg 1038, Level 15, State 4, Line 4
An object or column name is missing or empty. For SELECT INTO statements,
verify each column has a name. For other statements, look for empty alias
names. Aliases defined as "" or [] are not allowed. Add a name or single
space as the alias name.

I have imported a .csv file and find that all records in the FIRSTNAME field
have an open quote mark at the beginning of the field ("Jones). I also have a
closed quote mark at the end of each record in my LOCATION field (Elm St")
What is the easiest way to purge the " from each of these fields?

Back up your data first.
To remove the " only if it is the first character or the last
character in the field you can use:
Update YourTable Set YourTable.FirstName = Mid([FirstName],2) Where
Left([FirstName],1) = '"';

To remove the " only if it is at the end of the Location field, you
could use:
Update YourTable Set YourTable.Location =
Left([Location],Len([Location])-1) Where Right([Location],1) = '"';

Just for clarity, the criteria quotes above are ' " '

Or..
To remove all " in the field, regardless of how many there are, you
can use just one update query:
Update YourTable Set YourTable.[FirstName] =
Replace([FirstName],"""",""), YourTable.[Location] =
Replace([Location],"""","")