?
Solved

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

Posted on 2015-01-02
14
Medium Priority
?
280 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 800 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 600 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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
Dale Fye earned 600 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

800 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