?
Solved

Why doesn't "DoCmd.TransferText" method on an Access object import ALL the characters in a CSV file field into an Access Database memo field?

Posted on 2008-10-11
19
Medium Priority
?
1,690 Views
Last Modified: 2013-12-25
I am writing a calendar application for my Church as follows.
     1 Outlook exports its data to a CSV file in "Windows" format.
     2. I use VB6 code to transfer the data from the CSV file into an Access database using the
         subroutine I have written named: ImportCSVFile (see below).
     3. My program then filters the Access data to process only records between
         certain dates, writing each record out to a HTML file for eventual display in a
         browser.

         (the current output from the program can be viewed on the web by going to:
          www.StThomasTheApostle.org.au/calendar.html)

My problem is that the method ".DoCmd.TransferText" within my procedure "ImportCSVFile" appears to truncate one of the fields before placing it in the Table. The table destination field is a MEMO field (since I sometimes may have more than 256 characters).

I viewed the CSV file and confirmed that the field data being imported contains  471 characters, but the method is transferring only the first 261 characters into the MEMO field.

Can anyone explain why this is happening and whether there is a way to successfully transfer all the characters - perhaps my code is faulty????

As a matter of interest the field being imported from the CSV file is NOT the last field in each of the CR/LF delimited lines of that file.

