Interesting & Creative way of loading SpreadSheet from PC

[bobkrohn]

Interesting & Creative way of loading SpreadSheet from PC

Since the Connectivity Kit can't load SpreadSheet Data from a PC...
I had to be creative.

I wrote a program on the PC that takes TAB or COMMA delimited ASCII data files,
and creates HP Basic Source Code.

1) You create an "empty" Program File on the HP Prime.
2) Connect it to the PC via the Connectivity Kit.
3) Open the "empty" Program residing on the HP Prime via the Connectivity Kit.
4) Open the "Source file" created by my PC program via any Text editor.
5) Simply Copy/Paste the "Source Code" in the "empty" Program.
6) Esc out on the HP Prime and Run the program.

Presto.... Spreadsheet loaded with 10,000 records!

Example
//---------------


Export Load()
Begin

// Start of "Source file" created by my PC program

Spreadsheet.Cell(2,1):= 10000.00000000;
Spreadsheet.Cell(2,2):= 10000.00000000;
Spreadsheet.Cell(2,3):= 101.00000000;
Spreadsheet.Cell(2,4):="SW";

Spreadsheet.Cell(3,1):= 11000.00000000;
Spreadsheet.Cell(3,2):= 10000.00000000;
Spreadsheet.Cell(3,3):= 102.00000000;
Spreadsheet.Cell(3,4):="NW";

Spreadsheet.Cell(4,1):= 11000.00000000;
Spreadsheet.Cell(4,2):= 11000.00000000;
Spreadsheet.Cell(4,3):= 103.00000000;
Spreadsheet.Cell(4,4):="NE";

Spreadsheet.Cell(5,1):= 10000.00000000;
Spreadsheet.Cell(5,2):= 11000.00000000;
Spreadsheet.Cell(5,3):= 104.00000000;
Spreadsheet.Cell(5,4):="SW";

Spreadsheet.Cell(6,1):= 10500.00000000;
Spreadsheet.Cell(6,2):= 10500.00000000;
Spreadsheet.Cell(6,3):= 105.00000000;
Spreadsheet.Cell(6,4):="CTR";

MSGBOX("Data Load Finished");

END;

//--------------


Way Cool "Out of the Box" so to speak.
Maybe even easier than the so called "normal" way.

Can't go the other direction just yet. (Prime => PC)
We are still limited to the defective 999 Record (out of 10,000) output from Connectivity Kit.

Any details on the file structure of the Prime Spreadsheet file is welcome.

[Han]

(02-06-2015 07:52 PM)bobkrohn Wrote:  Can't go the other direction just yet. (Prime => PC)
We are still limited to the defective 999 Record (out of 10,000) output from Connectivity Kit.

Can you get around that by making an export program that copies cells into a Note in the same way that one can create a program using Programs("nameofprogram"):= ... blah ... (see other threads on this)? Then, export the Note via the conn. kit.

Of course, I have not tested this theory. The idea is that the conn. kit should not have similar restrictions on a Note.

[rprosperi]

(02-06-2015 07:52 PM)bobkrohn Wrote:  Any details on the file structure of the Prime Spreadsheet file is welcome.

When the firmware upgrade came out last spring(?), there was a change in the internal storage format for the spreadsheet, and to help folks migrate, Cyrille posted some very specific byte-level details about the internals. No idea if thats enough for your needs, but you can find searching old posts here. Prob best to search using Cyrille's name.

hth

[bobkrohn]

(02-06-2015 07:57 PM)Han Wrote:  Can you get around that by making an export program that copies cells into a Note in the same way that one can create a program using Programs("nameofprogram"):= ... blah ... (see other threads on this)? Then, export the Note via the conn. kit.

Of course, I have not tested this theory. The idea is that the conn. kit should not have similar restrictions on a Note.

I have thought about this and saw some info on using the Note space.
Have only had the prime for about 6 weeks so still on the steep learning curve.
Also, wondered if the Terminal has any possibilities.
So far the program I'm working on in the Prime is 180k so that was where my work has been.

Thanks for the ideas.

[bobkrohn]

(02-06-2015 08:01 PM)rprosperi Wrote:  When the firmware upgrade came out last spring(?), there was a change in the internal storage format for the spreadsheet, and to help folks migrate, Cyrille posted some very specific byte-level details about the internals. No idea if thats enough for your needs, but you can find searching old posts here. Prob best to search using Cyrille's name.

hth

OK, thanks for the lead. Will look into this. I really don't have any expertise in binary files. I'm working with VB6. (not C) Maybe this will provide a stimulating VB learning experience?
SEEMS like the ZIP'ed SpreadSheet actually holds the 10,000 records though.

[bobkrohn]

A little more experimenting with this transfer technique.
Incrementing the amount of data transferred upward.

I was only able to get about 2000 records loaded.
That is 2000 Rows of 4 Columns. All numeric values.

The LOADing program shows a size of 628k on the Prime
It's 320k on PC.

I could be more space economical with better text formatting by the PC program.

The transfers were fast (< 5 secs) but was having a problem
with the Prime stalling with full constipation when trying to sync with Connectivity Kit.
Several pin-hole resets until I lost patience.

But that frequently happens even under normal conditionswhen

At one point the screen on the Prime got highly "pixilated" so I knocked off.

On the Prime loading the SpreadSheet via program also took only a few seconds.

the Terminal Screen only can hold 92 lines of text

I didn't see any info on programmatically read/write to the Notes App.

[Thomas_Sch]

(02-07-2015 04:27 AM)bobkrohn Wrote:  ..
I didn't see any info on programmatically read/write to the Notes App.

In the new firmware there is a command "Notes". See help.
[/php]

Code:

The Notes variable gives access to the notes saved in the calculator.
Notes returns a list of the names of all the notes in the calculator.
Notes(n) returns the content of the nth note in the calculator (1 to
NbNotes).
Notes(n):="string" sets the value of note n. If string is empty, erases the
note.
Notes("name") returns the content of the note called name.
Notes("name"):="string" sets the value of note "name". If string is empty,
erases the note. If there is no note called "name", creates it.

[bobkrohn]

Notes Info...
Thank You Very Much!

This may be Just the thing I need!!

[Thomas_Sch]

Did just a small test.
- Selected a range in a spreadsheet (A1:E15 in may example)
- copied the data
- created new note, paste, (value)
(there is an option paste, paste formula. but very cryptic)

Maybe this could be an option for interchanging spreadsheet data with excel.

[bobkrohn]

A problem with the Notes text.

I'm programatically writing text records to a Note.
Everything looks fine when viewed in the Prime.

But...

Using the Connectivity Kit and Copying the Note text
and Pasting to an ASCII text Editor on the PC
the imported text has extra breaks (lines) between Records.

Looking at the Hex I see that there are Double CRLF's
at the end of each Record.
It's not something I'm doing since the Records are written
only using the standard Prime Command:

Notes("MyNote"):=temp;

[bobkrohn]

After working on some other Sub Routines and several Restores
I looked at this thing again.
Now I'm seeing the extra blank lines on the Prime too.
Just randomly trying different things to just make it work.
Last change was adding just a LineFeed character to each written Record.
(ASCII 13)
Now, the data looks all jumbled as if one big Record on the Prime
But...
It looks normal in the Connectivity Kit.
Most important is that Copy/Paste into my Text Editor it looks OK.
And saved file loads into Excel.
That's all I really care about.

[Marcus von Cube]

(02-09-2015 07:04 AM)bobkrohn Wrote:  Last change was adding just a LineFeed character to each written Record.
(ASCII 13)
Now, the data looks all jumbled as if one big Record on the Prime
But...

13 is CR, LF is 10. So you may try the other value and see what the result is.

[YEDERF]

hello !!
who think this way transfer?

[bobkrohn]

Oh My!!!

Unfortunately I only speak English have no idea what is being said.
But it looks like a program that I would love to get!

I have PrimeComm v0.9 b22
Is that the same one?
It looks different and I'm not seeing a way to Copy/Paste Excel data.

Tell me more.
Is there an English language version?

[Han]

