Remove commas w/in quotes

[RJF]

I import a fixed txt file into access using this spec:

FieldName Data Type Start Width
Field1 Memo 1 3000

Example of how it comes in.

"468433","Microsoft Word - Preliminary General Project Requirements, Brown
CAC, 4-7-09 Update.doc","SBarbary","\\172.20.113.93",

After the import, I need to remove all commas that are located between the
quotes.

So I need an update query that changes the above line to:

"468433","Microsoft Word - Preliminary General Project Requirements Brown
CAC 4-7-09 Update.doc","SBarbary","\\172.20.113.93",

I think I got the following from this forum:

Left([field1],InStr(1,[field1],Chr(34))-1) &
replace(Mid([field1],InStr(1,[field1],Chr(34)),InStrrev([field1],Chr(34))-7),","," ") & Mid([field1],InStrrev([field1],Chr(34))+1,1000)

And I thought it was working, but it’s not. It removes all the commas.

Any ideas on what I'm doing wrong? Any help will be greatly appreciated.

Thank you.

 

[PJFry]

It seems that you be importing this as a CSV file and placing each of those
data elements into a seperate field.
Is there a reason that all the data has be contained in one field?

 

[John Spencer]

IF (mighty important word) the sections are ALL separated by ",", then
you can write a VBA function to split the string into sections, remove
the commas in the sections and recombine the sections.

The VBA function might look something like the following UNTESTED code.
In a query you would Update to
fRemovePeskyCommas([YourTableName].[YourFieldName]

BACKUP YOUR DATA BEFORE YOU TRY THIS. If this does not work the way you
expect, that may be the only way to recover.

Public Function fRemovePeskyCommas(strIn as Variant) as variant
Dim T as Variant
Dim i as Long
Dim sReturn as String

If Len(strIn & "") = 0 Then
fRemovePeskyCommas = strIn
ELSE
T = Split(strIn,""",""")
For I = LBound(T) to Ubound(T)
T(i) = Replace(T(i),",","")
Next I

For I=Lbound(T) to UBound(T)
sReturn = sReturn & """,""" & T(i)
Next I

fRemovePeskyCommas = Mid(sReturn,4)

END IF
End Function

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

 

[RJF]

Sorry, I should have given you more information. I had problems importing
the file and had to use some code that fixes the problems before I import
into Access. Because of that, I needed to import into one field. I don’t
remember the exact problem that I was having, but it had to do with carriage
returns within the body of the csv file which was messing with the import
when I tried to bring them into separate fields. Robert Morley saw my
posting regarding that and helped me with some code. The code goes out the
csv file and fixes the carriage returns problem before I import. It also
eliminates the quotes found within a field surrounded by quotes. After this
is done, I import the file, then run queries to do the following:

1. Replace the semi colons with nothing.
2. Delete the titles.
3. Delete the commas that are in quoted fields. (this is the part that’s
not working)
4. Replace the quotes with nothing.
5. Replace the commas with semi colons.

Then I split the table into separate fields. I know that this may be the
long way around and not the most efficient, but it’s working well for me and
since I’m bombarded with work right now, I’d rather not change anything
except to get the query working that deletes the commas within the quoted
fields.

Thank you so much for your help.

--
RJF

It seems that you be importing this as a CSV file and placing each of those
data elements into a seperate field.
Is there a reason that all the data has be contained in one field?
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

I import a fixed txt file into access using this spec:

FieldName Data Type Start Width
Field1 Memo 1 3000

Example of how it comes in.

"468433","Microsoft Word - Preliminary General Project Requirements, Brown
CAC, 4-7-09 Update.doc","SBarbary","\\172.20.113.93",

After the import, I need to remove all commas that are located between the
quotes.

So I need an update query that changes the above line to:

"468433","Microsoft Word - Preliminary General Project Requirements Brown
CAC 4-7-09 Update.doc","SBarbary","\\172.20.113.93",

I think I got the following from this forum:

Left([field1],InStr(1,[field1],Chr(34))-1) &
replace(Mid([field1],InStr(1,[field1],Chr(34)),InStrrev([field1],Chr(34))-7),","," ") & Mid([field1],InStrrev([field1],Chr(34))+1,1000)

And I thought it was working, but it’s not. It removes all the commas.

Any ideas on what I'm doing wrong? Any help will be greatly appreciated.

Thank you.

 

[RJF]

John, you the man!

It worked perfectly, first try. Thank you so much.

You guys are so great!
--
RJF

IF (mighty important word) the sections are ALL separated by ",", then
you can write a VBA function to split the string into sections, remove
the commas in the sections and recombine the sections.

The VBA function might look something like the following UNTESTED code.
In a query you would Update to
fRemovePeskyCommas([YourTableName].[YourFieldName]

BACKUP YOUR DATA BEFORE YOU TRY THIS. If this does not work the way you
expect, that may be the only way to recover.

Public Function fRemovePeskyCommas(strIn as Variant) as variant
Dim T as Variant
Dim i as Long
Dim sReturn as String

If Len(strIn & "") = 0 Then
fRemovePeskyCommas = strIn
ELSE
T = Split(strIn,""",""")
For I = LBound(T) to Ubound(T)
T(i) = Replace(T(i),",","")
Next I

For I=Lbound(T) to UBound(T)
sReturn = sReturn & """,""" & T(i)
Next I

fRemovePeskyCommas = Mid(sReturn,4)

END IF
End Function

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

I import a fixed txt file into access using this spec:

FieldName Data Type Start Width
Field1 Memo 1 3000

Example of how it comes in.

"468433","Microsoft Word - Preliminary General Project Requirements, Brown
CAC, 4-7-09 Update.doc","SBarbary","\\172.20.113.93",

After the import, I need to remove all commas that are located between the
quotes.

So I need an update query that changes the above line to:

"468433","Microsoft Word - Preliminary General Project Requirements Brown
CAC 4-7-09 Update.doc","SBarbary","\\172.20.113.93",

I think I got the following from this forum:

Left([field1],InStr(1,[field1],Chr(34))-1) &
replace(Mid([field1],InStr(1,[field1],Chr(34)),InStrrev([field1],Chr(34))-7),","," ") & Mid([field1],InStrrev([field1],Chr(34))+1,1000)

And I thought it was working, but it’s not. It removes all the commas.

Any ideas on what I'm doing wrong? Any help will be greatly appreciated.

Thank you.