Max row setting for Personal Data Files

building reports desktopintelligence
1

Hello all!

I couldn’t find this in the archives, so I hope someone can help out. I’m trying to bring in two personal data providers from an Excel worksheet. I have no problems connecting or bringing back data, but only 16,383 records come back in each case. The sheets are of varying length (18,000 and 21,000 rows respectively), so it is truncating the data at the 16,383’rd record. Is there a setting to modify the row limit for personal data files, as there is for universes on Designer? Thanks in advance!!

BO 4.1.4 (soon to be 5.1)

Steve Large
Software Engineer I
Information Services
Sprint Publishing and Advertising

Listserv Archives (BOB member since 2002-06-25)

2

In a message dated 01-03-07 20:27:33 EST, you write:

I couldn’t find this in the archives, so I hope someone can help out. I’m
trying to bring in two personal data providers from an Excel worksheet. I have no problems connecting or bringing back data, but only 16,383 records come back in each case. The sheets are of varying length (18,000 and
21,000
rows respectively), so it is truncating the data at the 16,383’rd record. Is there a setting to modify the row limit for personal data files, as
there
is for universes on Designer? Thanks in advance!!

BusObj 4.x only supports Excel 95 file formats. That file format is limited to, you guessed it, approximately 16K rows. I presume that you are creating the files in a later version of Excel, and doing a “Save As” to put them in the older file format for BusObj to read. Open the saved file in Excel and see how many rows there are; I bet you will find the same number.

Upgrading to BusObj 5 will allow you to read Excel 97 file formats.

Or better yet, save your current Excel file as a CSV file (text format). It will not suffer the same limitation on the row limit, and it will be faster to export and import as well.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

3

Dave said…

Or better yet, save your current Excel file as a CSV file (text format). It will not suffer the same limitation on the row limit, and it will be faster to export and import as well.

Me…

We haven’t mentioned this in a while :slight_smile: At least twice a month I hear the following ‘Cindy, I’m exporting my data to an excel file and it’s just sitting there.’.

My answer… export to text format. I don’t know what in the world is wrong with export to .xls but avoid it! It’s so very slow and .txt is virtually instantaneous (depending on the number of rows to be exported, of course :-)!

Cindy Clayton

Ask WHY until you understand!

Overlooked blessings…
Laughing so hard your sides hurt and you can’t catch your breath… Daydreams…
The feel of a child in your arms…

Listserv Archives (BOB member since 2002-06-25)

4

Me:
The only problem with CSV format is that one of my fields contains a name, which sometimes contains a comma. Also, I have a grand total of nine separate sheets within the one file, so is splitting them out to separate text files the only way to work around the 16K size limitation?

Thanks,
Steve

Dave said…

Or better yet, save your current Excel file as a CSV file (text format). It will not suffer the same limitation on the row limit, and it will be faster to export and import as well.

Cindy said…

My answer… export to text format. I don’t know what in the world is wrong
with export to .xls but avoid it! It’s so very slow and .txt is virtually instantaneous (depending on the number of rows to be exported, of course :-)!

Listserv Archives (BOB member since 2002-06-25)

5

The CSV allows for ebmedded commas. You will find that the “name” field is surrounded by quotes. So, if you have data that looks like:

Name, Date, Value

The CSV file will look like:

“Rathbun, Dave”, 03/08/2000, 2945.32

I don’t remember if dates have quotes around them as well. Try exporting your data and see what you get, but I believe that you will not have a problem with the commas. Now if you had both commas and quotes in your data… but that is highly unusual.

As far as the nine sheets: you have to import them separately in BusObj anyway, so it’s not really adding an extra step. You have to build a different data provider for each Excel sheet within the workbook, or you can build a data provider for each text file. Either way you have nine data providers for your document.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

In a message dated Thu, 8 Mar 2001 9:41:49 AM Eastern Standard Time, “Large, Steven” steven.large@MAIL.SPRINT.COM writes:

<< Me:
The only problem with CSV format is that one of my fields contains a name, which sometimes contains a comma. Also, I have a grand total of nine separate sheets within the one file, so is splitting them out to separate text files the only way to work around the 16K size limitation?

Thanks,
Steve

Dave said…

Or better yet, save your current Excel file as a CSV file (text format). It will not suffer the same limitation on the row limit, and it will be faster to export and import as well.

Cindy said…

My answer… export to text format. I don’t know what in the world is wrong with export to .xls but avoid it! It’s so very slow and .txt is virtually instantaneous (depending on the number of rows to be exported, of course :-)!

Listserv Archives (BOB member since 2002-06-25)

6

Steven said…
The only problem with CSV format is that one of my fields contains a name, which sometimes contains a comma. Also, I have a grand total of nine separate sheets within the one file, so is splitting them out to separate text files the only way to work around the 16K size limitation?

Me… Did you try making a data dump report within the doc and then doing a file/save as .txt? I can’t remember how the Excel import wizard treats the commas in that. Also, could you remove the commas from the data using instr() and substr() if you’re Oracle. We’ve talked a lot about that lately :slight_smile:

Hope this helps! Let us know!

Cindy Clayton

Ask WHY until you understand!

Overlooked blessings…
Laughing so hard your sides hurt and you can’t catch your breath… Daydreams…
The feel of a child in your arms…

Listserv Archives (BOB member since 2002-06-25)

7

Me:
The .csv dropped some names on export for some reason, so I went to straight text, tab-delimited, and that did the trick. I did have to enclose the phone numbers in quotes so that BO would recognize it as a string instead of a calculation (don’t understand that one yet…). Thanks once again for all of your help!!

Cindy:
Did you try making a data dump report within the doc and then doing a file/save as .txt?

Dave:
The CSV allows for ebmedded commas. You will find that the “name” field is surrounded by quotes. So, if you have data that looks like: Name, Date, Value
The CSV file will look like: “Rathbun, Dave”, 03/08/2000, 2945.32

I don’t remember if dates have quotes around them as well. Try exporting your data and see what you get, but I believe that you will not have a problem with the commas. Now if you had both commas and quotes in your data… but that is highly unusual.

Listserv Archives (BOB member since 2002-06-25)