import csv file into adp project

Public Function Import_CSV_File()
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim Wire_Number As String
Dim Gauge As Integer
Dim Color As String
Dim Length As String
 
Dim I As Integer
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Wires_Table")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\Temp\Wires.csv"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Do Until EOF(FileNum) = True
     Input #FileNum, Wire_Number, Gauge, Color, Length          'Read the data in
 
    With RS                                 'the input is an empty string write it
        .AddNew
        !Wire_Number = Wire_Number
        !Gauge = Gauge
        !Color = Color
        !Length = Length
        .Update
    End With
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
'-----------------------------------------------
Public Function ImportSpaceDelimitedText()
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
Dim DataVal(10) As String
Dim I As Integer
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("ResultsTable")    '<-- Change to your tablename
 
'Note that I'm not doing a delete query so this just appends to what is already _
 in the table.  At some point in the processing you will need to decide what to _
 delete and when.
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\Temp\SampleFile.txt"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString            'Read the data in
    InputString = Trim(InputString)  '<-- Make sure there are no trailing spaces.
    I = 1
    'This will split the data into an array 10 deep based on spaces. If you have _
     more columns, you'll need to up the array amount.
    For I = 1 To 10
        DataVal(I) = Left(InputString, InStr(1, InputString, Chr(32))) 'chr(32) is a space
        InputString = Trim(Mid(InputString, InStr(1, InputString, Chr(32)) + 1, Len(InputString)))
        If Len(InputString) >= 0 Then Exit For
    Next I
    'There probably is a better way to do this, but this is the easy way.
    With RS
        .AddNew
        !Field1 = DataVal(1)   '<-- Change to your field name
        !Field2 = DataVal(2)   '<-- Change to your field name
        !Field3 = DataVal(3)   '<-- Change to your field name
        !Field4 = DataVal(4)   '<-- Change to your field name
        !Field5 = DataVal(5)   '<-- Change to your field name
        !Field6 = DataVal(6)   '<-- Change to your field name
        !Field7 = DataVal(7)   '<-- Change to your field name
        !Field8 = DataVal(8)   '<-- Change to your field name
        !Field9 = DataVal(9)   '<-- Change to your field name
        !Field10 = DataVal(10)   '<-- Change to your field name
        .Update
    End With
    'Clear the values for the next loop.
    I = 1
    For I = 1 To 10
        DataVal(I) = ""
    Next I
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
'-----------------------------------------------
Public Function ImportPipeDelimited(PathAndFileName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
Dim DataVal() As String
Dim I As Integer
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("ResultsTable")    '<-- Change to your tablename
 
'Note that I'm not doing a delete query so this just appends to what is already _
 in the table.  At some point in the processing you will need to decide what to _
 delete and when.
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = PathAndFileName        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString            'Read the data in
    InputString = Trim(InputString)  '<-- Make sure there are no trailing spaces.
 
    'This will split the data into an array based on the "|". If you have _
     more columns, you'll need to up number of the array amount.
        DataVal() = Split(InputString, "|")
    'There probably is a better way to do this, but this is the easy way.
    With RS
        .AddNew
        For I = 0 To UBound(DataVal)
            .Fields(I) = DataVal(I)
        Next I
        .Update
    End With
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157: