Solved

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

Posted on 2015-01-02
14
255 Views
Last Modified: 2015-01-09
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
Comment
Question by:juricta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 200 total points
ID: 40528768
instead of using this format

'" & OLCCDescription & "'

use

chr(34) &  OLCCDescription & chr(34)
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 40528970
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40529188
@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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:juricta
ID: 40529373
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
 

Author Comment

by:juricta
ID: 40529376
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40529377
<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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40529397
and do I nee to ADD anything quotation or other marks to you code?
the complete syntax is

" & chr(34) & OLCCDescription & chr(34) & "
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
ID: 40529410
@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
 
LVL 12

Expert Comment

by:jkaios
ID: 40530808
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
 
LVL 85
ID: 40531245
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
 
LVL 12

Expert Comment

by:jkaios
ID: 40532472
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
 

Author Comment

by:juricta
ID: 40539397
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40540039
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
 

Author Closing Comment

by:juricta
ID: 40541549
Thanks to all who provided input.  This program would not have been possible without that input!!!!!

taj
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

705 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