批次匯入大量Excel資料至同一張資料表

冰殤 · 发表于 2016-10-17 17:11

jack5d

表格.rar (84.51 KB, 下载次数: 15)
此檔為[jack5d]大大製作
想請問如何做修改成
原本:只能選一個檔案希望:可選擇大量EXCEL
原本:每個檔案各一張資料表希望:可彙整成同一張

jack5d · 发表于 2016-11-9 11:26

多選反而簡單多了

表格.rar (92.54 KB, 下载次数: 13)

冰殤 · 发表于 2016-11-9 12:37

jack5d 发表于 2016-11-9 11:26
多選反而簡單多了

感謝J大,
想請問該如何讓他彙整至同一張呢?
試著把這段的TbaName 改成我要的資料表
fExistTable (TbaName) '("彙整")
但變成第一個檔案的資料就消失了

jack5d · 发表于 2016-11-9 14:50

匯總到一個表內就更簡單了,
但是要注意, 你所選取的excel文件內的欄位及其數據形態必須與access目標取放資料表的欄目數量及其數據形態一致, 否則就是匯入不了

表格.rar (94.56 KB, 下载次数: 21)

jack5d · 发表于 2016-11-9 15:01

冰殤发表于 2016-11-9 12:37
感謝J大,
想請問該如何讓他彙整至同一張呢?
試著把這段的TbaName 改成我要的資料表

Option Compare Database
Private Sub t001_Click()
'Dim TbaName As String, TbbName As String '表名稱變數
Dim FN As String
Dim URL() As Variant
Dim i As Long
On Error GoTo ER
URL = dbFunction.Inport_From_Excel
i = 1
DoCmd.SetWarnings False
Do Until i = UBound(URL) + 1
FN = Dir(URL(i))
'TbaName = Left(FN, InStrRev(FN, ".", -1, 2) - 1) '抓取表名
'fExistTable (TbaName) 'fExistTable
'是在dbFunction中的一段FUNCTION,
'其作用是檢查當前Access中有沒有存在和匯入EXCEL檔案重複的命名,
'如有就會把Access中已存在的表刪掉,
'判斷沒有的話就會運行以下一句, 以TbaName來為新匯入的資料表命名,
'但由於你要求把所有資料匯到其一表內,哪就沒必要怕有[重複命名]的問題了,所以以上代碼可刪掉
'如果你想改表名, 只要把下面一句中的"Main"改為你想要匯入到的資料表名稱就可以了
'匯入資料的欄目數及其數據形態必須和目標存放表的欄位數及數據形態一致
'匯入資料的欄目數及其數據形態必須和目標存放表的欄位數及數據形態一致
'匯入資料的欄目數及其數據形態必須和目標存放表的欄位數及數據形態一致
'重要的事情要說三次!
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Main", URL(i), True, "A:B"
i = i + 1
Loop
DoCmd.SetWarnings True
DoCmd.Close acForm, Me.Name, acSaveNo
Exit Sub
ER:
MsgBox VBA.Err.Description
End Sub

复制代码

冰殤 · 发表于 2016-11-9 23:08

jack5d 发表于 2016-11-9 15:01

感謝大大詳細的解說,正在嘗試調整原始數據型態
匯入後將ACCESSE的表格皆調整為簡短文字,但不知為何還是某幾列無法匯入

冰殤 · 发表于 2016-11-18 13:28

jack5d 发表于 2016-11-9 15:01

J大,不好意思
在數據型態上有問題無法解決......
例:A欄為文字與數字都有出現該如何在讀取資料時強制他為文字型態呢 ?
因資料為公式帶出直接在EXCEL設定為文字格式會有問題

目前做法為在第2列新增一列都設為文字格式,作為判斷數據型態使用

派單_不可行.rar (143.11 KB, 下载次数: 6)

jack5d · 发表于 2016-11-18 17:52

冰殤发表于 2016-11-18 13:28
J大,不好意思
在數據型態上有問題無法解決......
例:A欄為文字與數字都有出現該如何在讀取資料時強 ...