I use Access 2003 interactively on my system, but the VB Code seems to create an earlier version of the Access database when it is created (using code such as follows (the memo field in question is the last one):

                  Set CalTd = myDB.CreateTableDef("Calendar")
                  Set CalFlds(0) = CalTd.CreateField("Subject", dbText)
                  Set CalFlds(1) = CalTd.CreateField("Start Date", dbDate)
                  Set CalFlds(2) = CalTd.CreateField("Start Time", dbDate)
                  Set CalFlds(3) = CalTd.CreateField("End Date", dbDate)
                  Set CalFlds(4) = CalTd.CreateField("End Time", dbDate)
                  Set CalFlds(5) = CalTd.CreateField("All Day Event", dbBoolean)
                  Set CalFlds(6) = CalTd.CreateField("Reminder On/Off", dbBoolean)
                  Set CalFlds(7) = CalTd.CreateField("Reminder Date", dbDate)
                  Set CalFlds(8) = CalTd.CreateField("Reminder Time", dbDate)
                  Set CalFlds(9) = CalTd.CreateField("Meeting Organizer", dbText)
                  Set CalFlds(10) = CalTd.CreateField("Required Attendees", dbText)
                  Set CalFlds(11) = CalTd.CreateField("Optional Attendees", dbText)
                  Set CalFlds(12) = CalTd.CreateField("Meeting Resources", dbText)
                  Set CalFlds(13) = CalTd.CreateField("Billing Information", dbText)
                  Set CalFlds(14) = CalTd.CreateField("Categories", dbText)
                  Set CalFlds(15) = CalTd.CreateField("Description", dbMemo)
Option Explicit
 
Public Sub ImportCSVFile(cFileName as string, cDatabaseFileName As String,        
                                        cTableName As String)
 
'This subroutine imports data from a comma delimited file (Windows Format)
'into an Access database.
'    The three required parameters are:
'        1. the name of the CSV file to be imported (cFileName)
'                        (e.g. "C:\MyData\Calendar.CSV")
'        2. the name of the database (cDatabaseFileName)
'                        (e.g. "C:\MyDatabases\MyDataBase.mdb")
'         3. the name of the Table within that database into which the
'             delimited file is to be imported (e.g. "Calendar")
 
Dim MyWs As Workspace             'Pointer to workspace area in which Access 
                                                    'database will open
 
Dim accApp As Access.Application  'Pointer to Access database
 
Dim fs As Object                             'File pointer for Import File (cFileName)
 
'Set up a workspace in which to open the Access Database
Set MyWs = DBEngine.Workspaces(0)
 
'Set up a pointer to the Access database
Set accApp = CreateObject("Access.application")
 
'If Access 10 (97) or later then we can turn off
'the security alert that annoyingly pops up
If accApp.Version >= 10 Then
   accApp.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If
 
'Open the Access database
accApp.OpenCurrentDatabase cDatabaseFileName
 
'Set up a file pointer to the text file
Set fs = CreateObject("Scripting.FileSystemObject")
'If the file exists then import the data
If fs.FileExists(cFileName) Then
  accApp.DoCmd.TransferText acImportDelim, "", cTableName, cFile, True, ""
End If
 
'Release the pointers
Set fs = Nothing
Set accApp = Nothing
Set MyWs = Nothing
 
End Sub

Open in new window

0
Comment
Question by:Chris Raisin
[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
  • 10
  • 5
  • 4
19 Comments
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 186 total points
ID: 22697978
Try exporting to Excel; I remember something about a bug doing this type of transfer. And Excel will only capture something like 1000 to 1300 characters.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 189 total points
ID: 22699134
craisin,

Why not simply Link the Calendar data directly into Access, thus eliminating the whole: Outlook-->CSV-->Access, process?

To Link Outlook to Access simply click:

File-->Get External Data-->Link tables
Select: "Outlook" from the "Files of Type" dropdown
Expand the object called "Personal Folders"
Select: "Calendar"

JeffCoachman
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22699636
Jeff,
 
Is there a way I can do that with code? Perhaps using the "Access" engine?
If so, and it work, then that is the quick solution.
Ideally of course I should write the program with the assumption that the user may not have Microsoft Access nor Microsoft Excel on board. If I write code creating a "Class" object and then simply read each line of the CSV file in, assigning the delimited values to the appropriate class properties, I should successfully be able to deal with the MEMO problem.
I will go down that path anyway (being a purist) but if you know of a way that you can get an Access object to perform the "Get External Data" method that would be handy to know about.
I have decided to award you BOTH half points since I am sure the "Excel" method would work too. Again that is not a total solution since Excel has that limiting bug on the size of the import.
Thanks to you both!
I will come back and add a sample of my solution code using a class object as soon as I can.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 13

Author Closing Comment

by:Chris Raisin
ID: 31505391
Thanks for your help in trying to overcome this obvious bug in Microsoft Access.
Of course the best solution is for Microsoft to do the fix, but I might reach my 100th birthday before then :-) .... The best solution is hard coding via a class object (a lot more work) but greater control and no dependence on external "buggy" programs!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22701210
Once you link the table, no further user interatction in this regard is required.
The most up to date Calendar info will always be available.
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22702077
Thanks for that Jeff.
 
I will investigate whether I can link via VB Code. If I can I will give it a try. Any success I will post to this discussion.
Cheers
Chris
 
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22821668
I have solved the problem using the following method:
      1. Export the data from Outlook into a file in XML format
      2. Create a database with code to define the large fields being imported as MEMO
      3. Import the produced XML into the Access database.
 
When using "DoCmd.TransferText", behind the scenes the data is imported into an access database table which has ONLY "String" fields and so is subject to the size restriction for strings.
Using the Access object method "ImportXML" we can import into an already created Access table in which you have defined each field as either "string" or "memo". The only stipulation is that the field names in the table must match those in the XML file and be in the same order. They must also be either string or MEMO (do not define Integer or any other type). All imported XML is string in nature, but if the field accepting the data is MEMO in the access table, the entire contents of the large string field being imported is added to the table. NO DATA IS TRUNCATED!
I have attached the code I wrote to perform these functions. Simply change the name of the access table where required and the code should work for any situation after tweaking to suit the programmers requirements.
Cheers everyone!  :-)

Option Explicit
 
Public Function ExportCal2XML(cXMLFile As String, Optional datStart As Date, Optional datEnd As Date) As Integer
Const NODE_PROCESSING_INSTRUCTION = 7
Const NODE_ELEMENT = 1
Dim cTemp As String
 
