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

 

[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 '*["]*';

 

[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],"""","")