Results 1 to 8 of 8
  1. #1
    Ashe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Location
    Blacksburg VA
    Posts
    65

    Quotes being added to CSV import during VBA read

    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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Code:
             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
    You have a "With rs" and then you go ahead and use rs???
    Try:
    Code:
             With rs 'in the database
                
                .AddNew 'make a new line
                
                   .Fields.Item("Date on Info Form") = arrData(1) 
                    .Fields.Item("Project Permit #") = arrData(2) 
                    .Fields.Item("Project") = arrData(3) 
                    .Fields.Item("PPMS #") = arrData(4)
                    .Fields.Item("UPC #") = arrData(5)
                    .Fields.Item("Permit Status") = arrData(6)
            End With

  3. #3
    Ashe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Location
    Blacksburg VA
    Posts
    65
    You're right, that was sloppy.
    I removed the additional rs's, but it has not resolved the quoting issue.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Phooie! It was a shot in the dark. Have you single stepped the code to see where the extra quotes are inserted?

  5. #5
    Ashe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Location
    Blacksburg VA
    Posts
    65
    I did step through it, and the most interesting thing I noticed was a missing quotation mark. The first column of the data (which we could call arrData(0)) was the file name. The CSV file is created from a merge of multiple Adobe PDF's form data, and Adobe automatically loads each file name as the first column. So arrData(0)= filename.pdf. I guess the period alters how the array is read, because it was reading in as "filename.pdf,"text1","text2","text3" etc. It needs to be read in as "filename.pdf,text1,text2,text3"etc. I replaced "filename.pdf" with "a" and it imported just fine.

    -I want the code to skip this first column, but I'm not sure how I'll be able to do that if it the period causes it to break so quickly... I think I need something right after "set objFile..." to say disregard the first column. Any ideas?
    Thanks for your help!



    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 '''***Picks up incorrectly 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
                
                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

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I'm sorry but I have not played with the fso before. I'm not mutch help here, sorry.

  7. #7
    Ashe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Location
    Blacksburg VA
    Posts
    65
    Thanks anyway! I'm going to post this question as a new question.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Good idea.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 10-12-2011, 11:08 AM
  2. Database to produce quotes
    By rossp0203 in forum Database Design
    Replies: 2
    Last Post: 05-26-2011, 10:15 AM
  3. Problems with Recordsets when items contain quotes.
    By caddcop in forum Programming
    Replies: 6
    Last Post: 05-17-2011, 07:49 AM
  4. Remove Quotes within Data Values
    By kfschaefer in forum Programming
    Replies: 0
    Last Post: 02-26-2009, 01:15 PM
  5. Replies: 2
    Last Post: 10-19-2006, 04:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums