TransferText - delay/check to ensure fully imported (1 Viewer)

[bignose2]

Hi,

I have a 6 iCal files as a TEXT files with simply 1 field in each (see bottom)

I import using transfer text as below.
6 different text files appended to one table, hence the looping

On exactly the SAME text files, sometimes it works fine but mostly a few missed lines.
Each Text file is about 200 lines long. short text.

If I add a msgbox as below, between each import file it works fine so I guess enough of a delay to allow it to work. tried a variety of DoEvents dotted around but helps a little but not reliable, I think I need a loop.

However the ODD thing is the missing lines are usually random & in the middle of the text file so I can't open the table & delay loop to check to see the last line is e.g. "END:VCALENDAR" as it could well be but missing a few lines earlier in the file??

I will add a time delay but not really proper & would prefer to understand what is going on. The text files do not have the same number of lines or anything else I can easily check to be sure.

thanks i/a



Set SH = CurrentDb.OpenRecordset("StaffCalHeader")

If Not (SH.EOF And SH.BOF) Then
SH.MoveFirst

Do Until SH.EOF = True
strFilename = SH!DestinationFile
DoCmd.TransferText acImportFixed, "ImportGoogleiCalSpec", "GoogleCalendar", strFilename, False

DoEvents

MsgBox "Wait - delay"

SH.MoveNext

Loop
End If

MsgBox "Import complete"



text file

BEGIN:VCALENDAR
PRODID:-//Google Inc//Google Calendar 70.9054//EN
VERSION:2.0
CALSCALE:GREGORIAN
METHODUBLISH
X-WR-CALNAME:Greg
X-WR-TIMEZONE:Europe/London
BEGIN:VEVENT
DTSTART;VALUE=DATE:20171026
DTEND;VALUE=DATE:20171029
DTSTAMP:20181105T173656Z

 

[isladogs]

I don't use Google Calendar so can't check this for you

Each Text file is about 200 lines long. short text.

Umm. Did you mean 200 characters?
If not try using Long text/memo field instead

Also
a) check you have no formatting on the destination field
b) try the changes in RED

Set SH = CurrentDb.OpenRecordset("StaffCalHeader[COLOR="red"]",  dbOpenDynaset[/COLOR])

If Not (SH.EOF And SH.BOF) Then
[INDENT][COLOR="Red"]SH.MoveLast[/COLOR]
SH.MoveFirst

Do Until SH.EOF = True
strFilename = SH!DestinationFile
[COLOR="Red"]
DoEvents 
Debug.Print strFileName[/COLOR]
DoCmd.TransferText acImportFixed, "ImportGoogleiCalSpec", "GoogleCalendar", strFilename, False

[COLOR="Red"]'DoEvents 'not sure this serves any purpose here[/COLOR]

MsgBox "Wait - delay"

SH.MoveNext

Loop[/INDENT]
End If

[COLOR="red"]Set SH=Nothing[/COLOR] 'clear the recordset

MsgBox "Import complete"

 

[bignose2]

Hi,

Thanks for the reply,

Sorry not clear, 200 lines, but each line is only 80 or so characters, hence short text. (I only mentioned as long text, the old memo fields might have slowed up the import)

The StafCalHeader is not the problem, it loads all these fine as the source files name & destination

It is the resulting GoogleCalender table that has missing lines and is not importing properly unless I use the msgbox to delay.

The fact that it imports perfectly with this msgbox delay, I assume means that all the coding is sort of OK but I just need a away to check/slow before it goes onto importing the next text file automatically.
I don't want to have to click OK between each.

 

[isladogs]

Sorry not clear, 200 lines, but each line is only 80 or so characters, hence short text

Hmm. 200 x 80 = 16000 which is obviously far bigger than 255

Try my code first with & then without the MsgBox
If it still doesn't work without the message box you can build in a delay using the Sleep API

Add this in the declarations section of a standard module

'###############################################
#If VBA7 Then 'Add PtrSafe - required for 64-bit Office 
    Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else '32-bit Office
    Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If
'###############################################

You need to build in a delay to allow processing to finish before looping to the next time.

So add this line after the TransferText line (& omit the message box)
This builds in a delay of 500 milliseconds (0.5s)

Sleep 500

If necessary, experiment with the value till you get the delay needed for it to work reliably

HTH