回答編集履歴
2
不要な定数を削除
answer
CHANGED
|
@@ -5,7 +5,6 @@
|
|
|
5
5
|
|
|
6
6
|
Public Sub ImportCSVFiles()
|
|
7
7
|
Const DIR_CSV = "YOUR_CSVFILES_DIRECTORY"
|
|
8
|
-
Const PATH_OUTPUT = DIR_CSV & "\" & "OUTPUT.csv"
|
|
9
8
|
Const MAX_CSV_FILENUM = 300
|
|
10
9
|
|
|
11
10
|
Dim DB As Database: Set DB = CurrentDb
|
1
CSVファイルへの書き出しからデータベースへの読み込みに変更
answer
CHANGED
|
@@ -1,48 +1,51 @@
|
|
|
1
|
-
出力データ仕様をみたところ、複数のcsvファイルを一つのcsvファイルにまとめたいのでしょうか？でしたらテーブルに読み込む必要はありません。勘違いでしたらごめんなさい。
|
|
2
|
-
一つずつcsvファイルを読み込んで、それを一行ずつ
|
|
1
|
+
愚直に一つずつcsvファイルを読み込んで、それを一行ずつデータベースに読み込めば良いと思います。テーブルはあらかじめ作成しましょう。その際m付きの値をDoubleに変換します。
|
|
2
|
+
以下にコードを載せます。参照設定で Microsoft Scripting Runtime を追加しています。
|
|
3
|
+
解決のヒントになれば幸いです。
|
|
3
4
|
```vba
|
|
5
|
+
|
|
4
|
-
Public Sub
|
|
6
|
+
Public Sub ImportCSVFiles()
|
|
5
7
|
Const DIR_CSV = "YOUR_CSVFILES_DIRECTORY"
|
|
6
8
|
Const PATH_OUTPUT = DIR_CSV & "\" & "OUTPUT.csv"
|
|
7
9
|
Const MAX_CSV_FILENUM = 300
|
|
8
|
-
|
|
10
|
+
|
|
9
|
-
Dim
|
|
11
|
+
Dim DB As Database: Set DB = CurrentDb
|
|
10
|
-
Dim
|
|
12
|
+
Dim RS As Recordset: Set RS = DB.OpenRecordset("YOUR_TABLE")
|
|
11
|
-
|
|
13
|
+
|
|
12
14
|
Dim FileNum As Long
|
|
13
15
|
For FileNum = 1 To MAX_CSV_FILENUM
|
|
14
|
-
|
|
16
|
+
ImportCSVFile RS, DIR_CSV & "\" & FileNum & ".csv", FileNum
|
|
15
17
|
Next
|
|
16
|
-
|
|
18
|
+
|
|
17
|
-
|
|
19
|
+
RS.Close
|
|
20
|
+
DB.Close
|
|
18
21
|
End Sub
|
|
19
22
|
|
|
20
|
-
Private Sub
|
|
23
|
+
Private Sub ImportCSVFile(ByVal RS As Recordset, ByVal CSVFilePath As String, ByVal FileNum As Long)
|
|
21
24
|
Dim FSO As FileSystemObject: Set FSO = New FileSystemObject
|
|
22
|
-
Dim
|
|
25
|
+
Dim TS As TextStream: Set TS = FSO.OpenTextFile(CSVFilePath)
|
|
23
26
|
Dim Row As Variant
|
|
24
|
-
Do Until
|
|
27
|
+
Do Until TS.AtEndOfStream
|
|
25
|
-
Row = Split(
|
|
28
|
+
Row = Split(TS.ReadLine, ",")
|
|
26
|
-
|
|
29
|
+
RS.AddNew
|
|
27
|
-
|
|
30
|
+
RS!NO = FileNum
|
|
28
|
-
|
|
31
|
+
RS!R# = Row(1)
|
|
29
|
-
|
|
32
|
+
RS!TP1 = Row(2)
|
|
30
|
-
|
|
33
|
+
RS!TP2 = Row(3)
|
|
31
|
-
|
|
34
|
+
RS!Min = ConvMinMaxToDbl(Row(4))
|
|
32
|
-
|
|
35
|
+
RS!Max = ConvMinMaxToDbl(Row(5))
|
|
33
|
-
Row(6), _
|
|
34
|
-
Row(7) _
|
|
35
|
-
|
|
36
|
+
RS!Result = Row(6)
|
|
37
|
+
RS!Actual = Row(7)
|
|
38
|
+
RS.Update
|
|
36
39
|
Loop
|
|
37
|
-
|
|
40
|
+
TS.Close
|
|
38
41
|
End Sub
|
|
39
42
|
|
|
40
43
|
' 21.72m -> 0.02172
|
|
41
|
-
Private Function
|
|
44
|
+
Private Function ConvMinMaxToDbl(ByVal NumLikeStr As String) As Double
|
|
42
45
|
If NumLikeStr Like "*m" Then
|
|
43
|
-
|
|
46
|
+
ConvMinMaxToDbl = CDbl(Replace(NumLikeStr, "m", "")) / 1000
|
|
44
47
|
Else
|
|
45
|
-
|
|
48
|
+
ConvMinMaxToDbl = CDbl(NumLikeStr)
|
|
46
49
|
End If
|
|
47
50
|
End Function
|
|
48
51
|
```
|