Help run out of rows!!!!

[masplin]

I have a model that is connected to excel tables that are linked. I was trying to update it when I realised I now have more rows of transactions than the 1,048,000 excel limit. We have just recently started looking at moving all the data to an sql import which i know would not have this limit, but that is possibly a while away. Meanwhile the company has no other tool for running its finances and other business intelligence.

So before I give the CEO the bad news is there anything I can do quickly? I believe if all the data was in a CSV there is more room, but presumably as soon as i link that to powerpivot I would need to rewrite every measure? This is the same amount of work as getting all the data into sql and importing directly I think. Is the best option just to get an sql DB in place urgently and just rebuild all the measures once?

Thanks for any advice.

mike

 

[NeonRedSharpie]

Yea...once you've even come close to that limit it should have been in a database environment. You can TRY access, but sql is going to be your friend at this point.

 

[Asala42]

If it were me I'd either import that data into MS Access or look for a way to split the query in some logical way

[edit] Another thing that might be worthwhile is to review a recent export and look for "junk records", see if there's something you can filter off on the query side that you've been unnecessarily pulling this whole time.

 

[GDRIII]

PowerQuery might allow you to merge two sheets but, I have only a loose understanding of it's capabilities

 

[scottsen]

There will be some minor suck... but I think you will live n stuff

So, you have an immediate problem that deserves some immediate attention. I think SQL is a longer term play. Getting that data out of a sheet and into a CSV is pretty easy. Importing it to power pivot via the CSV ... easy. The only thing that really sucks is re-creating the 1 table. If there are measures on it... move them off to some other table first. Then nuke the table, bring in the table via the csv, move your measures back, recreate any relationships... done.

Then you can think about SQL once you are back to breathing...