DoCmd.TransferText & variables?

  • Thread starter Thread starter Guest
  • Start date Start date
OK, that rids the "item not found in this collection" error.
And the code will now import records; but it still errors
when it hits memo data in the text file.
Run-Time Error 3163: The field is too small to accept
the amount of data you attempted to add; try inserting
or pasting less data.
I assume that will require an additional if statement using the Append Chunk
function?
--
LF


Ken Snell (MVP) said:
If the hover value is this:
"string"

then it's correct.

If it's this:
""string""

then the value in the variable needs to have the leading and trailing "
characters stripped out. This can be done this way, assuming that there
would not be a field name beginning with or ending with " character:

For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(varColumn(lngDelimCount), 1) = Chr(34) And _
Right(varColumn(lngDelimCount), 1) = Chr(34) Then _
varColumn(lngDelimCount) = Mid(varColumn(lngDelimCount),
_
2, Len(varColumn(lngDelimCount)) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount

--

Ken Snell
<MS ACCESS MVP>



LF said:
Yes the hover value is in the recordset table, but I noticed that the
hover
value is surrounded by double quotes> do those quotes need to be stripped
out
first?

I tried two seperate text files to rule out that the content was the
source
of the problem. I also tried converting the variant column to a string and
got the same error in both instances.
strCol = varColumn(lngDelimCount))
rs.Fields(strCol).Value = varRow(lngDelimCount)

--
LF


Ken Snell (MVP) said:
When you get the error, hold cursor over the varColumn variable and note
what the value of that variable is. Is that field in the table that's the
basis of the recordset?

--

Ken Snell
<MS ACCESS MVP>


Almost there, although I can see in the VB Locals pane that it returns
the
expected varColumn, varRow, & lngDelimCount variables. it errors "Item
not
found in this collection" at the rs.Fields Value property in step 8.

One of the fields datatype is Memo; does that matter?

--
LF


:

Sorry -- I overlooked that you're bringing the values of strFilePath
and
strTbl to the sub. Here is corrected code:

Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & srtTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Forgot to add this to my previous reply. It appears to grab all of the memo
content, but it tries to insert it into the preceeding field which is not a
memo datatype; it also does not strip the quotes from the value.
 
Memo fields should not be a problem for this method/code. And, if the memo
field is in the appropriate location in the record (i.e., corresponding to
the field name in first row of text file that is the memo type), it
shouldn't be trying to put it into the previous field in the recordset.

I assume that your previously posted table and data info are still valid
here:

Table:
.... | RFPid: AutoNumber | RFPdoc: Text | RFPnum: Text | RFPsort: Text
.... | RFPsec: Text | RFPpara: Memo | Annotation: Memo | Response: Memo
.... | Compliance: Text | Risk: Text | UpdateBy: Text | UpdateWhen: Date/Time

a few sample rows of data from the text file,
.... "RFPdoc", "RFPnum", "RFPsec", "RFPpara"
.... "SOW", "1.1.2", "Introduction", "The solution shall provide an improved
widget."
.... "SOW", "1.1.2", "Introduction", "The soultion shall reduce cycle time."
.... "SOW", "1.3", "System Architecture", " The current system should be
replaced."
.... "L", "2.4", "Proposal Submital", "Submitted documents shall include..."
.... "L", "2.5", "Submital Formats", "The page margins should not exceed 1
inch."



By chance, do the data in the memo fields or other fields contain commas
(your field delimiter)? If yes, then the code is truncating the data because
of the Split function. So we need to "protect" the commas until after the
Split function, then "unprotect" them afterwards. The code to do this is a
bit tricky; it would be easier if you could use a different field delimiter
(such as | character). Is this possible?

Try this code - I've also made changes in the " character stripping to
remove the " characters from varRow values too -- I've not added the
"protection / unprotection" of the comma delimiter at this point, awaiting
your reply about whether you can use a different delimiter -- if not, post
back and I'll put together some code for you to do this (would do sometime
this evening):


Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
2, Len(Trim(varColumn(lngDelimCount))) - 2)
Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & srtTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
2, Len(Trim(varRow(lngDelimCount))) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub

--

Ken Snell
<MS ACCESS MVP>
 
Yes, the memo field is in the appropriate location in the record.

Yes, the previously posted table and data info are still valid.

