Excel load unmerge cells with new value

[#1]

Hi Guys

I am loading over 600 excel sheets into QV ( Its a data sorting project)

The issue is one of the columns Q ,[ Review Date],is merged ( randomly 3,2,7,8 lines)

See sample above

I am using either a Macro or Formula to unmerge and bring the correct values into the column,

This is the macro

Sub UnMergeA()

With Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

.MergeCells = False

With .SpecialCells(xlCellTypeBlanks)

.Value = .Value

.FormulaR1C1 = "=R[-1]C"

End With

End With

End Sub

It unmerges the cell and if the cell is blank looks to the cells above and takes the value of that cell and populates it in, if it is not blnak it retains value.

See Result below

I dont relish doing this for 600 sheets before the load, is it possible to write this as part of the load code in QV?

Thanks

Anne

[swuehl · Accepted solution]

Should be possible within the load filter, try something like

Directory;

LOAD Field1,

     Field2

FROM

Map1.xlsx

(ooxml, embedded labels, table is Table1, filters(

Replace(2, top, StrCnd(null))

));

You could easily create the correct filter with the assistant, enable transformation step, check out FILL filter option tab.

Hope this helps,

Stefan

[#3]

Hi Swuehl,

Im getting the following error :

Bad Zip File
LOAD [Account Line],

  [Next Annual Review Date]

FROM

(ooxml, embedded labels, table is Table1, filters(

Replace(2, top, StrCnd(null))

))

is it because i amended the script incorrectly ?

Any help is greatly appreciated

thanks,
Anne

[swuehl]

ooxml format specifier is for Excel2010 ( .xlsx) files. If you are trying to open an older Excel / .xls file. I think biff is the format to specify.

LOAD [Account Line],

  [Next Annual Review Date]

FROM

(biff, embedded labels, table is Table1, filters(

Replace(2, top, StrCnd(null))

))

Check also that embedded labels and Table1 is appropriate for your file.

Hope this helps,

Stefan

[#5]

Stefan

Thanks a mill

The Load is now completing,

But how to I build it within the table !

Not sure how to actually enable the filter !

Thanks so much

[swuehl]

Not quite sure what you mean with enabling the filter?

The filter should be enabled if you state a filter like

..filters(Replace(2, top, StrCnd(null)..

in your format specification.

Maybe I missed something that we need to consider for your setting, the table wizard should help you to create the correct filter. Have you tried the wizard?

[#7]

Stefan

Realised that I should have used the Wizard all along I got it going on now

Thanks a million for your help really appreciated

A