Import from excel and delete unwanted data

[oxicottin]

Hello, I need to import from Excel some data BUT not all data so after I import, I need to delete what is not needed. Using the below code behind my button to import it imports all the data from the workbook. In the image I need to only collect the data above the red line I drew in the image and delete everything below it.

I have a second question, the number I highlighted in green in my image, how can I collect that number in another temp table? This report is always the same it might just be longer or shorter in rows.

Private Sub cmdImport4WeekSchedule_Click()
    
    Const msoFileDialogFilePicker As Long = 3
    Dim fDialog As Object
    Dim myCurrentDir As String

 
'Dialog box to open to users Desktop
    myCurrentDir = "C:\Documents and Settings\" & Environ("username") & "\Desktop\"
    
'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 'Instantiates the variable creating a filepicker object using late binding
    With fDialog
        .AllowMultiSelect = False 'Does not allow selecting more than one file
        .InitialFileName = myCurrentDir 'Open the users desktop folder
        .Title = "Please Select the 4 Week Schedule.xls File." 'Set the file dialog title
        .Filters.Clear 'Clears the file dialog file type existing filters
        .Filters.Add "Excel", "*.xls, *.xlsx" 'This file dialog will only allow the selection of .xls files
        .Show
        
        If .SelectedItems.Count = 1 Then 'A file was selected
            
'Delete the existing temperary table
            Status "Deleting previous import table" 'Displays status in text box
            On Error Resume Next 'Incase you deleted the tbl_Import4WeekSchedule table you wont get error
            DoCmd.DeleteObject acTable, "tbl_Import4WeekSchedule"
            On Error GoTo 0 'Turn back on error handeling

'IMPORTING SHEET from selected .xls file
            Status "Importing 4 Week Schedule Data" 'Displays status in text box
            DoCmd.TransferSpreadsheet acImport, , "tbl_Import4WeekSchedule", .SelectedItems(1), False
            
        Else 'No file was selected give message
            MsgBox ("No file was selected."), vbInformation, "No File"
        End If
    End With
 
End Sub

 

[Gasman]

I would probably delete what is not required in the worksheet and import the rest. As you know where to delete from, you can grab that cell value easily enough.

 

[DHookom]

Delete where the date field is greater than 11/27/2024.

 

[plog]

Can you logically explain what data you need from the file? 'Where the red squiggly line is on the screenshot' isn't something that can be coded into Access.

Is it somehow related to a date? Is it related to where the first blank row appears? And don't give me logic for just this one specific file, give me a general explanation that will work on all files.

 

[oxicottin]

The code below works because I just deleted everything after the row of dates now I need to figure out how to get that number I highlighted in green? Suggestions on that?

Private Sub cmdImport4WeekSchedule_Click()
    
    Const msoFileDialogFilePicker As Long = 3
    Dim fDialog As Object
    Dim myCurrentDir As String
    
    
'Dialog box to open to users Desktop
    myCurrentDir = "C:\Documents and Settings\" & Environ("username") & "\Desktop\"
    
'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 'Instantiates the variable creating a filepicker object using late binding
    With fDialog
        .AllowMultiSelect = False 'Does not allow selecting more than one file
        .InitialFileName = myCurrentDir 'Open the users desktop folder
        .Title = "Please Select the 4 Week Schedule.xls File." 'Set the file dialog title
        .Filters.Clear 'Clears the file dialog file type existing filters
        .Filters.Add "Excel", "*.xls, *.xlsx" 'This file dialog will only allow the selection of .xls files
        .Show
        
        If .SelectedItems.Count = 1 Then 'A file was selected
            
'Delete the existing temperary table
            On Error Resume Next 'Incase you deleted the tbl_Import4WeekSchedule table you wont get error
            DoCmd.DeleteObject acTable, "tbl_Import4WeekSchedule"
            On Error GoTo 0 'Turn back on error handeling
            
'IMPORTING SHEET from selected .xls file
'DoCmd.TransferSpreadsheet acImport, , "tbl_Import4WeekSchedule", .SelectedItems(1), False
            DoCmd.TransferSpreadsheet acImport, , "tbl_Import4WeekSchedule", .SelectedItems(1), False, "A2:H300"
            
        Else 'No file was selected give message
            MsgBox ("No file was selected."), vbInformation, "No File"
        End If
    End With
    
    Dim FoundEnd As Boolean
    Dim rs As Recordset
    Dim S As String
    Set rs = CurrentDb.OpenRecordset("tbl_Import4WeekSchedule")
    FoundEnd = False
    While Not rs.EOF
        S = Nz(rs!F1, "")
        If Not IsDate(S) Then FoundEnd = True
        If FoundEnd Then rs.Delete
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
End Sub

 

[MajP]

I would think you simply move to the next line if it always is in that format. So I would think it is simply rs!Description (or whatever field name).

While Not rs.EOF
        S = Nz(rs!F1, "")
        If Not IsDate(S) Then FoundEnd = True
        If FoundEnd Then rs.Delete
        rs.MoveNext 'its the next line after the delete
    Wend
    
    MSGBOX rs!description

Deleting a record does not automatically cause the next record to become the current record; to move to the next record you must use the MoveNext method. Be aware that after you have moved off the deleted record, you cannot move back to it.

 

