昨天介紹完提交清單的下載後，今天來介紹提交資料下載，其實直接從提交資料下載應該就可以，且資料較為完整，但依照需求，如果只是要個清單列表，那下載清單速度會快一些。

相關說明文件網址：
https://docs.getodk.org/central-api-submission-management/

提交資料下載

透過「Exporting Form Submissions to CSV」API，可以將提交資料與相關的多媒體檔案壓成ZIP檔案下載，然後我們透過程式將檔案解壓縮到特定資料夾，然後再轉入Access內使用。

以下是相關VBA程式：

Function ODK_Submissions_EXP2ZIP(int_projectId, int_formID, strXmlFormId, strLastDate, strZIPFile)
    
    Dim strUrl As String
    Dim strReqBody As String
    
    strMethod = "GET"
    'strQuery = "?%24filter=concat(year(__system/submissionDate),month(__system/submissionDate)) eq '" & strYYYYMM & ""
    'strQuery = "?%24filter=year(__system/submissionDate) eq " & left(strYYYYMM, 4) & " and month(__system/submissionDate) eq " & right(strYYYYMM, 2)
    strQuery = "?%24filter=__system/submissionDate gt " & Format(strLastDate, "00-00-00") & "T00:00:00.000Z" & " "
    
    strUrlOpt = "/v1/projects/" & int_projectId & "/forms/" & strXmlFormId & "/submissions.csv.zip" & strQuery
    
    'GET /v1/projects/{projectId}/forms/{xmlFormId}/submissions.csv.zip
    
    strReqBody = ""
    
    strReqHeader1 = "Authorization"
    strReqHeader2 = "Bearer " & ConfigLocal("ODK_Token")
    
    
    strUrl = ConfigCloud("F_ODK_Url") & strUrlOpt
        
    Set hReq = CreateObject("MSXML2.XMLHTTP")
        
        
        With hReq
            .Open strMethod, strUrl, False
            .setRequestHeader strReqHeader1, strReqHeader2
            .send strReqBody
            While hReq.ReadyState <> 4
                DoEvents
            Wend
            
            'strResponse = hReq.responseText
            'Debug.Print strResponse
            'If Len(strResponse) > 10 And InStr(strResponse, "Could not authenticate") = 0 Then
            'If strResponse <> "" Then
            If hReq.STATUS = 200 Then
   
                
                Set ostream = CreateObject("ADODB.Stream")
                ostream.Open
                ostream.TYPE = 1
                ostream.Write hReq.responseBody
                ostream.SaveToFile strZIPFile, 2 ' 1 = no overwrite, 2 = overwrite
                ostream.CLOSE
                  
        
                Debug.Print "Submissions_ListingAll : OK!"
                '下載後匯入到
                Call ODK_Submissions_ZIP2TMP(strZIPFile, strXmlFormId)
                Debug.Print strZIPFile
                Debug.Print strXmlFormId
                
                ODK_Submissions_EXP2ZIP = True
                
            Else
                ODK_Submissions_EXP2ZIP = False
                Debug.Print ":Submissions_ListingAll: Fail.." & hReq.responseText
                Exit Function
            End If
        End With
    
End Function

裡面有用到過濾的參數，可以使用的參數可以參考這裡：
https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part1-protocol.html#_Toc31358948

目前是過濾方式，是寫大於最後一次更新日期，就都抓下來，以確保抓的是還沒下載過的部分。

以下子程式，用來將下載下來的壓縮檔解壓縮，然後匯入到Access中：

Function ODK_Submissions_ZIP2TMP(strZIPFile, strXmlFormId)
    
    strODir = GetSpecialFolderNames("MyDocuments") & "\ODK\" & strXmlFormId
    Call MakeDir(CStr(strODir))
    
    If strZIPFile <> "" Then Call SevenZ(strZIPFile, "", "x", "-aoa -o" & strODir)
    DoCmd.SetWarnings False
    DoCmd.TransferText acImportDelim, "ODK_" & strXmlFormId & " 匯入規格S", "ODK_TMP_" & strXmlFormId, strODir & "\" & strXmlFormId & ".csv", True
    DoCmd.TransferText acImportDelim, "ODK_" & strXmlFormId & "-Repeat_Report 匯入規格S", "ODK_TMP_" & strXmlFormId & "_Repeat_Report", strODir & "\" & strXmlFormId & "-Repeat_Report.csv", True
    DoCmd.SetWarnings True
    
End Function

DoCmd.TransferText中的匯入文字規格的規格名稱，請依照自己定義的名稱修改。

抓下來的ZIP檔案，透過以下子程式將它解壓縮，並使用Access內建的「DoCmd.TransferText」指令，把文字檔案轉入Access內使用。這部份必須預先製作好「匯入規格」的資訊，這部份由手動匯入文字檔案時，開啟「匯入文字精靈」視窗後，將相關欄位都設定完畢，再開啟進階按鈕：

開啟匯入規格視窗後，設定字碼頁為「Unicode (UTF-8)」，確定好哪些欄位要匯入，以及資料類型、日期時間格式等設定，都完成後，再用另存新檔按鈕將資料儲存：

如果有要開啟/刪除已存在的規格，可以用規格按鈕來開啟或者刪除規格資訊：

解壓縮程式，之前我有撰寫過WinRAR用的VBA程式，但由於版權問題7z已變成熱門替代品，已將壓縮預設程式改成免費的7z使用。依然使用WinRAR來解壓縮的，可以參考以下連結：