Yes, the data in the memo fields or other fields contain commas;
as well as | charachters. I went with CSV syntax due to the
originating source file either being MS-Word or MS-Excell.
Also because my original DoCmd.TransferText used import specs
that support CSV delimits.

Can the delimitor constant accomodate a multi-character delimitor
such as ", " (i.e. quote, comma, space quote)?

Your latest code modification removes all of the surrounding quotes
except for the beginning of the the last field value.

I appreciate your patience with the back and forth regarding this issue.

--
LF
Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
2, Len(Trim(varColumn(lngDelimCount))) - 2)
Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & srtTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
2, Len(Trim(varRow(lngDelimCount))) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub

--

Ken Snell
<MS ACCESS MVP>

LF said:
Forgot to add this to my previous reply. It appears to grab all of the
memo content, but it tries to insert it into the preceeding field which
is not a memo datatype; it also does not strip the quotes from the value.
Click to expand...
Click to expand...
 
No, the Split function can accommodate only a single-character delimiter, so
", " won't work. The reason the " character remains at beginning of the memo
field is because it's being truncated and the string doesn't end with a "
character (the code looks for preceding and trailing " characters).

Let me have a bit of time here and see if I can write code to protect /
unprotect the comma character embedded within the strings.
--

Ken Snell
<MS ACCESS MVP>





LF said:
Yes, the memo field is in the appropriate location in the record.

Yes, the previously posted table and data info are still valid.

Yes, the data in the memo fields or other fields contain commas;
as well as | charachters. I went with CSV syntax due to the
originating source file either being MS-Word or MS-Excell.
Also because my original DoCmd.TransferText used import specs
that support CSV delimits.

Can the delimitor constant accomodate a multi-character delimitor
such as ", " (i.e. quote, comma, space quote)?

Your latest code modification removes all of the surrounding quotes
except for the beginning of the the last field value.

I appreciate your patience with the back and forth regarding this issue.

--
LF
Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
varColumn(lngDelimCount) =
Mid(Trim(varColumn(lngDelimCount)), _
2, Len(Trim(varColumn(lngDelimCount))) - 2)
Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & srtTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then
_
varRow(lngDelimCount) =
Mid(Trim(varRow(lngDelimCount)),
_
2, Len(Trim(varRow(lngDelimCount))) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub

--

Ken Snell
<MS ACCESS MVP>

LF said:
Forgot to add this to my previous reply. It appears to grab all of the
memo content, but it tries to insert it into the preceeding field which
is not a memo datatype; it also does not strip the quotes from the
value.
Click to expand...
Click to expand...
Click to expand...
 
OK - I've written a function (DelimProtect) that will protect or unprotect
an embedded delimiter within a text string. To protect an embedded
delimiter, it converts the specified delimiter character to ASCII character
7 (highly unlikely that any text string from your sources is going to
contain this character); to unprotect, it converts any ASCII character 7
back to the delimiter character. The function is at the end of the following
code; put it in the same module where your original subroutine is located.


Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'_2a) Protect embedded delimiter characters
strColumns = DelimProtect(strColumns, strDelim, True)
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
'_3a) Unprotect embedded delimiter characters
strColumns = DelimProtect(strColumns, strDelim, False)
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_3b) Unprotect embedded delimiter characters
varColumn(lngDelimCount) = DelimProtect(varColumn(lngDelimCount),
strDelim, False)
If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
2, Len(Trim(varColumn(lngDelimCount))) - 2)
Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & strTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'_5a) Protect embedded delimiter characters
strRow = DelimProtect(strRow, strDelim, True)
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_8a) Unprotect embedded delimiter characters
varRow(lngDelimCount) = DelimProtect(varRow(lngDelimCount),
strDelim, False)
If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
2, Len(Trim(varRow(lngDelimCount))) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub



Public Function DelimProtect(strString As Variant, strDelim As String,
blnProtect As Boolean) As Variant

Dim blnInString As Boolean
Dim lngLen As Long, lngLoop As Long
Dim strProtect As String, strChar As String
Dim strNewString As String

strProtect = Chr(7)
lngLen = Len(strString)
strNewString = ""

