iReport – Removing Pagination

Uncategorized — Jeff Eske on November 28, 2012 at 4:35 pm

I have been trying to save some old data off of a database, before the database server is decommissioned.  I was hoping to save the data out in a .csv file, but the first problem that I ran into was that some of the columns that I wanted to keep contained carriage returns and newline characters.  When the data was saved out to .csv, the carriage returns and newlines would appear as the end of a record, causing said records to break.  Eventually, I was able to solve that issue.

Once issue was solved, I ran into a new one that took a while (way too long) to figure out.  The problem was, when I would save out a .csv file, I was still getting some records that were broken.  It appeared that there were STILL carriage returns and newlines within my records!

After a fair amount of research, I figured out that iReport itself was adding the offending characters when it was saving the data as a .csv.  Basically, wherever it was adding a page break within the report on the screen, it was adding a carriage return and newline within the .csv file.  Not very handy.  Fortunately, it was an extremely easy problem to solve.  All I needed to do, was set that particular report to not do pagination.

To disable pagination (page breaks) within iReport:

1> Right-click on the report that you want to remove page breaks from and click on “properties”.

2> Scroll down until you find “ignore pagination” and simply check the box.

3> Close the window and you should be ready to go.

What you end up with is one continuous list of records – just like you need for a .csv file.  I used this, along with the previously mentioned replace() function and was able to save out 47.000 individual records fairly cleanly.  Granted, I ran into 15-20 records that were still broken when I tried to import the .csv file into MySQL, but I would consider that a success!

Jeff Eske

iReport – Removing Carriage Returns and Newlines With the replace() Funtion

iReport — Jeff Eske on November 28, 2012 at 4:11 pm

For some time, I’ve been trying to save information out of an MS SQL database that’s going away.  I’m wanting the data in a .csv file and the problem that I’ve been having is that a couple of the columns that I want contain data that includes carriage returns and newline characters within the column.  When the data is saved out, those returns look like the end of a record, essentially breaking every record containing them.  This also makes the data virtually useless for importing into other databases, since the records are broken, column counts end up off, etc.

What ended up solving the problem for me was the replace() function within iReport.  Since I was only concerned with the content and not necessarily the formatting, I used replace() to take out the returns and replace them with a single space.  One additional thing that you’ll notice is that the data is on an old version of MS SQL, and after considerable trial-and-error, I found that I had to actually use cast() function as well.

The complete syntax that I used:

[code]

CAST(REPLACE(CAST(Column_Name as NVarchar(4000)),CHAR(13)+char(10),’ ‘) AS NText) as Some_Name

[/code]

Another issue that I thought I had run into was a problem with some columns only having a newline, not both.  Well, I found out that I can nest the first replace() function and use the first output as the input for the next, so that I could actually make another pass through the data to catch the individual newlines the second time around.  THAT setup starts to look really messy, due needing the cast() function as well…

The syntax for nesting cast() and replace() functions:

[code]

CAST(REPLACE(CAST(CAST(REPLACE(CAST(Column_Name as NVarchar(4000)),CHAR(13)+char(10),’ ‘) AS NText) as NVarchar(4000)),char(10),’ ‘) AS NText) as Some_Name

[/code]

This solved part of my problem.  Within my iReport report, the columns looked right and complete.  At that point, I was ready to save everything out as a .csv file.  That ended up being a minor problem also.

Jeff

Of Mice and Coffee

General — Jeff Eske on November 28, 2012 at 2:13 pm

I’ve found what, for me, has been a handy little tidbit of information.  I’ve found that coffee is a VERY effective mouse bait.  Yep, good ‘ol coffee.  A couple of years ago, I had a mouse break (chew) into one of my kitchen cupboards and ransack the place.  One of the main things it attacked?  My tea and instant coffee bags.  Little bugger!  Well, I fortified the cupboard and set traps in the kitchen, using peanut butter as bait.  I’ve used peanut butter for a long time, and that seemed to do the trick, but didn’t seem as effective as it should be.

The mouse problem went away and I didn’t think any more of it.  Then, last winter, we had a mouse problem at work.  What did they pillage in my desk?  My coffee and tea again!  THAT got me thinking.  The next time I had a need to catch a mouse, I set a trap with my standard peanut butter, BUT I sprinkled a little Dunkin’ Donuts Cinnamon Spice ground coffee on the peanut butter also.  Well, that made ALL the difference!

Mice seem to like coffee as much as some humans.  I’m sure the aroma probably helps draw them to their doom also.  Whatever the case, the only way I bait my mouse traps now is with BOTH peanut butter and coffee.

As long as they don’t get the coffee and then get away.  Imagine the damage that a jacked-up, caffeinated mouse could do…

Jeff

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. | Jeff's Blog