Copying txt file into field line by line (1 Viewer)

[frustrating]

Greetings:
I am trying to import text line by line (it's html source code), but access imports it out of order if I use the import tool, so I am running code that will paste it line by line.

I have most of the code finished, but the text file i wish to import has apostrophes in it, which won't go into the tables. I'd like everything to be automated, is there any way this can be done? What I'm using now:

Dim i As Integer
Dim strPathAndFile As String
Dim strHold As String
Dim strSQL As String
strPathAndFile = "P:\test1.txt"
i = FreeFile
Open strPathAndFile For Input As #i
Do Until EOF(i)
Line Input #i, strHold
strSQL = "Insert INTO Master ([Field1]) Values ('" & Chr(34) & strHold & Chr(34) & "')"
CurrentDb.Execute strSQL, dbFailOnError
Loop
Close #i

 

[nanscombe]

You could try replacing the offending character (') with it's HTML Markup equivalent ( ' ) which is what the naughty writer of the webpage should probably be doing anyway.

Dim i As Integer
Dim strPathAndFile As String
Dim strHold As String
Dim strSQL As String
strPathAndFile = "P:\test1.txt"
i = FreeFile
Open strPathAndFile For Input As #i
Do Until EOF(i)
  Line Input #i, strHold

  [COLOR="Red"]strHold = Replace(strHold, Chr(39), "'")[/COLOR] ' Replace apostrophe with '
  [COLOR="Red"]strHold = Replace(strHold, Chr(34), """)[/COLOR] ' Replace double quote with "

  strSQL = "Insert INTO Master ([Field1]) Values ('" & Chr(34) & strHold & Chr(34) & "')"
  CurrentDb.Execute strSQL, dbFailOnError
Loop
Close #i

Or just replace a single apostrophe with two apostrophes strHold = Replace(strHold, Chr(39), Chr(39) & Chr(39))

 

[Booker-T]

Does anyone know how to remove the ' from data stored in a table? For example, I have the following text stored in a table: "I didn't see that coming." Is there a way to clean that data up so that it says: "I didn't see that coming."? Thanks in advance!

 

[CJ_London]

to follow up on nanscome's suggestion, replace ' with two single quotes. e.g.

replace("I didn't see that coming.","'","''")

will give you

I didn''t see that coming.

which will import to your field as

I didn't see that coming.

 

[Booker-T]

Thanks for the quick response! I used this on the report field and it did work perfectly.
=Replace([InstantMessage],"'","'")