• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

How to automatically import text with APOSTROPHE (')s in the string

I have a text file containing 6900 rows (each row will be a record).  I  imported this file into Access 2010 table (tblNewItem).  The records are text and contain 63 characters for each record/line.  (this is OK)

I then have to take tblNewItem and parse each record into 10 different parts.  Each part is assigned to a variable.  One of these parts is a TITLE which  I have found out that many of these contain apostrophes (').

I need to import this record fiIe into Access 2010 table (tblNew2).  I do this by using the code below which executes an INSERT statement.  Because this statement uses SINGLE QUOTE (which is an apostrophe) to separate the variables and text, I am getting a conflict with any variable that contains the apostrophe (ie the Description/Title-OLCCDescription).  By regulations I MUST keep the string as is (with apostrophes) when I import the file into Access 2010.  These rows are randomly written so there  is no way to know when/where an apostrophe will show up.  This code works great EXCEPT when a record has an apostrophe.  Here is an exampple of a problem description: DEATH'S DOOR VODKA

 Does anyone know of a way to make this happen?

Here is my INSERT code:
            Set mydb = CurrentDb
            mydb.Execute "INSERT INTO tblItemMaster ([OLCCItemCode],[OLCCAlphaItemCode],[OLCCDescription],[OLCCAge],[OLCCAgeUnit],[OLCCProof],[OLCCSize],[OLCCPrice],[OLCCBottlesPerCase],[OLCCStatus]) SELECT '" & OLCCItemCode & "','" & OLCCAlphaItem & "','" & OLCCDescription & "','" & OLCCAge & "','" & OLCCAgeUnit & "','" & OLCCProof & "','" & OLCCSize & "','" & OLCCPrice & "','" & OLCCBottlesPerCase & "','" & OLCCStatus & "'"
0
juricta
Asked:
juricta
  • 4
  • 3
  • 3
  • +2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
instead of using this format

'" & OLCCDescription & "'

use

chr(34) &  OLCCDescription & chr(34)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can also use the Replace function:

Replace(OLCCDescription, "'", "''")

This replaces any single quotes ( ' ) with two single quotes ( ''). This is known as "escaping" a character.
0
 
Dale FyeCommented:
@Scott,

After all of these years, I didn't realize there was actually a term "escaping" associated with that process.

@Juricta,

I'll go one step beyond what Rey and Scott have provided.  I have a function I use for this process which makes it easier to read.  The function looks like:
Public Function fnWrap(WrapWhat as Variant, _
                  Optional WrapWith as String = """") as String

    if IsNull(WrapWhat) then
        fnWrap = "NULL"
    ELSE
        fnWrap = WrapWith _
               & Replace(WrapWhat, WrapWith, WrapWith & WrapWith) _
               & WrapWith
    End If

End Function

Open in new window

Then, in my code I would use:
strSQL = "INSERT INTO tblItemMaster ([OLCCItemCode],[OLCCAlphaItemCode], " _
        & "[OLCCDescription],[OLCCAge],[OLCCAgeUnit],[OLCCProof],[OLCCSize], " _
        & "[OLCCPrice],[OLCCBottlesPerCase],[OLCCStatus]) " _
        & "SELECT " & fnWrap(OLCCItemCode) & "," _
                    & fnWrap(OLCCAlphaItem) & "," _
                    & fnWrap(OLCCDescription) & "," _
                    & fnWrap(OLCCAge) & "," _
                    & fnWrap(OLCCAgeUnit) & "," _
                    & fnWrap(OLCCProof) & "," _
                    & fnWrap(OLCCSize) & "," _
                    & fnWrap(OLCCPrice) & "," _
                    & fnWrap(OLCCBottlesPerCase) & "," _
                    & fnWrap(OLCCStatus) 
mydb.Execute strsql, dbfailonerror

Open in new window

The nice thing about this function is that you can use it to wrap text with a double quote ("), single quote ('), or even with a # (for dates).  I'm concerned that you appear to be writing what are obviously numeric values (Age, Price, BottlesPerCase) into text fields.  Are you sure you want to do that?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
jurictaAuthor Commented:
A question or two for each:
1.  REY:  When I use your code (chr(34) & OLCCDescription & chr(34)) - Will the description retain its original apostrophe and do I nee to ADD anything quotation or other marks to you code?

2. SCOTT: Because the state forces us to use THEIR description I cannot use your code WITHOUT having to replace the double quotation mark before opening the table for use. I MUST keep the possessive apostrophe.

3. DALE:From what I can see your code WrapWhat would be my apostrophe?  WrapWith would be whatever I choose?  But I am still missing the apostrophe?
0
 
jurictaAuthor Commented:
Dale: I AM writing what appears to be numeric values to text.  The requirement is that the user must later convert them to numeric or date values.  The entire text file given by the state has these values.  It is OK in this step of the program to leave them as text (at least until later)
Thanks
0
 
Rey Obrero (Capricorn1)Commented:
<When I use your code (chr(34) & OLCCDescription & chr(34)) - Will the description retain its original apostrophe
YES
and do I nee to ADD anything quotation or other marks to you code?
NO
0
 
Rey Obrero (Capricorn1)Commented:
and do I nee to ADD anything quotation or other marks to you code?
the complete syntax is

" & chr(34) & OLCCDescription & chr(34) & "
0
 
Dale FyeCommented:
@juricta,

I gave you the code to use.  It actually is using the default value of the function which would be double quotes, instead of single quotes.  And because it uses double quotes as the default, you don't have to worry about the single quote (apostrophe) embedded is some of the variables.  If you want to wrap the text in single quotes (there is no reason to do this), you would use:
strSQL = "INSERT INTO tblItemMaster ([OLCCItemCode],[OLCCAlphaItemCode], " _
        & "[OLCCDescription],[OLCCAge],[OLCCAgeUnit],[OLCCProof],[OLCCSize], " _
        & "[OLCCPrice],[OLCCBottlesPerCase],[OLCCStatus]) " _
        & "SELECT " & fnWrap(OLCCItemCode) & "," _
                    & fnWrap(OLCCAlphaItem, "'") & "," _
                    & fnWrap(OLCCDescription, "'") & "," _
                    & fnWrap(OLCCAge, "'") & "," _
                    & fnWrap(OLCCAgeUnit, "'") & "," _
                    & fnWrap(OLCCProof, "'") & "," _
                    & fnWrap(OLCCSize, "'") & "," _
                    & fnWrap(OLCCPrice, "'") & "," _
                    & fnWrap(OLCCBottlesPerCase, "'") & "," _
                    & fnWrap(OLCCStatus, "'") 
debug.print strSQL
mydb.Execute strsql, dbfailonerror

Open in new window

Add a debug.print just above the Execute so you can see the results of the strSQL.
0
 
jkaiosCommented:
Try this.  Notice the addition in bold.  Exising quotes (apostrophies) will be kept intact.

Set mydb = CurrentDb
            mydb.Execute "INSERT INTO tblItemMaster ([OLCCItemCode],[OLCCAlphaItemCode],[OLCCDescription],[OLCCAge],[OLCCAgeUnit],[OLCCProof],[OLCCSize],[OLCCPrice],[OLCCBottlesPerCase],[OLCCStatus]) SELECT '" & OLCCItemCode & "','" & OLCCAlphaItem & "','" & Replace(OLCCDescription, "'", "''") & "','" & OLCCAge & "','" & OLCCAgeUnit & "','" & OLCCProof & "','" & OLCCSize & "','" & OLCCPrice & "','" & OLCCBottlesPerCase & "','" & OLCCStatus & "'"
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
jkaios: I suggested the use of the Replace function earlier.

Because the state forces us to use THEIR description I cannot use your code WITHOUT having to replace the double quotation mark before opening the table for use. I MUST keep the possessive apostrophe.
Any of these suggestions would do exactly that. For example, if I INSERTed a record with a name of O'Brian in the "txName" field, I'd issue this:

INSERT INTO SomeTable(Field1) VALUES('" & Replace(Me.txName, "'", "''") & "")

That would insert a value of O'Brian, not O''Brian.
0
 
jkaiosCommented:
Scott: you're right!  My apologies for the overlook.

Juricta:  I recommend that you try Scott's method.  It does work.  The Replace function (regardless of its meaning and name) DOES NOT actually remove any single quote from the string, it only allows the INSERT clause to accept apostrophes in the process.
0
 
jurictaAuthor Commented:
Moderator and Experts-I could use your input.  Rey was the first to offer a solution. But the other solutions were just as go in that they also did the job reuired.  Within the group and your rules, is it OK to offewr 1/3 points for the solutions or 100% for the first working entry (Rey)??  Based on your input I will be back tomorrow to award points

Thanks to ALL who offered input!!
0
 
Dale FyeCommented:
Award of points is up to you.  You can choose to award all of the points to one contributor or split them up, as you see fit.  But if you award all the points to a post that has a similar solution to an earlier post, you should explain why you awarded the points to that post; sometimes this may be the difference between an answer which simply posts the fix and one that explains why your original didn't work and why the new answer does.
0
 
jurictaAuthor Commented:
Thanks to all who provided input.  This program would not have been possible without that input!!!!!

taj
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now