Best extensions for my word/excel based needs.

[njt]

Hello, I joined a long long time ago. Life happened and I'm back 8 years later :p.

I would like to say I've mastered livecode by now, but unfortunately I'm no better now than I was then.
My story aside - I want to create the following in livecode.

I can import an excel/word document. (Either 1 or multiple.)
It displays the info within livecode.
I can then filter/sort/modify the content within what is displayed.
-As in, I can have an option that will either sort for cells that have certain characters, Select a range of cells/columns, or I can mass delete/replace certain characters/words.(If I could filter by color of the cells, that would be awesome too.)
-The image I have for this is, there's a window in which I can insert characters that I want to either single out or replace.
-The types of cases I can think of are special characters like { [ * % & , . ) - and even words between brackets like [] in which those cases would need to be deleted/replaced with something else.
After sorting/filtering/modifying the content I can then export it to a text document.

You may wonder why I would need such a tool - well, it's for giving clients a quote for translation related work. A lot of the files we work with are excel / word based. But not everything within the document is required for translation. Thus using the above tool - we can single out only what we plan on translating and give the client a word/character count from Word.

I've seen multiple extensions that could work with microsoft files - but most seem to be released quite a while ago, and well, I'm not sure which (if any) would allow me to do the above.

Also, if a kind soul out there wants to give a quote on making the above - I could possibly pay for it and then check out how it was done and learn form that :p.

Any advice would be appreciated .
If further info / examples are needed, let me know and I'll happily provide them.

[richmond62]

If I need to import a spreadsheet file into LiveCode I generally export it from the program it has been
made in as a comma-delimited text file, then it is dead easy to import into a table field.
-

Screenshot 2021-02-14 at 13.14.36.png (36.9 KiB) Viewed 6145 times

-
LibreOffice: your mileage may vary.

[richmond62]

viewtopic.php?t=12056

Although the download reffed there is not available.

BUT, you can download it here: https://web.archive.org/web/20151108120 ... .com/rgrid

[dunbarx]

Richmond.

Import an Excel file as csv, eh?

Isn't the "standard" format for an Excel workbook tab and return delimited, so that the data will fall right into a LC table field without issue? Importing as csv required replacing each comma with a tab. Simple to do, but why bother?

Craig

[richmond62]

Isn't the "standard" format for an Excel workbook tab and return delimited

Quite possibly: never bothered to find out.

[jacque]

I'd make a list in a field with one replacement per line. Each line would have the undesirable character or phrase, a tab, and the desired replacement. If you'd rather delete than replace, leave the replacement blank.

In the script, repeat for each line and use the replace command:

Code: Select all

set the itemDelimiter to tab 
put fld "fulltext" into tText -- the imported text
put fld "replacements" into tReps -- the replacement list
repeat for each line L in tReps
   replace item 1 of L with item 2 of L in tText
end repeat
put tText into field "fulltext"  

If you need to replace more generic things, like any string inside square brackets, look at replaceText in the dictionary. That function lets you use regex, which we can help you with if you need it.

[FourthWorld]

Isn't the "standard" format for an Excel workbook tab and return delimited, so that the data will fall right into a LC table field without issue? Importing as csv required replacing each comma with a tab. Simple to do, but why bother?

Exporting as tab-delimited is much easier to deal with. A simple replace from comma-delimited may do the trick...or not:
http://www.fourthworld.com/embassy/arti ... t-die.html

[SparkOut]

Excel's "File>Saveas..." menu gives a whole shedload of options to save any given file in a whole shedload of file formats. Csv is one choice, typical and obvious (unfortunately the one most familiar to casual users) with .tab being another choice. I suspect it is a combination of the user's experience level, and (most likely) the target system's designated import specification that determines the choice made.

[njt]

Thanks for all the replies so far. To help give you all an idea of what I'm looking for, I made a preliminary design on what I'm imagining.



This currently is more focused on excel sheets, but ideally it'd work just the same with word files (Although worksheet list and below would be irrelevant).

Knowing what you see now - is what I'm looking for even possible?

To go into a bit more detail:

File list will allow me to select one or multiple files.
Worksheet list would allow me to select one or more sheets.
Columns would be the columns in which data is pulled.
Cells, the same.
Option to ignore the top row or not.

Filter bar at the top of the text display filed. Can either use it to include or exclude said character(s).
And a find / replace option at the bottom.

It's a bit complicated, but again - would be amazing to see working.

I'll take a stab at it - but again, if there are plugins I should consider for this, let me know .

[SparkOut]

There are a couple of Excel importers (more than importers, full control of xls and xlsx files) available. One is dual licensed, iirc. Not sure how current they are but testing a while back I know they worked flawlessly with Office 2016.
The other is a sister product to a word library.

https://www.aslugontheroad.com/ourprodu ... r-livecode

http://livecodeaddons.com/

[TerryL]

Hi njt. Maybe this stack will give you some ideas. It imports a text.csv spread and exports a text.txt, filters and sorts lines, and replaces items. Includes undo using a custom property.

Colors won't transfer, but adding a flag column in the original spread, like "red" or "alert" or "!", could be identified as an item in the stack for an action. Plenty of room to create your own buttons, like collect data from a "column" (hint: use a repeat loop to test an item in a line).

Note in LibreOffice the text.csv default delimeter is comma but you can change to tab, or in LC for a table field: replace comma with tab in tText. Have fun. Terry

edit: added header line button.

[njt]

Thanks for the resource suggestions - as well as sample file.

@Terry, your site is pretty informative too!

I've been going through the various tutorials on the livecode site as well as plan on trying out your tutorials on your site Terry and see if I can make this my self ^^.

Again, if anyone is looking to make some extra cash and wouldn't mind helping - I'd be up for paying for your services. (Probably should post this in the other forum asking for people to help out) but I guess I'll try here again first, and see what happens. )

Thanks all ~