I have a CSV file that I have Access import into a table using a scripting FSO and reading each line of the CSV file. For any given import, there will be an unknown number of lines, so there a few loops set up. Something I've done in the past two hours has caused the Access to each column of the CSV file with quotes. For instance, the date 1/1/11 is being picked up as ""1/1/11"", and therefore unrecognized as a date. There are too many fields to find and replace all quotations- but more importantly, it had worked earlier today, so I would like to know what I've screwed up...
During the time that this import stopped working, I had tried some adobe code (and turned on adobe references) in a different Module of the database and added the file location as a string instead of placing it directly in the code. I have tried reversing these actions, but it didn't solve the problem
Here is a sample of my code, if it helps:
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 ' 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 rs.AddNew 'make a new line rs.Fields.Item("Date on Info Form") = arrData(1) rs.Fields.Item("Project Permit #") = arrData(2) rs.Fields.Item("Project") = arrData(3) rs.Fields.Item("PPMS #") = arrData(4) rs.Fields.Item("UPC #") = arrData(5) rs.Fields.Item("Permit Status") = arrData(6) End With '''SEND TO NEXT LINE''' Next i '''CLEAN UP THE RSs''' rs.Close rsMS4.Close rsSF.Close '''CONFIRM IMPORT''' MsgBox ("Shizam!") End Sub