Exporting financial report into excel and exceed rows - need alternative

Paula Eyerman

We are struggling with exporting an RE financial report into Excel - we have done it in the past fine but we have now exceeded the number of rows allowed.  Is there ANY way to remove the blank rows (before exporting it) or is there another way to get all the information so it can at least be converted to an Excel document.  I know we need a long term solution but for right now I am looking for an easy work around.  We have a process set up to use this document.  I even tried running the report with two different queries - trying to get 1/2 of the records at a time but that is not proving easy either.  I was pulling on the sort key and still not seeming to pull all the records. 

I also tried to pull is csv and that was a mess.....we only show the Excel 7 and Excel 8 and (etended versions)...


Any help is appreciated!


Paula.

Amy Dana

Paula Eyerman:

We are struggling with exporting an RE financial report into Excel - we have done it in the past fine but we have now exceeded the number of rows allowed.  Is there ANY way to remove the blank rows (before exporting it) or is there another way to get all the information so it can at least be converted to an Excel document.  I know we need a long term solution but for right now I am looking for an easy work around.  We have a process set up to use this document.  I even tried running the report with two different queries - trying to get 1/2 of the records at a time but that is not proving easy either.  I was pulling on the sort key and still not seeming to pull all the records. 

I also tried to pull is csv and that was a mess.....we only show the Excel 7 and Excel 8 and (etended versions)...


Any help is appreciated!


Paula.

Are you exporting the results of a Report? Have you tried to create an Export with the same info on the Report? That way you can skip all those random blank rows that Excel likes to throw in on the Report results.

Paula Eyerman

No - I think that is my only option at this point - so I am going to try that this morning.

Thank you!

Paula Eyerman

Amy Dana:

Paula Eyerman:

We are struggling with exporting an RE financial report into Excel - we have done it in the past fine but we have now exceeded the number of rows allowed.  Is there ANY way to remove the blank rows (before exporting it) or is there another way to get all the information so it can at least be converted to an Excel document.  I know we need a long term solution but for right now I am looking for an easy work around.  We have a process set up to use this document.  I even tried running the report with two different queries - trying to get 1/2 of the records at a time but that is not proving easy either.  I was pulling on the sort key and still not seeming to pull all the records. 

I also tried to pull is csv and that was a mess.....we only show the Excel 7 and Excel 8 and (etended versions)...


Any help is appreciated!


Paula.

Are you exporting the results of a Report? Have you tried to create an Export with the same info on the Report? That way you can skip all those random blank rows that Excel likes to throw in on the Report results.

 

So as I was creating the export - I realize I can't do it that way because it will create additional columns for the different funds and I need a seperate row for each gift....any other suggestions?

 

Alan French

Have you got any experience with Crystal Reports? You could change the export format from XLSX to MDB and then use that as a data source in Crystal, where it would be relatively straightforward to display each fund split on a separate line (assuming that's what you mean by having a separate row for each gift).

Paula Eyerman

Good suggestion,


I am filling in for a position and I was hoping to get through the interim with a quicker fix.  I do think this is how we will need to proceed in the long run - thank you!

 

Christine Cooke 2

Paula Eyerman:

We are struggling with exporting an RE financial report into Excel - we have done it in the past fine but we have now exceeded the number of rows allowed.  Is there ANY way to remove the blank rows (before exporting it) or is there another way to get all the information so it can at least be converted to an Excel document.  I know we need a long term solution but for right now I am looking for an easy work around.  We have a process set up to use this document.  I even tried running the report with two different queries - trying to get 1/2 of the records at a time but that is not proving easy either.  I was pulling on the sort key and still not seeming to pull all the records. 

I also tried to pull is csv and that was a mess.....we only show the Excel 7 and Excel 8 and (etended versions)...


Any help is appreciated!


Paula.

Is there a pattern to the blank rows that are generated?  If so that could be something(s) you exclude from your query so that less empty rows would populate.


I also think you were on the right track by pulling the query in parts.  You just have to figure out the best way to sort it based on your fields, that will allow you choose choose the first half and second half... or maybe you are at a point where it needs to be in thirds.

Paula Eyerman

every entry has two empty rows after it.....I tried to make all of the columns very small in the report to see if that eliminated the empty rows and it didn't.  If you can tell me how to check on the excel spreadsheet to see what is making the empty rows that would be great.  


I'm working on the query being split into names - but it looks like I need to add to all the OR statements and this query is used for other things - so not a real easy fix as I was hoping.    


Thanks!  

Marie Stark

Paula Eyerman:

every entry has two empty rows after it.....I tried to make all of the columns very small in the report to see if that eliminated the empty rows and it didn't.  If you can tell me how to check on the excel spreadsheet to see what is making the empty rows that would be great.  


I'm working on the query being split into names - but it looks like I need to add to all the OR statements and this query is used for other things - so not a real easy fix as I was hoping.    


Thanks!  


 

What I usually do is export to Microsoft Access.  It looks kind of messy in Access, so I create a query of just the fields that I need, then I export it back to Excel.  It' kind of cumbersome, but it gives me a clean export to Excel in the end.

Christine Cooke 2

Christine Cooke:

Paula Eyerman:

We are struggling with exporting an RE financial report into Excel - we have done it in the past fine but we have now exceeded the number of rows allowed.  Is there ANY way to remove the blank rows (before exporting it) or is there another way to get all the information so it can at least be converted to an Excel document.  I know we need a long term solution but for right now I am looking for an easy work around.  We have a process set up to use this document.  I even tried running the report with two different queries - trying to get 1/2 of the records at a time but that is not proving easy either.  I was pulling on the sort key and still not seeming to pull all the records. 

I also tried to pull is csv and that was a mess.....we only show the Excel 7 and Excel 8 and (etended versions)...


Any help is appreciated!


Paula.

Is there a pattern to the blank rows that are generated?  If so that could be something(s) you exclude from your query so that less empty rows would populate.


I also think you were on the right track by pulling the query in parts.  You just have to figure out the best way to sort it based on your fields, that will allow you choose choose the first half and second half... or maybe you are at a point where it needs to be in thirds.

 

I just reread your question.  Are you taking an actual canned report and exporting that into Excel?  That is why you are getting the two blank lines between each record.  That is a standard formatting thing you cannot get rid of.  If that is the case, then I suggest breaking the report into two or more parts by date and then putting those exports back together again after removing the extra lines from the export.


OR saving that canned report as a Query which is the checkbox option on the bottom of the first tab, and then Exporting that Query through Export, you will not get the extra rows between records/names that way.

Paula Eyerman

I exported to an mdb file yesterday but I think your suggestion - is perfect....I will work on that one today..  thank you!

Paula Eyerman

Thanks for the suggestion - so once again I went to export and when there are multiple funds it puts in a new column and I need them to be multiple rows so this won't work.


Paula

Brenda Heyer

Paula, When you go into the export functions for the financial report what options do you see?  When I choose the below options, I do not get any blank rows.




 

Brenda Heyer

Brenda Heyer:

Paula, When you go into the export functions for the financial report what options do you see?  When I choose the below options, I do not get any blank rows.




 

Oops, you can't see my screen shots.



 

Paula Eyerman

Brenda - I think you are a genius!!!!  This worked perfectly - THANK YOU!

 

Brenda Heyer

Paula,

I'm glad you were able to get what you needed.


Brenda