If blnProtect = True Then
lngLoop = 1
Do While lngLoop <= lngLen
strChar = Mid(strString, lngLoop, 1)
If strChar = Chr(34) Then
If blnInString = True Then
If Mid(strString, lngLoop + 1, 1) = Chr(34) Then
strNewString = strNewString & strChar
lngLoop = lngLoop + 1
strChar = Mid(strString, lngLoop, 1)
ElseIf Mid(strString, lngLoop + 1, 1) = "," Then
blnInString = False
End If
Else
blnInString = True
End If
ElseIf strChar = strDelim Then
If blnInString = True Then strChar = strProtect
End If
strNewString = strNewString & strChar
lngLoop = lngLoop + 1
Loop
Else
For lngLoop = 1 To lngLen
strChar = Mid(strString, lngLoop, 1)
If strChar = strProtect Then strChar = strDelim
strNewString = strNewString & strChar
Next lngLoop
End If

DelimProtect = strNewString

Exit Function
End Function

--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
No, the Split function can accommodate only a single-character delimiter,
so ", " won't work. The reason the " character remains at beginning of the
memo field is because it's being truncated and the string doesn't end with
a " character (the code looks for preceding and trailing " characters).

Let me have a bit of time here and see if I can write code to protect /
unprotect the comma character embedded within the strings.
--

Ken Snell
<MS ACCESS MVP>





LF said:
Yes, the memo field is in the appropriate location in the record.

Yes, the previously posted table and data info are still valid.

Yes, the data in the memo fields or other fields contain commas;
as well as | charachters. I went with CSV syntax due to the
originating source file either being MS-Word or MS-Excell.
Also because my original DoCmd.TransferText used import specs
that support CSV delimits.

Can the delimitor constant accomodate a multi-character delimitor
such as ", " (i.e. quote, comma, space quote)?

Your latest code modification removes all of the surrounding quotes
except for the beginning of the the last field value.

I appreciate your patience with the back and forth regarding this issue.

--
LF
Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
varColumn(lngDelimCount) =
Mid(Trim(varColumn(lngDelimCount)), _
2, Len(Trim(varColumn(lngDelimCount))) - 2)
Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & srtTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then
_
varRow(lngDelimCount) =
Mid(Trim(varRow(lngDelimCount)),
_
2, Len(Trim(varRow(lngDelimCount))) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub

--

Ken Snell
<MS ACCESS MVP>

Forgot to add this to my previous reply. It appears to grab all of the
memo content, but it tries to insert it into the preceeding field
which
is not a memo datatype; it also does not strip the quotes from the
value.
Click to expand...
Click to expand...
Click to expand...
 
Rats -- typo in the code -- here is corrected copy:


Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'_2a) Protect embedded delimiter characters
strColumns = DelimProtect(strColumns, strDelim, True)
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_3a) Unprotect embedded delimiter characters
varColumn(lngDelimCount) = DelimProtect(varColumn(lngDelimCount),
strDelim, False)
If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
varColumn(lngDelimCount) = Mid(Trim(varColumn(lngDelimCount)), _
2, Len(Trim(varColumn(lngDelimCount))) - 2)
Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & strTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'_5a) Protect embedded delimiter characters
strRow = DelimProtect(strRow, strDelim, True)
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
'_8a) Unprotect embedded delimiter characters
varRow(lngDelimCount) = DelimProtect(varRow(lngDelimCount),
strDelim, False)
If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varRow(lngDelimCount)), 1) = Chr(34) Then _
varRow(lngDelimCount) = Mid(Trim(varRow(lngDelimCount)),
_
2, Len(Trim(varRow(lngDelimCount))) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub



Public Function DelimProtect(strString As Variant, strDelim As String,
blnProtect As Boolean) As Variant

Dim blnInString As Boolean
Dim lngLen As Long, lngLoop As Long
Dim strProtect As String, strChar As String
Dim strNewString As String

strProtect = Chr(7)
lngLen = Len(strString)
strNewString = ""

If blnProtect = True Then
lngLoop = 1
Do While lngLoop <= lngLen
strChar = Mid(strString, lngLoop, 1)
If strChar = Chr(34) Then
If blnInString = True Then
If Mid(strString, lngLoop + 1, 1) = Chr(34) Then
strNewString = strNewString & strChar
lngLoop = lngLoop + 1
strChar = Mid(strString, lngLoop, 1)
ElseIf Mid(strString, lngLoop + 1, 1) = "," Then
blnInString = False
End If
Else
blnInString = True
End If
ElseIf strChar = strDelim Then
If blnInString = True Then strChar = strProtect
End If
strNewString = strNewString & strChar
lngLoop = lngLoop + 1
Loop
Else
For lngLoop = 1 To lngLen
strChar = Mid(strString, lngLoop, 1)
If strChar = strProtect Then strChar = strDelim
strNewString = strNewString & strChar
Next lngLoop
End If

DelimProtect = strNewString

Exit Function
End Function

--

Ken Snell





Ken Snell (MVP) said:
OK - I've written a function (DelimProtect) that will protect or unprotect
an embedded delimiter within a text string. To protect an embedded
delimiter, it converts the specified delimiter character to ASCII
character 7 (highly unlikely that any text string from your sources is
going to contain this character); to unprotect, it converts any ASCII
character 7 back to the delimiter character. The function is at the end of
the following code; put it in the same module where your original
subroutine is located.



Ken Snell (MVP) said:
No, the Split function can accommodate only a single-character delimiter,
so ", " won't work. The reason the " character remains at beginning of
the memo field is because it's being truncated and the string doesn't end
with a " character (the code looks for preceding and trailing "
characters).