Access VBA 的眉眉角角Day15: 使用WinRAR進行壓縮
https://ithelp.ithome.com.tw/articles/10185868

如果使用7z的，可以使用以下程式來套用壓縮/解壓縮指令：

Function SevenZ(str_archive_name, str_file_name, str_Commands, str_Switches, Optional bnSilent As Boolean = True)

If str_archive_name = "" Then Exit Function

'7-Zip 22.01 (x64) : Copyright (c) 1999-2022 Igor Pavlov : 2022-07-15
'
'Usage: 7z <command> [<switches>...] <archive_name> [<file_names>...] [@listfile]
'
'<Commands>
'  a : Add files to archive
'b:    Benchmark
'  d : Delete files from archive
'  e : Extract files from archive (without using directory names)
'  h : Calculate hash values for files
'  i : Show information about supported formats
'  l : List contents of archive
'  rn : Rename files in archive
'  t : Test integrity of archive
'  u : Update files to archive
'  x : eXtract files with full paths
'
'<Switches>
'  -- : Stop switches and @listfile parsing
'  -ai[r[-|0]]{@listfile|!wildcard} : Include archives
'  -ax[r[-|0]]{@listfile|!wildcard} : eXclude archives
'  -ao{a|s|t|u} : set Overwrite mode
'  -an : disable archive_name field
'  -bb[0-3] : set output log level
'  -bd : disable progress indicator
'  -bs{o|e|p}{0|1|2} : set output stream for output/error/progress line
'  -bt : show execution time statistics
'  -i[r[-|0]]{@listfile|!wildcard} : Include filenames
'  -m{Parameters} : set compression Method
'    -mmt[N] : set number of CPU threads
'    -mx[N] : set compression level: -mx1 (fastest) ... -mx9 (ultra)
'  -o{Directory} : set Output directory
'  -p{Password} : set Password
'  -r[-|0] : Recurse subdirectories for name search
'  -sa{a|e|s} : set Archive name mode
'  -scc{UTF-8|WIN|DOS} : set charset for for console input/output
'  -scs{UTF-8|UTF-16LE|UTF-16BE|WIN|DOS|{id}} : set charset for list files
'  -scrc[CRC32|CRC64|SHA1|SHA256|*] : set hash function for x, e, h commands
'  -sdel : delete files after compression
'  -seml[.] : send archive by email
'  -sfx[{name}] : Create SFX archive
'  -si[{name}] : read data from stdin
'  -slp : set Large Pages mode
'  -slt : show technical information for l (List) command
'  -snh : store hard links as links
'  -snl : store symbolic links as links
'  -sni : store NT security information
'  -sns[-] : store NTFS alternate streams
'  -so : write data to stdout
'  -spd : disable wildcard matching for file names
'  -spe : eliminate duplication of root folder for extract command
'  -spf : use fully qualified file paths
'  -ssc[-] : set sensitive case mode
'  -sse : stop archive creating, if it can't open some input file
'  -ssp : do not change Last Access Time of source files while archiving
'  -ssw : compress shared files
'  -stl : set archive timestamp from the most recently modified file
'  -stm{HexMask} : set CPU thread affinity mask (hexadecimal number)
'  -stx{Type} : exclude archive type
'  -t{Type} : Set type of archive
'  -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName] : Update options
'  -v{Size}[b|k|m|g] : Create volumes
'  -w[{path}] : assign Work directory. Empty path means a temporary directory
'  -x[r[-|0]]{@listfile|!wildcard} : eXclude filenames
'  -y : assume Yes on all queries

    str7z = ConfigLocal("7zPath")

    If str7z = "" Then
        str7z1 = Environ("ProgramFiles(x86)") & "\7-Zip\7z.exe"
        str7z2 = Environ("ProgramFiles") & "\7-Zip\7z.exe"
        str7z3 = Environ("ProgramW6432") & "\7-Zip\7z.exe"
        
    
        '檢查壓縮程式
        If Len(Dir(str7z1)) > 0 Then str7z = str7z1
        If Len(Dir(str7z2)) > 0 Then str7z = str7z2
        If Len(Dir(str7z3)) > 0 Then str7z = str7z3
        
        
        If Len(str7z) = 0 Then
            MsgBox "請安裝7-Zip解壓縮軟體"
            Exit Function
        End If
        Call ConfigLocalSave("7zPath", str7z)
    End If
    'Function SevenZ(str_archive_name, str_file_name, str_Commands, str_Switches)
    strCMD = Chr(34) & str7z & Chr(34) & " " & str_Commands & " " & str_Switches & " " & Chr(34) & str_archive_name & Chr(34) & " " & str_file_name '& " || pause"
    Debug.Print strCMD
    If bnSilent = True Then
        iWS = 0
    Else
        iWS = 1
    End If
    
    result = RunCMD(CStr(strCMD), bnSilent, True, CInt(iWS), False)
    
    Debug.Print result
    
End Function

更多API?

ODK Central 提供了許多的API，如果全部都導入使用，的確可以用Access VBA完整控制ODK Central後端的運作，但是否要做到這種程度，我個人是不認為有這需要，我只需要把資料倒出到自己的資料庫來彙整數據，這樣就足夠，如果有更多的需求，就在另外寫程式導入使用。