Dim objDOM As Object, _
    objCalendar As Object, _
    objCal As Object, _
    objP As Object, _
    objData As Object, _
    olkItems As outlook.Items, _
    olkAppt As outlook.AppointmentItem, _
    intCount As Integer, _
    cStartDate As String, _
    cStartTime As String, _
    cEndDate As String, _
    cEndTime As String
 
intCount = Day(Date) - 1
If IsMissing(datStart) Then
  datStart = DateAdd("d", -intCount, Date)
  datEnd = DateAdd("m", 3, datStart)
End If
 
' Create the main xml node '
Set objDOM = CreateObject("MSXML2.DOMDocument")
Set objCalendar = objDOM.createNode(NODE_PROCESSING_INSTRUCTION, "xml", "")
objDOM.appendChild objCalendar
   
' Create the Parent Node - "calendar" '
Set objCalendar = objDOM.createNode(NODE_ELEMENT, "Calendar", "")
   
' Create a child node - "cal" '
Set objCal = objDOM.createNode(NODE_ELEMENT, "cal", "")
   
' Get the Outlook calendar items '
Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items.Restrict("[Start] > '" & Format(datStart & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datEnd & " 23:59pm", "ddddd h:nn AMPM") & "'")
olkItems.Sort "[Start]"
For Each olkAppt In olkItems
    DoEvents
    Set objP = objDOM.createNode(NODE_ELEMENT, "Calendar", "")
    objCal.appendChild objP
    
    ' Add Subject '
    Set objData = objDOM.createNode(NODE_ELEMENT, "Subject", "")
    If Len(olkAppt.SUBJECT) = 0 Then olkAppt.SUBJECT = Space$(10)
    objData.Text = olkAppt.SUBJECT
    objP.appendChild objData
    
    ' Add Start Date '
    Set objData = objDOM.createNode(NODE_ELEMENT, "StartDate", "")
    objData.Text = Left(olkAppt.Start, 10)
    objP.appendChild objData
    
    ' Add Start Time '
    Set objData = objDOM.createNode(NODE_ELEMENT, "StartTime", "")
    cTemp = Mid$(olkAppt.Start, 11)
    If Len(cTemp) = 0 Then cTemp = Space$(10)
    objData.Text = cTemp
    objP.appendChild objData
    
    ' Add End Date '
    Set objData = objDOM.createNode(NODE_ELEMENT, "EndDate", "")
    objData.Text = Left(olkAppt.End, 10)
    objP.appendChild objData
    
    ' Add End Time '
    Set objData = objDOM.createNode(NODE_ELEMENT, "EndTime", "")
    cTemp = Mid$(olkAppt.End, 11)
    If Len(cTemp) = 0 Then cTemp = Space$(10)
    objData.Text = cTemp
    objP.appendChild objData
    
    ' Add All Day Event '
    Set objData = objDOM.createNode(NODE_ELEMENT, "AllDayEvent", "")
    objData.Text = olkAppt.AllDayEvent
    objP.appendChild objData
    
    ' Add Description '
    Set objData = objDOM.createNode(NODE_ELEMENT, "Description", "")
    If Len(olkAppt.Body) = 0 Then olkAppt.Body = Space$(10)
    objData.Text = olkAppt.Body
    objP.appendChild objData
    
    ' Add Categories '
    Set objData = objDOM.createNode(NODE_ELEMENT, "Categories", "")
    cTemp = olkAppt.CATEGORIES
    If Len(cTemp) = 0 Then cTemp = Space$(10)
    objData.Text = cTemp
    objP.appendChild objData
    
    'Add Location
    Set objData = objDOM.createNode(NODE_ELEMENT, "Location", "")
        If Len(olkAppt.LOCATION) = 0 Then olkAppt.LOCATION = Space$(10)
    objData.Text = olkAppt.LOCATION
    objP.appendChild objData
    ' Add the data to the Cal node '
    
    objCal.appendChild objP
    Set objP = Nothing
    intCount = intCount + 1
