Results 1 to 4 of 4
  1. #1
    timpepu is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    13

    How to remove blank rows when I import from excel

    Hi !

    How I can remove empty rows when I import from excel ?

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "merkinnät", "thya.XLS", True, ""





    Br

    Timo

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about simply running a delete query after you import?

  3. #3
    mindea is offline Novice
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    11
    We do dozens of spreadsheet imports every month and run into this a lot. I wrote this code to delete the extra blank rows. All you have to do is pass the name of the table in question to the sub. The code builds a query that selects rows that contains no data, and deletes the rows.

    Sub DeleteBlankRows(TableName)

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim fld As Field
    Dim strSQL As String, strSelect As String, strWhere As String
    Dim i As Integer, iFieldCount As Integer

    Set dbs = CurrentDb
    Set rst = CurrentDb.OpenRecordset(TableName)
    i = 1
    iFieldCount = rst.Fields.Count
    For Each fld In rst.Fields
    strSelect = strSelect & "[" & TableName & "].[" & fld.Name & "] AS " & CStr(i)
    strWhere = strWhere & "([" & TableName & "].[" & fld.Name & "] is Null)"
    If i < iFieldCount Then
    i = i + 1
    strSelect = strSelect & ", "
    strWhere = strWhere & " AND "
    Else
    End If
    Next fld
    rst.Close

    strSQL = "Delete " & strSelect & " FROM " & TableName & " WHERE " & strWhere

    dbs.Execute strSQL
    dbs.Close

    End Sub

  4. #4
    mindea is offline Novice
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    11
    Another annoying thing that happens when importing spreadsheets is the blank fields that often get added on. F34, F35, F36 . . . etc. So I wrote this to remove the extra fields. It looks for fields named "F[some number]" and drops them from the table.

    Sub DeleteExtraFields(TableName)
    'Remove extra fields from imported table
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim fld As Field
    Dim strReturn As String
    Dim vFieldsToDrop As Variant
    Dim i As Integer
    Dim element As Variant
    i = 0
    ReDim DynArray(0)
    Set dbs = CurrentDb
    Set rst = CurrentDb.OpenRecordset(TableName)
    For Each fld In rst.Fields
    strReturn = fld.Name
    If Left(strReturn, 1) = "F" And IsNumeric(Right(strReturn, Len(strReturn) - 1)) Then
    ReDim Preserve DynArray(UBound(DynArray) + 1)
    DynArray(UBound(DynArray)) = strReturn
    Else
    End If
    Next fld
    rst.Close

    For Each element In DynArray
    strReturn = CStr(element)
    If Len(strReturn) > 0 Then
    dbs.Execute "Alter Table " & TableName & " Drop Column " & strReturn & ";"
    Else
    End If
    Next element

    dbs.Close

    End Sub

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

Similar Threads

  1. Exporting more than 65000 Access rows to Excel
    By TonyBender in forum Import/Export Data
    Replies: 8
    Last Post: 08-22-2013, 04:56 PM
  2. Import single cell from excel
    By zippy483 in forum Import/Export Data
    Replies: 9
    Last Post: 02-24-2010, 02:16 PM
  3. How to import named range from excel
    By timpepu in forum Import/Export Data
    Replies: 1
    Last Post: 02-19-2010, 11:26 AM
  4. Access to Excel transferring multiple rows to single row
    By peter_lawton in forum Import/Export Data
    Replies: 10
    Last Post: 09-23-2009, 10:16 AM
  5. Import excel sheets to access.
    By calexandru in forum Import/Export Data
    Replies: 0
    Last Post: 08-19-2009, 09:44 AM

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