Solved

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

Posted on 2015-01-02
14
166 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
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 119

Accepted Solution

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

'" & OLCCDescription & "'

use

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

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 47

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
 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

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 84
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 47

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now