Next
   
 ' Append "Cal" to "Calendar" '
 objCalendar.appendChild objCal
 Set objCal = Nothing
   
 ' Append "Calendar" to the XML Dom Document '
 objDOM.appendChild objCalendar
 Set objCalendar = Nothing
   
 ' Change the name and path of the output file.'
 objDOM.Save cXMLFile
  
 ' Cleanup '
 Set objDOM = Nothing
 Set objCalendar = Nothing
 Set objCal = Nothing
 Set objP = Nothing
 Set objData = Nothing
 Set olkItems = Nothing
 Set olkAppt = Nothing
 
 ExportCal2XML = intCount
End Function
 
'=======================================================================
 
 
Option Explicit
 
Public Function CreateDatabase() As Workspace
'Note: the creation of the indexes at the end of the
'function are not really required since the data being imported
'will already be in date and time order (the way Outlook exports its data)
 
Dim myDB As Database, MyWs As Workspace
Dim CalTd As TableDef
Dim CalFlds(21) As Field
Dim CalIdx As Index
Dim CalQuery As QueryDef
Dim SQLstr As String
Dim fs as object
Dim x As Integer
 
'Set up a file pointer to the database file
Set fs = CreateObject("Scripting.FileSystemObject")
'If the file exists then erase the file
If fs.FileExists("C:\StThomasTheApostle.mdb") Then
  Kill "C:\StThomasTheApostle.mdb"
End If
 
Set MyWs = DBEngine.Workspaces(0)
 
Set myDB = MyWs.CreateDatabase("C:\StThomasTheApostle.mdb", dbLangGeneral, dbVersion30)
 
Set CalTd = myDB.CreateTableDef("Calendar")
 
Set CalFlds(0) = CalTd.CreateField("Subject", dbText)
Set CalFlds(1) = CalTd.CreateField("StartDate", dbText)
Set CalFlds(2) = CalTd.CreateField("StartTime", dbText)
Set CalFlds(3) = CalTd.CreateField("EndDate", dbText)
Set CalFlds(4) = CalTd.CreateField("EndTime", dbText)
Set CalFlds(5) = CalTd.CreateField("AllDayEvent", dbInteger)
Set CalFlds(6) = CalTd.CreateField("Categories", dbText)
Set CalFlds(7) = CalTd.CreateField("Description", dbMemo)
Set CalFlds(8) = CalTd.CreateField("Location", dbMemo)
For x = 0 To 8
  CalTd.Fields.Append CalFlds(x)
Next
myDB.TableDefs.Append CalTd
 
SQLstr = "Select * from Calendar where [StartDate] >= " + CStr(frmMain.CalendarFrom.Value) + _
         " and [StartDate] <= " + CStr(frmMain.CalendarTo.Value) + " order by [StartDate],[StartTime],[Categories]"
 
CreateIndexes
End Function
 
Public Function CreateIndexes()
Dim myDB As Database, MyWs As Workspace
Dim CalTd As TableDef
Dim CalIdx As Index
Dim x As Integer
 
Set MyWs = DBEngine.Workspaces(0)
 
Set myDB = MyWs.OpenDatabase("C:\StThomasTheApostle.mdb")
Set CalTd = myDB.TableDefs(0)
 
With CalTd
  ' Create new index, create and append Field
  ' objects to its Fields collection.
  Set CalIdx = CalTd.CreateIndex("Calendar")
 
  With CalIdx
    .Fields.Append .CreateField("StartDate")
    .Fields.Append .CreateField("StartTime")
    .Fields.Append .CreateField("Categories")
  End With
 
  ' Add new Index object to the Indexes collection
  .Indexes.Append CalIdx
  .Indexes.Refresh
End With
End Function
 
'=======================================================================
 
 
Option Explicit
 
Public Function ImportXMLFile(cXMLFile As String, cDatabaseFileName As String, cTableName As String) As Integer
 
