Data transfer from external

Dynamics 365 Business Central/NAV Developers Forum
1

I have a question about data transfer from an external MS ACCESS2 into a Navision file. In the ACCESS2 file is a field with the data type MEMO. The length of this field can be 64000 characters. The content of this field are text data’s whit different length’s. How can I implement this field into a file which include text fields with a fix field length ? Best regards Edwin

2

Hello Edwin, I did this a couple of times. As I like programming in Delphi, I did this by 1) Looping through the access table 2) reading the memo field into a memo component 3) looping through the memo component lines and 4) writing the lines into a new text lines table. Would you like some help? Pelle

3

quote:

Originally posted by pelle: Hello Edwin, I did this a couple of times. As I like programming in Delphi, I did this by 1) Looping through the access table 2) reading the memo field into a memo component 3) looping through the memo component lines and 4) writing the lines into a new text lines table. Would you like some help? Pelle

Hi Pelle, if you can help me this will be very good. My problem is, we don’t have Delphi on our System. Edwin

4

Edwin, email me the zipped access table if it isn’t too confidential. pl@seldenmast.se Pelle

5

Edwin, You can implement it without any programming in two following steps: 1. Export the memo field of your MS Access table into a text file. If you do not want to make an export procedure, just copy this field and paste into notepad. 2. Import existing text file into Navision table via dataport. Good luck. Regards, Yuri Pokusaev IBS, Senior Consultant NCPS, NCSD ypokusaev@yahoo.com +7(095)967-8080

6

Yuri, this sounds simple enough, but the text file created from the memo field needs some more information to handle the referential integrety: References to the “parent” table + line no (i.e. “Item No.”, “Line No.”.) Where do you get that info from? Pelle

7

Dear Pelle, My previous design was a rather simple. I absolutely agree with your requirements above. Therefore I would like to offer another decision. I believe, it will be useful for a person not familiar with Delphi.

**Prerequisites:** 
1. MS Access (VBA)
2. Dataport object of Navision Attain

**Example:**
**(1) Table Structure**
MemoTable
  Index LongInt
  mem Memo

**(2) VBA Code**
Public Function WriteNAImportFile()
  Dim mytable As Recordset
  Dim ifile As Integer
  Dim iStrPos As Integer
  Dim iStrLen As Integer
  Dim iCounter As Integer
  ifile = FreeFile()
  Open "D:\import.txt" For Output As #ifile
  Set mytable = DBEngine(0).Databases(0).OpenRecordset("MemoTable")
  mytable.MoveFirst
  While Not mytable.EOF
    If IsEmpty(mytable!mem) Or IsNull(mytable!mem) Then
      Write #ifile, mytable!Index & "", "0", ""
    Else
      iStrPos = 0
      iCounter = 0
      iStrLen = InStr(iStrPos + 1, mytable!mem, Chr(10)) - iStrPos - 1
      While iStrLen > 0
        Write #ifile, mytable!Index & "", iCounter & "", Mid$(mytable!mem, iStrPos + 1, iStrLen - 1)
        iCounter = iCounter + 1
        iStrPos = iStrPos + iStrLen + 1
        iStrLen = InStr(iStrPos + 1, mytable!mem, Chr(10)) - iStrPos - 1
      Wend
    End If
    mytable.MoveNext
  Wend
  Close #ifile
End Function

**(3) Output Access-to-NA import-file Structure:**
Comma-Separated
Field Delimited is [ “ ]
Fields: “< Key Field Value >”,”< Line No. >”,”< Memo Line (< Line No. >) >”
Example: 
"1","0","string 1"
"1","1","string 2"

Regards, Yuri Pokusaev IBS, Senior Consultant NCPS, NCSD ypokusaev@yahoo.com +7(095)967-8080