Let me have a bit of time here and see if I can write code to protect /
unprotect the comma character embedded within the strings.
--

Ken Snell
<MS ACCESS MVP>





LF said:
Yes, the memo field is in the appropriate location in the record.

Yes, the previously posted table and data info are still valid.

Yes, the data in the memo fields or other fields contain commas;
as well as | charachters. I went with CSV syntax due to the
originating source file either being MS-Word or MS-Excell.
Also because my original DoCmd.TransferText used import specs
that support CSV delimits.

Can the delimitor constant accomodate a multi-character delimitor
such as ", " (i.e. quote, comma, space quote)?

Your latest code modification removes all of the surrounding quotes
except for the beginning of the the last field value.

I appreciate your patience with the back and forth regarding this issue.

--
LF

Sub btnTest_Click (strFilePath as String, strTbl as String)
Dim strColumns as String, strRow as String
Dim varColumn as Variant, varRow as Variant
Dim lngDelimCount as Long, i as Integer, intF as Integer
Dim db as DAO.database, rs as DAO.recordset, strSQL as String

Const strDelim As String = ","

intF = FreeFile()

'__1) Open the text file.
Open strFilePath For Input As #intF
'__2) Get column names from the first text file paragraph.
Line Input #intF, strColumns
'__3) Parse column names into an array of field names.
varColumn = Split (strColumns, strDelim)
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varColumn(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varColumn(lngDelimCount)), 1) = Chr(34) Then _
varColumn(lngDelimCount) =
Mid(Trim(varColumn(lngDelimCount)), _
2, Len(Trim(varColumn(lngDelimCount))) - 2)
Next lngDelimCount
'__4) Open a recordset to the table that will receive the data.
Set db = CurrentDb
strSQL = "SELECT * FROM [" & srtTbl & "];"
Set rs = db.OpenRecordset (strSQL, dbOpenDynaset, dbAppendOnly)
'__5) Read each text file paragraph until the end of file is reached.
Do While EOF(intF) = False
Line Input #intF, strRow
'__6) Parse rows into an array of field values.
varRow = Split (strRow, strDelim)
'__7) Add a new record to the recordset.
rs.AddNew
'__8) Write row field values to the recordset.
For lngDelimCount = LBound(varColumn) To UBound(varColumn)
If Left(Trim(varRow(lngDelimCount)), 1) = Chr(34) And _
Right(Trim(varRow(lngDelimCount)), 1) = Chr(34)
Then _
varRow(lngDelimCount) =
Mid(Trim(varRow(lngDelimCount)),
_
2, Len(Trim(varRow(lngDelimCount))) - 2)
rs.Fields(varColumn(lngDelimCount)).Value =
varRow(lngDelimCount)
Next lngDelimCount
'__9) End the second loop.
'__10) Update the recordset.
rs.Update
'__11) End the first loop
Loop
'__12) Close the recordset.
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'__13) Close the text file.
Close #intF
End Sub

--

Ken Snell
<MS ACCESS MVP>

Forgot to add this to my previous reply. It appears to grab all of
the
memo content, but it tries to insert it into the preceeding field
which
is not a memo datatype; it also does not strip the quotes from the
value.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top