Solved

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

Posted on 2015-01-02
14
236 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 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

732 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