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

    Periods breaking CSV in objFile.ReadLine

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't just use DoCmd.TransferText?

    I tested your code. The dot is not causing any issue for me. I did get a subscript out of range on the arrData indexing.
    Had to change to 0,1,2,3,4,5

    Cannot replicate your issue. Do you want to provide your csv file for testing?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ashe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Location
    Blacksburg VA
    Posts
    65
    Sure-
    The attachment function for the forum is calling my csv an invalid file, so I've just copied my test rows here:
    ,1,2,3,4,5,6,7
    1.pdf,1/1/2011,1,1,1,1,Active,TRUE
    2.pdf,2/2/2022,,,,,Terminated,FALSE

    I was trying to avoid copying in the file name, which is why the code starts with arrData(1) instead of arrData(0)....maybe I should try importing the file name as part of the database table also (?). It's not actually necessary, but it could help.

    I'm also looking at this and wondering if the first row is causing a problem because it starts with blank. When adobe merges form data, it puts a header in the csv column (so I had labeled these columns as the array would import them- hence 1,2,3,etc.). Perhaps I need to add something in that area. I'll test these theories and update you.
    Thanks for your help!

  4. #4
    Ashe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Location
    Blacksburg VA
    Posts
    65
    Update: I gave the first column a title (the letter "A") and the import is now working:
    The updated sample CSV reads like this:

    A,1,2,3,4,5,6,7
    1.pdf,1/1/2011,1,1,1,1,Active,TRUE
    2.pdf,2/2/2022,,,,,Terminated,FALSE

    and it works...now I just need to figure out how to automatically get that into the file. Do you have any recommendations on how I might insert text into that first row, or have it skip that row?

  5. #5
    Ashe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Location
    Blacksburg VA
    Posts
    65
    I got it!
    I added an if statement that says to skip the header row... the updated code is below:
    Thanks for the support..


    Code:
    Option Compare Database
    
    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)
                If i = 0 Then 'this is the first line of the csv file (the header)
                objFile.skipline 'don't read this first line
                Else 'this is not the first line
                    arrFileLines(i) = objFile.ReadLine 'read this line
                End If
            Count = Count + 1 'Will count all the lines
            i = i + 1 'Move to the next row
        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
                                      'arrFileLines(0) was skipped and = "empty"
            
            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

  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,922
    Quote Originally Posted by Ashe View Post
    The attachment function for the forum is calling my csv an invalid file...
    It does not say it but all you need do is zip of the file and the forum will accept a zip file.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-18-2011, 01:59 PM
  2. Breaking the string into Text / Number
    By Amerigo in forum Queries
    Replies: 15
    Last Post: 05-20-2011, 03:29 PM
  3. plot points for missing periods of time
    By ajetrumpet in forum Reports
    Replies: 0
    Last Post: 09-13-2010, 05:34 PM
  4. Change Financial Quater Periods
    By scorched9 in forum Access
    Replies: 0
    Last Post: 01-26-2010, 08:38 PM
  5. Replies: 16
    Last Post: 01-13-2010, 04:31 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