Solved: Importing CSV file to Access

Hi Experts,

I have a CSV file, (comma delimated), which I need to import to Access.

The problem is that, the length of the columns is not fixed, and therefore my results keep varying.

This is some sample data.

DecimalYear, Year, Day, HourEnding, Mean, Max, Min, Std, n
1998.06997716895,1998,26,13,174.7737,175.4645,173.8365,.597093970829045,13
1998.0700913242,1998,26,14,173.505207692308,174.3511,173.0487,.348951678743761,13
1998.07066210046,1998,26,19,174.998866666667,175.4015,174.6347,.333858405130546,6
1998.07077625571,1998,26,20,175.07798,176.1997,174.2671,.733736478276468,10
1998.07089041096,1998,26,21,174.351135714286,174.5087,174.2146,9.90580374109552E-02,14
1998.07100456621,1998,26,22,174.464261538462,174.8448,174.0676,.189198372719213,13
1998.07111872146,1998,26,23,175.037907692308,175.3595,174.4877,.231419739385494,13
1998.07123287671,1998,26,24,175.085553846154,175.5905,174.2566,.379807983562166,13
1998.07134703196,1998,27,1,174.9078,174.9078,174.9078,0,1
1998.07146118721,1998,27,2,175.433,175.433,175.433,0,1
1998.07157534247,1998,27,3,174.2041,174.2041,174.2041,0,1
1998.07168949772,1998,27,4,174.7188,174.7188,174.7188,0,1

I have about 10 years of hourly data, I can open it in excel and then import it to Access, but it takes very long, and it also passed the 65,000 row limit in excel.

And I only need 5 columns from above, not all of the data

This is my code so far.

Dim fso As New FileSystemObject
Dim ts As TextStream
Dim Data As String
Dim sFileToGet As String
Dim start_Date, St_Time As Date
Dim Mean, St_Dev As Double
Dim n As Integer
 
sFileToGet = cbo_fileName1.Text
Set ts = fso.OpenTextFile(sFileToGet)
Data = ts.ReadLine
 
Dim cmd_ToAccess2 As New ADODB.Command
Dim RS_ToAccess2 As New ADODB.Recordset
With cmd_ToAccess2
    .ActiveConnection = m_Conn
    .CommandText = " Select * From tbl_MathIntegration;"
    .CommandType = adCmdText
End With
 
  With RS_ToAccess2
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd_ToAccess2
End with
 
Do While ts.AtEndOfStream <> True
 
start_Date = Format(DecimaltoDate(Left$(Data, 16)), "dd-mm-yyyy")
St_Time = Format(DecimaltoDate(Left$(Data, 16)), "hh:mm")
Mean = Mid$(Data, 28, 7)
St_Dev = Mid$(Data, 35, 7)
n = Mid$(Data, 45, 2)
 
RS_ToAccess2.AddNew
RS_ToAccess2!StartDate = start_Date
RS_ToAccess2!StartTime = St_Time
RS_ToAccess2!Mean = Mean
RS_ToAccess2!St_Dev = St_Dev
RS_ToAccess2!n = n
RS_ToAccess2.MoveNext
Data = ts.ReadLine
Loop
 
ts.Close
Set RS_ToAccess2 = Nothing

Open in new window