I have a CSV file with several columns of data, the first column being a file name (example: filename.pdf). I am using FSOs and an array to put the data into columns. Unfortunately, the period in the first column of data (from the file name) is causing problems. Is there someway I can reassert that the data is text and to ignore the periods in the data?
As an example, this is how the data is brought in with a file name:"filename.pdf,"text1","text2","text3" etc. It needs to be read in as "filename.pdf,text1,text2,text3"...
Thanks for any suggestions you can offer!
Here is part of my code with notes:
Code:
Public Sub Importing()
Dim rs As DAO.Recordset
Dim rsMS4 As DAO.Recordset
Dim rsSF As DAO.Recordset
Dim FileLocation As String
Dim objFSO
Dim objFile
Dim strdata As String
Dim arrData
Dim Count As Integer
Dim strLine As String
Dim VID As String
Dim arrFileLines()
i = 0
'''SET THE FILE PATHWAY FOR THE CSV FILE HERE'''
FileLocation = DLookup("CSVLocation", "filelocations", "id=1")
'''IDENTIFY ACCESS TABLES AND CSV FILE'''
Set rs = CurrentDb.OpenRecordset("select * from [All VSMP Permit Projects]")
Set rsMS4 = CurrentDb.OpenRecordset("select * from [Receiving-Waters-TBL]")
Set rsSF = CurrentDb.OpenRecordset("select * from [Support Facility TBL]")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(FileLocation)
'''REACH EACH LINE OF THE CSV FILE'''
Do Until objFile.AtEndOfStream 'repeat this process until there's nothing left
ReDim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine ***Problem starts here***
' strdata = objFile.ReadLine 'the string data is a line in the file
Count = Count + 1 'Will count all the lines
i = i + 1
Loop
'''FOR EACH LINE OF THE CSV FILE EXCEPT THE HEADER'''
'''PUT THE NEW INFO IN THE VSMP TABLE'''
For i = 1 To (i - 1) 'start after the header line
'go to the last line of data
strdata = arrFileLines(i) 'set the string equal to each line
arrData = Split(strdata, ",") 'splits each line into a new column when there's a comma
With rs 'in the database
.AddNew 'make a new line
MsgBox (arrData(1))
.Fields.Item("Date on Info Form") = arrData(1) 'the first column goes in the first column
.Fields.Item("Project Permit #") = arrData(2) 'same
.Fields.Item("Project") = arrData(3) 'same
.Fields.Item("PPMS #") = arrData(4) 'same
.Fields.Item("UPC #") = arrData(5) 'same
.Fields.Item("Permit Status") = arrData(6) 'same