Importing Excel that contains blank Rows

mponter

Forums

Hi Brian,
We have a spreadsheet that is generated monthly by HR and is dumped into a shared folder in .xlsx format. Unfortunately the top 2 rows of the .xlsx file is used as a header which is throwing IDEA when I try to import the file.
My first guess is to have Excel grab the file open it, remove the rows and re-save the file then import with IDEA. Second is to use Excel to convert the .xlsx file to a .txt and import (ignoring the first few lines).
Either way, I'm having difficulty manipulating Excel from IDEA. Also, if we move towards running tests in IDEA Server I am not sure whether using intermediary applications is an option.  I feel like I'm overlooking something basic and wondered if you had any thoughts.
Regards, Mark
 

Brian Element Wed, 08/03/2016 - 07:58

Hi Mark,

Here is some code for the first option.  This will open your spreadsheet, remove the first two rows and then resave it at which time you can do the import.

A third option is to bring it in and remove the first two lines through an extraction, the downside with this is I assume you would then have to rename all the fields.

Function ModifySpreadsheet(sFilename as string)
Const xlUp = -4162
 Dim excel As Object
 Dim oBook As Object
 Dim oSheet As Object
 
 Set excel = CreateObject("Excel.Application")
  excel.Visible = False
  Set obook = excel.Workbooks.Open(sFilename)
   Set oSheet = oBook.Worksheets.Item(1)
    sSheetName = oSheet.Name
    oSheet.Rows("1:2").Delete Shift:=xlUp
   Set oSheet = Nothing
   oBook.Save
   oBook.Close (True)
  Set obook = Nothing
  excel.quit
 Set excel = Nothing
End Function