'This subroutine imports data from a comma delimited file (Windows Format)
'into an Access database.
'    The three required parameters are:
'        1. the name of the XML file to be imported (cFileName)
'                        (e.g. "C:\MyData\Calendar.XML")
'        2. the name of the database (cDatabaseFileName)
'                        (e.g. "C:\MyDatabases\MyDataBase.mdb")
'         3. the name of the Table within that database into which the
'             delimited file is to be imported (e.g. "Calendar")
 
Dim MyWs As Workspace             'Pointer to workspace area in which Access
                                                    'database will open
 
Dim accApp As Access.Application  'Pointer to Access database
 
Dim fs As Object                             'File pointer for Import File (cFileName)
 
'Set up a workspace in which to open the Access Database
Set MyWs = DBEngine.Workspaces(0)
 
'Set up a pointer to the Access database
Set accApp = CreateObject("Access.application")
 
'If Access 10 (97) or later then we can turn off
'the security alert that annoyingly pops up
If accApp.Version >= 10 Then
   accApp.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If
 
'Open the Access database
accApp.OpenCurrentDatabase cDatabaseFileName
 
'Set up a file pointer to the text file
Set fs = CreateObject("Scripting.FileSystemObject")
'If the file exists then import the data
If fs.FileExists(cXMLFile) Then
  accApp.ImportXML cXMLFile, acAppendData
End If
 
accApp.CloseCurrentDatabase
 
'Release the pointers
Set fs = Nothing
Set accApp = Nothing
Set MyWs = Nothing
 
End Function

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22824690
If you solve your own issue and provide the procedure (both of which you have done), you are allowed to accept your own solution.

I have no issue with this.
You can click the "Request Attention" button to have this changed, if you like.

Congratulations!
;-)

JeffCoachman
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22828591
Jim,
Would you be agreeable for me to take the action suggested by Jeff above?
Thanks, by the way, Jeff, very kind of you. If Jim is also agreeable I will click the "Request Attention"  button as suggested.
Cheers
Chris
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22828620
Hi Chris, I have absolutely no problem with that; maybe I need to award you some points because you shared with me a solution.

Jim
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22828727
Thanks Guys,
I have clicked the "Needs Attention" button hoping that the "powers that be" will allow me to award the solution to myself (I often talk tyo myself, but awarding myself the solution seems strange!....LOL)
When I finally retire I might try to become a "guru" like you all seem to be. I think it is a GREAT way for a programmer/analyst to spend his time helping others and I still think that my annual payment to EE is the best money I spend each year.
I await some response from someone sometime...maybe.
 
Cheers
Chris
 
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22828744
Chris, Points are fun but I find that I get as much or more than I give. I'm a beginner here but Jeff is the real thing. I've been a member since 2001 but just really started participating a couple of months ago.

The current financial debacle has taken care of my retirement, I'll be working til I'm 420...

Regards,

Jim

0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22828773
Adjudicator....
There a a couple of very minor  "changes" I would like to make to the code (just to clarify) but I don't seem to be able to do that....It seems I would have to post the entire code again as an attachment it, I think.
Is that correct, or is there a way to amend the code after you have posted it?
Cheers
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22828905
jmoss111 ,

"Points are fun but I find that I get as much or more than I give."
Same here.
;-)

"I'm a beginner here but Jeff is the real thing."
There have been scores of Experts who came online after me, and promptly surpassed me in points in a matter of months (DatabaseMX comes to mind right away)

I am sure you can do the same, if you wanted to.
;-)

Jeff
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22830891
OK...I am now closing this question (I think)
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22830959
I cant see how to add a comment so that I can accept my comment as a solution, as per instructions.
It just appears that the question is only open to discussion....
 
Please help
 
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22833062
Yeah but it show us as point recipients. You'll need ZA
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22833574
crasin

Click the Request Attention button in your original post
0
 
LVL 13

Author Comment

by:Chris Raisin
ID: 22836179
There seems to be a time limitation on the "RA" button...it says I have to wait a while.
 
I will come back in a week.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

801 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