(02-10-2015 04:19 PM)bobkrohn Wrote:  Oh My!!!

Unfortunately I only speak English have no idea what is being said.
But it looks like a program that I would love to get!

I have PrimeComm v0.9 b22
Is that the same one?
It looks different and I'm not seeing a way to Copy/Paste Excel data.

Tell me more.
Is there an English language version?

The programs being used are Excel (which you know), the HP Connectivity Kit, and the HP Prime Virtual calculator. Other than Excel, the rest came with your calculator. When copying from Excel to the calculator, make sure that the dimensions match. Unlike Excel, where one could copy a large array of data and paste into a single cell and it would be smart enough to know to place the data in the proper cells, it seems the conn. kit requires that dimensions match. I haven't done it recently but in the paste, the conn. kit would crash (?) or complain if you didn't have properly matched dimensions.

I'm also not sure if this scales to large spreadsheets. In the end, if you are trying to transfer data to a real calculator, there still may be issues with transmission from conn. kit to the calc.

[bobkrohn]

Oh Shucks.
Have previously tried manually Copying and Pasting (in CK) with no luck
Even when I tried to match the areas it would not cooperate very well.
Besides that, it didn't Save the modified Spreadsheet.

Not user friendly.

I'm forging ahead with the idea of using Notes text as a transfer tool.

I have it working both directions pretty well as of today.

Also threw in List and Matrix export SubRoutines.

My use of the Spreadsheet is very basic. Just 3 Columns of Numeric Data and One Column of Text. Matrix would have worked but doesn't allow Text.

I desperately want to love the HP Prime.
It's what I dreamed about back in HP-41 days

Thanks for your thoughts

[Thomas_Sch]

Hello Bob,

YEDERFs and HANs suggestions work very well, if you pre-populate the Spreadsheet in Prime and the Excel-Spreadsheet the same way.

The basics, If you want to copy data from Excel to Prime:
- all cells in Prime should have a content ith the rigth type,
e.g. formula, number, or text as in Excel
- Cells in Prime should not be empty prior to copying
- Cells in Excel should be displayed as formula, like shown in the video
- an editor is used as temporary storage. Excel -> Editor -> Conn.Kit
- additionaly cells with text-type-content should be included in ""

@ YEDERF: please correct me, if this description is faulty.

If the corresponding cells match the type, copying will work.

You're right, it's not really user-friendly, but it's working without program!
HP should add this (or a better description) to the manuals.

If users have problems with this way, publishing your routines here would help.

[bobkrohn]

I just am really baffled now.

My HP Prime program to read a Notes file and populate the Spreadsheet with data works fine.
So I thought.
Then I tried loading a larger file.
This one is 300 Records approx 60 characters (with spaces) wide.
20k on the PC
but when pasted into Notes, shows as... 371k !!
(a ratio of 20:1. What's up with that?)
I can look at it on the HP in Notes App

Start the program to load SpreadSheet and all is well.
Very slow though, about 100 Records/minute.
Program completes normally.
Shortly, thereafter the HP Prime reboots itself.
The Spreadsheet is empty and the previously transferred Notes file is gone.

Another odd thing is this.
The Notes file had some pre-exisiting text from a smaller run.
I had highlighted that and deleted it prior to Pasting in the much larger text into it.

After the reboot the OLD file data was there! What?

After three tries (no changes to anything) I got the SpreadSheet data to persist. So there is hope.

[Thomas_Sch]

see http://www.hpmuseum.org/forum/thread-302...l#pid27169

[Han]

(02-12-2015 07:17 AM)bobkrohn Wrote:  Shortly, thereafter the HP Prime reboots itself.
The Spreadsheet is empty and the previously transferred Notes file is gone.

Another odd thing is this.
The Notes file had some pre-exisiting text from a smaller run.
I had highlighted that and deleted it prior to Pasting in the much larger text into it.

After the reboot the OLD file data was there! What?

After three tries (no changes to anything) I got the SpreadSheet data to persist. So there is hope.

Turning the calculator off forces a write to storage memory so that any changes since the last "OFF" gets saved.