Results 1 to 8 of 8
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    Reading specific text from .csv file into Access table

    Hi All, I need modify this pre existing code to capture data from .CSV file from specific line but the .CSV files are changing every now and then.


    So instead of identifying i=1 or i=3 or 1>25, How can I identify the text in lines and then read the value?

    For Ex: Capture any thing next to Document Name on line 1 instead of saying i=1 because this Document name might be in line 3 in second file.
    or date can be in line 10 in one file and line 8 in another file. attached .csv file.

    Is there easy way with out much effort to change the existing code?


    Code:
    Sub DReport()
     
       Dim fso As New FileSystemObject
       Dim Tst As TextStream
       Dim strline As String
       Dim strFilePath As String
       Dim i As Integer
       Dim Strfilename As String
       Dim objDB As DAO.Database
       Dim mylog As DAO.Recordset
       Dim objFSO As Scripting.FileSystemObject
       Dim strInfile As String
       Dim operator As String
       Dim Username As String
       Dim Tester As String
       Dim MyArray() As String
       Dim ArrayOperator() As String
       DoCmd.SetWarnings False
       DoCmd.RunSQL "Delete * from ABI;"
       DoCmd.SetWarnings True
     
    strInfile = "\\project\ABI_testing\CSV_files\"
    Strfilename = "Sea_testing.csv" 
    strFilePath = strInfile & Strfilename
    Set objDB = CurrentDb()
    Set mylog = objDB.OpenRecordset("ABI")
     
    i = 1
     ' Reading .CSV file and creating ABI table Add a new record
    If fso.FileExists(strFilePath) Then
                Set Tst = fso.OpenTextFile(strFilePath, ForReading, False)
    Do Until Tst.AtEndOfStream
    ' Reading .CSV file and creating ABI table Add a new record
                             strline = Tst.ReadLine
                             If (i = 1) Then
                              Dim file_name As String
                             file_name = Replace(Mid(strline, InStr(strline, ":") + 1), ",", "")
                             End If
                             If (i = 3) Then
                             Username = Trim(Replace(Mid(strline, InStr(strline, ":") + 1), ",", ""))
                              If Trim(Username) = "xx9" Then operator = "Reporter 1"
                               ElseIf Trim(Username) = "kkk2" Then operator = "Reporter 2"
                              End If                        
                             ArrayOperator = Split(operator, " ")
                            
                             If (i = 8) Then
                               Dim rundate As String
                                    If Mid(strline, InStr(strline, ":") + 1) Like "*,,,*" Then
                                    rundate = Trim(Replace(Mid(strline, InStr(strline, ":") + 1), ",,,", ""))
                                  Else
                                    rundate = Trim(Replace(Mid(strline, InStr(strline, ":") + 1), ",,", ""))
                                 End If
                               Dim Date_Tested As Date
                               Dim DateTime_Tested As Date
                                Date_Tested = DateValue(Mid([rundate], InStr([rundate], ",") + 2))
                                DateTime_Tested = CVDate(Mid([rundate], InStr([rundate], ",") + 1))
                                End If                                               
                             If (i > 28) Then
                                
                                 Dim CT_num As Integer
                                 Dim ID_NUMBER As Variant
                                 Dim PRIMER_PROBE_SET As String
                                 Dim temp_str As String
                                 temp_str = Mid(strline, InStr(strline, ":") + 1)
                                 MyArray = Split(temp_str, ",")                             
                                 mylog.AddNew
                                 mylog![Run_file_Name] = file_name
                                 mylog![Username] = Username
                                 mylog![operator] = operator
                                 mylog![Tester] = Left(ArrayOperator(0), 1) & ArrayOperator(1)
                                 mylog![rundate] = rundate
                                 mylog![Date_Tested] = Date_Tested
                                 mylog![DateTime_Tested] = DateTime_Tested
                                 mylog![SampleID] = UCase(MyArray(1))                                                                
                                 If IsNumeric(MyArray(1)) Then
                                   mylog![ID_NUMBER] = MyArray(1)
                                 Else
                                   mylog![ID_NUMBER] = 0
                                 End If                        
                               mylog.Update
                             End If
                             i = i + 1
          Loop      
                    Tst.Close
          End If
          mylog.Close                      ' Temporarily close the report log table
           DoCmd.SetWarnings False
       End Sub
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do not loop thru records.
    Link the CSV file as an external table,
    then run a query with your criteria to filter only records you want.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Unfortunately, the CSV file does not appear simple enough for linking.

    Have to read each line and process through conditional code to test for particular strings and perform appropriate action.
    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.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There were a few things wrong with the code; in rewriting the code I fixed them.

    The "DateTime_Tested" is not just the time, it is the date and time; I don't know if that is what you want.

    You will need a reference set to "Microsoft Scripting Runtime" for this code to work. I was going to post the dB, but since you have A2003 and I am using A2010, I'll just post the code.

    Code:
    Option Compare Database    '<<-- should be at the top of EVERY code module
    Option Explicit            '<<-- should be at the top of EVERY code module
    
    Sub DReport_Mod()
    
        Dim objDB As DAO.Database
        Dim mylog As DAO.Recordset
        Dim fso As New FileSystemObject
    
        '    Dim objFSO As Scripting.FileSystemObject   '<<<---- Not used
        Dim Tst As TextStream
        Dim strLine As String
        Dim strFilePath As String
        Dim strFileName As String
        Dim strInfile As String
        Dim operator As String
        Dim Username As String
        '    Dim Tester As String                '<<--field in table
        Dim ArrayOperator() As String
        Dim MyArray() As String
        Dim file_name As String
        Dim rundate As String
        Dim PRIMER_PROBE_SET As String  '<<<---- Not used
        Dim Date_Tested As Date
        Dim DateTime_Tested As Date
        '    Dim ID_NUMBER As Variant       '<<--field in table
        Dim temp_str As String
        Dim CT_num As Integer        '<<<---- Not used
        Dim i As Integer
        Dim iPos As Integer    '<<-- Position in string
        Dim iEnd As Integer
    
        Set objDB = CurrentDb()
    
        '    DoCmd.SetWarnings False
        '    DoCmd.RunSQL "Delete * from ABI;"
        '    DoCmd.SetWarnings True
        objDB.Execute "Delete * from ABI;", dbFailOnError
    
        strInfile = "\\project\ABI_testing\CSV_files\"
        strFileName = "Sea_testing.csv"
        strFilePath = strInfile & strFileName
        'open recordset
        Set mylog = objDB.OpenRecordset("ABI")
    
        i = 1
        ' Read .CSV file and Add a new records to table ABI
        If fso.FileExists(strFilePath) Then
            Set Tst = fso.OpenTextFile(strFilePath, ForReading, False)
            Do Until i = 28
                iPos = 0
                ' Reading .CSV 
                strLine = Tst.ReadLine
                iPos = InStr(strLine, ":")
                If iPos > 0 Then
                    temp_str = Left(strLine, iPos + 0)
                    Select Case temp_str
                        Case "Document Name:"
                            file_name = Trim(Replace(Mid(strLine, InStr(strLine, ":") + 1), ",", ""))
                        Case "User:"
                            Username = Trim(Replace(Mid(strLine, InStr(strLine, ":") + 1), ",", ""))
                            If Trim(Username) = "xx9" Then
                                operator = "Reporter 1"
                            ElseIf Trim(Username) = "kkk2" Then
                                operator = "Reporter 2"
                            End If
                            ArrayOperator = Split(operator, " ")
                        Case "Run Date:"
                            rundate = Trim(Mid(strLine, iPos + 1))
                            temp_str = ""
                            temp_str = Right(rundate, 1)
                            Do Until temp_str <> ","
                                If Right(rundate, 1) = "," Then
                                    rundate = Left(rundate, Len(rundate) - 1)
                                    temp_str = Right(rundate, 1)
                                End If
                            Loop
                            Date_Tested = DateValue(Mid([rundate], InStr([rundate], ",") + 2))
                            DateTime_Tested = CVDate(Mid([rundate], InStr([rundate], ",") + 1))
                        Case "Last Modified:"
                            'do nothing
                    End Select
                End If
                i = i + 1
            Loop
    
            'now get well info
            strLine = Tst.ReadLine
            Do Until Tst.AtEndOfStream
                strLine = Tst.ReadLine
    
                temp_str = Mid(strLine, InStr(strLine, ":") + 1)
                MyArray = Split(temp_str, ",")
                mylog.AddNew
                mylog![Run_file_Name] = file_name
                mylog![Username] = Username
                mylog![operator] = operator
                mylog![Tester] = Left(ArrayOperator(0), 1) & ArrayOperator(1)
                mylog![rundate] = rundate
                mylog![Date_Tested] = Date_Tested
                mylog![DateTime_Tested] = DateTime_Tested
                mylog![SampleID] = UCase(MyArray(1))
                If IsNumeric(MyArray(1)) Then
                    mylog![ID_NUMBER] = MyArray(1)
                Else
                    mylog![ID_NUMBER] = 0
                End If
                mylog.Update
            Loop
    
        End If
    
    
        'clean up
        On Error Resume Next
        Tst.Close
        mylog.Close
    
        Set mylog = Nothing
        Set fso = Nothing
        Set objDB = Nothing
    
        MsgBox "Done"
    End Sub

  5. #5
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Steve,
    Thank you so much for spending time to debug the program and explaining it in comments.
    Program ran successfully as desired but failed at line objDB.Execute "Delete * from ABI;", dbFailOnError

    Run-time error ‘91’:

    Object variable or With block variable not set
    So I changed back to the original code to delete the existing data before updating the table..
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * from ABI;"
    DoCmd.SetWarnings True

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by stalk View Post
    <snip>Program ran successfully as desired but failed at line objDB.Execute "Delete * from ABI;", dbFailOnError <snip>
    Do you have a reference set for "Microsoft DAO 3.6 Object Library"? Using the "application.Execute" command is faster than using the "RunSQL" command.


    I hate using a counter set to a specific number, so if the format of the CSV file will ALWAYS have the "Well Data" as the last X number of lines, you could try this version.
    Code:
    Option Compare Database    '<<-- should be at the top of EVERY code module
    Option Explicit            '<<-- should be at the top of EVERY code module
    
    Sub DReport_Mod2()
        'Mod 2 version of code - does not use a counter to track loops
        '
        Dim objDB As DAO.Database
        Dim mylog As DAO.Recordset
        Dim fso As New FileSystemObject
    
        Dim Tst As TextStream
        Dim strLine As String
        Dim strFilePath As String
        Dim strFileName As String
        Dim strInfile As String
        Dim operator As String
        Dim Username As String
        Dim ArrayOperator() As String
        Dim MyArray() As String
        Dim file_name As String
        Dim rundate As String
        Dim Date_Tested As Date
        Dim DateTime_Tested As Date
        Dim temp_str As String
        Dim iPos As Integer
        Dim iEnd As Integer
    
        '    Dim i As Integer
        '    Dim ID_NUMBER As Variant       '<<--field in table
        '    Dim Tester As String                '<<--field in table
        '    Dim objFSO As Scripting.FileSystemObject   '<<<---- Not used
        Dim CT_num As Integer        '<<<---- Not used
        Dim PRIMER_PROBE_SET As String  '<<<---- Not used
    
        Set objDB = CurrentDb()
    
        '    DoCmd.SetWarnings False
        '    DoCmd.RunSQL "Delete * from ABI;"
        '    DoCmd.SetWarnings True
        objDB.Execute "Delete * from ABI;", dbFailOnError
    
        strInfile = "\\project\ABI_testing\CSV_files\"
        strFileName = "Sea_testing.csv"
        strFilePath = strInfile & strFileName
        'open recordset
        Set mylog = objDB.OpenRecordset("ABI")
    
        ' Reading .CSV file and  Add a new records to table ABI
        If fso.FileExists(strFilePath) Then   'If 1
            Set Tst = fso.OpenTextFile(strFilePath, ForReading, False)
    
            Do Until Tst.AtEndOfStream   'Do 1
                iPos = 0
                ' Reading .CSV file and creating ABI table Add a new record
                strLine = Tst.ReadLine
                iPos = InStr(strLine, ":")
                If iPos > 0 Then   'If 2
                    temp_str = Left(strLine, iPos + 0)
                    Select Case temp_str
                        Case "Document Name:"
                            file_name = Trim(Replace(Mid(strLine, InStr(strLine, ":") + 1), ",", ""))
                        Case "User:"
                            Username = Trim(Replace(Mid(strLine, InStr(strLine, ":") + 1), ",", ""))
                            If Trim(Username) = "xx9" Then   'If 3
                                operator = "Reporter 1"
                            ElseIf Trim(Username) = "kkk2" Then   'If 3
                                operator = "Reporter 2"
                            End If    'If 3
                            ArrayOperator = Split(operator, " ")
                        Case "Run Date:"
                            rundate = Trim(Mid(strLine, iPos + 1))
                            temp_str = ""
                            temp_str = Right(rundate, 1)
                            Do Until temp_str <> ","
                                If Right(rundate, 1) = "," Then   'If 4
                                    rundate = Left(rundate, Len(rundate) - 1)
                                    temp_str = Right(rundate, 1)
                                End If    'If 4
                            Loop
                            Date_Tested = DateValue(Mid([rundate], InStr([rundate], ",") + 2))
                            DateTime_Tested = CVDate(Mid([rundate], InStr([rundate], ",") + 1))
                        Case "Last Modified:"
                            'do nothing
                    End Select
                Else   'If 2
                    If Left(strLine, 4) = "Well" Then   'If 5
                        'check if at "Well Data" line
                        Do Until Tst.AtEndOfStream    'Do 2
                            strLine = Tst.ReadLine
    
                            temp_str = Mid(strLine, InStr(strLine, ":") + 1)
                            MyArray = Split(temp_str, ",")
                            mylog.AddNew
                            mylog![Run_file_Name] = file_name
                            mylog![Username] = Username
                            mylog![operator] = operator
                            mylog![Tester] = Left(ArrayOperator(0), 1) & ArrayOperator(1)
                            mylog![rundate] = rundate
                            mylog![Date_Tested] = Date_Tested
                            mylog![DateTime_Tested] = DateTime_Tested
                            mylog![SampleID] = UCase(MyArray(1))
                            If IsNumeric(MyArray(1)) Then   'If 6
                                mylog![ID_NUMBER] = MyArray(1)
                            Else    'If 6
                                mylog![ID_NUMBER] = 0
                            End If   'If 6
                            mylog.Update
                        Loop   'Do 2
                    End If     'If 5
                End If   'If 2
    
            Loop   'Do 1
        End If    'If 1
    
    
        'clean up
        On Error Resume Next
        Tst.Close
        mylog.Close
    
        Set mylog = Nothing
        Set fso = Nothing
        Set objDB = Nothing
    
        MsgBox "Done"
    End Sub
    (The "IF 1", "Do 1", etc comments are to help me keep track of the looping.......)

  7. #7
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thanks again Steve. It worked this time with out making any changes to your first version of the code

    Yes, "Microsoft DAO 3.6 Object Library" is checked, but it failed the first time. Not sure why. i am using Access 2016 and the database is still in .mdb format, so some times I am not surprised to see little hiccups here and there.

    Also I am not worried about making it run faster, as I will be processing two or three files a day.

    Regarding your second version of code....

    Actually "Well Data" in some files starts at line 25 and in some starts at 28 and number of lines changes in each files.
    Some times there will be 56 lines or 64 line or 126 lines of data after the "Well Data" position.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Actually "Well Data" in some files starts at line 25 and in some starts at 28 and number of lines changes in each files.
    Some times there will be 56 lines or 64 line or 126 lines of data after the "Well Data" position.
    As long as it is all "well data" lines after the line that begins "Well Data, Sample Name,....", all lines should be read whether the well data starts at line 25 or 28 or 30.


    *I would suggest trying/using the second version of the code since the well data lines can start at line 25 or 28 or 30.




    Good luck with your project......

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

Similar Threads

  1. Replies: 3
    Last Post: 02-21-2018, 07:32 PM
  2. Access Reading File Path Incorrectly "..\.."
    By tdoolittle in forum Programming
    Replies: 5
    Last Post: 10-01-2014, 02:53 PM
  3. Replies: 7
    Last Post: 06-06-2013, 10:32 AM
  4. Exporting table from access to text file
    By narendrabr in forum Import/Export Data
    Replies: 3
    Last Post: 01-08-2013, 09:59 AM
  5. Importing text file into Access Table
    By Anthony in forum Import/Export Data
    Replies: 13
    Last Post: 09-23-2009, 04:47 PM

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