研究了半天才發現你檔案中有把某些表Hidden
其實不是數據型態問題, 原因是......好科普一下(如不想看就跳過直接看最底):

1)EXCEL表(SHEET)
- EXCEL表實際上是有自己的一個順序號, 你有寫VBA就明白了
2)ACCESS Docmd.Transferspreadsheet
- 此方法有一個叫Range的參數, 是指對像(spreadsheet,就是EXECL內的表)的[地址]
   例如: "A:B" 就是指讀取 A1:Bx 的范圍, 其中 x 是指有數據的最後一行

- 好, 哪怎麼向參考傳入指定的EXCEL表呢? 如上述, 當你Range 參數直接輸入 "A:B" 時
   VB會默認以EXCEL檔案中第1順序的表格為目標, 因此由於你穩藏了某一表格, 而它的
   順位又剛好是第一位, 所以ACCESS老跳出問題說 :[無法找到目標: 編號1 欄位]

原因如上, 以下是解決方法:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Main", URL(i), True, "A:B"

复制代码

以上一句代碼請改為:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Main", URL(i), True, "[你要導入的excel表格名稱]!A:B"
'例如:
'"Sheet1!A:B"

复制代码

題外話: 你的表格不合乎資料庫正規化原則, 日後開發深了必定出大問題的, 你還是及早細化, 正規起來

冰殤 · 发表于 2016-11-20 15:31

本帖最后由冰殤于 2016-11-20 15:32 编辑

jack5d 发表于 2016-11-18 17:52
研究了半天才發現你檔案中有把某些表Hidden
其實不是數據型態問題, 原因是......好科普一下(如不想看就 ...

J大,不好意思~
忘了跟你說我有改過程式碼
就像你說的指定EXCEL表格

目前主要卡在某欄會有數字與文字並存,
我先將accesse的main表裡面修改為文字模式
但還是無法完整匯入,因第一列為數字所以好像整欄被視為數字

ACCESS多選彙整.rar (311.47 KB, 下载次数: 9)

我有另外做一個EXCEL 以SQL來抓取,是可抓取但有些檔名的中文會無法辨識,導致無法讀取資料
PS:關於正規化...正在嘗試
初衷只是要把資料彙整統計,並進行多次加工來分類
但越做越深真的不知該如何來正規化,目前只想到先加個唯一索引

jack5d · 发表于 2016-11-21 11:50

看了, 問題是出在 : 備註_2 第263, 264行及備註_3 第238行
你看看你哪表, 這些行內的數據是文字格式, 有些是文字形態的 [0] 有些是一點[.]
因此匯入轉換時會報錯, 因為ACCESS中, 對應欄位是整數形態, 而導入過程是不會自動轉換形態的

有2方法, 一就是由根源先處理好數據再匯內, 二就是ACCESS數據表內把以上欄位的數據形態改為[文字]

至於數據正規化簡單來就就是把資料分精分細分類,
以你表內資料為例:
欄位重覆問題:

如餐別有3欄( 餐別_1,餐別_2,餐別_3)
你要去想它們有沒必要都放在同一表內?能否分離?以一個ID號來聯係?
如集合到一個餐別表內, 再為它們加上獨立序號作為區別1,2,3例如:
你可加一欄位叫餐別序號數據形態為[整數] 以1,2,3 來區別開來

表內資料應簡單化, 才能提升查詢最大效率化
例如:特殊_1 這個槽位, 你由第一筆都最後一筆都有一大串文字, 你應該
去想, 把此單改為數值形態, 再另開一表叫特殊說明表
特殊說明表內只有 [ID] 和 [說明] 2個欄位
[ID]是用來給主表參考填入數值
[說明] 存文字, 這樣主表只記錄了 ID 號, 查詢時它只要去找[特殊說明表]的相對ID
再反回相應[說明]內說, 這樣存取量及過程都會更有效率, 主表不必每一筆都給你保存[請於兩小時內用餐] 這此資料, 當查詢時也不必一筆一筆去校對文字內容

以上2點只是一小部分, 有興趣再討論吧~

		自动登录	找回密码
密码			免费注册

[求助] 批次匯入大量Excel資料至同一張資料表