[oxicottin]

I am attaching the database and a excel sheet. In the excel sheet the yellow cell row is the blank row and the green highlighted number is what I need in a separate temp table. The database I attached I ran the code so that the table reflects what I'm after in that table.

Sorry had to .zip the excel sheet it wouldn't allow upload unless i did....

 

[Gasman]

I will not bother, as my idea was to trim the data required for import.

 

[oxicottin]

I will not bother, as my idea was to trim the data required for import.

I'm just trying to automate the process, if I was to open the excel workbook I might as well just use Excel and automate it from that or copy and paste it into my test database. I appreciate your input, happy holidays and have a great new year!

 

[Gasman]

Well @Majhas alrwady shown you how to get that value, so unsure as to what I could add to that.
Personally, I would grab that value, then delete the rest, but is just me.

 

[Isaac]

I'm just trying to automate the process, if I was to open the excel workbook I might as well just use Excel and automate it from that or copy and paste it into my test database. I appreciate your input, happy holidays and have a great new year!

Not necessarily all or nothing, no - I would pay equal attention to both approaches as either may serve you well, depending on your comfort in coding and who knows what all else is affecting this process.

Yes, in Gasman's suggestion you would use Excel automation -but there is nothing wrong with that. I've had SSIS packages in sql server as well as Access processes with text files that actually used vba to go into the text file and fix something before the host-program-import-automation began, as it was loads easier to do that than figure out how to configure my 'import process' to handle something really weird.

Regardless, if you did take this approach, all of the code would still be hosted inside Access, in the main procedure sub.

I can see the distinct possibility that when trying to implement your unusual logic about what you do or don't want imported, it might be easier to use some Excel automation for that portion of it than trying to figure out how to create Queries to find/identify it - not sure, just saying consider it

 

[MajP]

I am assuming you always have the same fields and format.
If that is the case make a very simple query.

SELECT tbl_import4weekschedule.f4 AS ReturnValue
FROM   tbl_import4weekschedule
WHERE  ( ( ( tbl_import4weekschedule.f4 ) NOT LIKE "hours available*" )
         AND ( ( tbl_import4weekschedule.f1 ) IS NULL ) );

This will return

ReturnValue
22.17
19.67

qryValues

If you only care about the first value then add a Select top 1.

I do not know if you need the second value 19.67. Put here is a function that would then allow you to get the first, second, third of these numbers.

Public Function GetValue(Optional WhichOne As Integer = 1) As Double
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("qryValues")
  If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    ' the rs is zero based but work in first, second, etc. value
    WhichOne = WhichOne - 1
    rs.Move WhichOne
    If Not rs.EOF Then GetValue = rs!ReturnValue
  End If
End Function

if you need to get rid of the null lines. simply
simply build the query
qryDeleteNullRows and call in code

DELETE tbl_import4weekschedule.f1,
       tbl_import4weekschedule.f2,
       tbl_import4weekschedule.f3,
       tbl_import4weekschedule.f4,
       tbl_import4weekschedule.f6
FROM   tbl_import4weekschedule
WHERE  ( ( ( tbl_import4weekschedule.f1 ) IS NULL )
         AND ( ( tbl_import4weekschedule.f2 ) IS NULL )
         AND ( ( tbl_import4weekschedule.f3 ) IS NULL )
         AND ( ( tbl_import4weekschedule.f4 ) IS NULL )
         AND ( ( tbl_import4weekschedule.f6 ) IS NULL ) );

If you want to delete the Available Hours line. Build the query qryDeleteAvailable and call in code

DELETE tbl_Import4WeekSchedule.F4
FROM tbl_Import4WeekSchedule
WHERE (((tbl_Import4WeekSchedule.F4) Like "*available*"));

Do you want to truncate after 22.17 and then continue processing to 19.67?

 

[MajP]

I broke it up so you can see the steps. That gives you a clean set with only a line for the values 22.17 and 19.67 along with the data
You can get those values from the function
I do not know if you are then processing the data. But I assume:
You can read down until F4 = 22.17 then do something with that block of data.
Then read down from there to 19.67 and do something with that block of data.

 

[MajP]

Here is a short version to only do what you asked. Get 22.17 then remove everything but the data above it.
No queries or external functions

 Const tablename = "tbl_Import4WeekSchedule"
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim FirstValue As Double

 
  ImportFile 'moved all your import code into here without the clean up code

  strSql = "Select top 1 F4 from " & tablename & " where f1 is null and F4 Not like '*Available*' "
  Set rs = CurrentDb.OpenRecordset(strSql)
  If Not rs.EOF then
     FirstValue = CDbl(rs!f4)
    'MsgBox FirstValue
    strSql = "Delete * from " & tablename & " where f4 is null or F4 like '*Available*' "
    CurrentDb.Execute strSql
    'delete 22.17 line and below
    Set rs = CurrentDb.OpenRecordset(tablename, dbOpenDynaset)
    rs.FindFirst "F4 = '" & FirstValue & "'"
    Do While Not rs.EOF
      rs.Delete
      rs.MoveNext
    Loop
 end if
  DoCmd.OpenTable tablename