[Solved] Can Files Be Generated From Calc List?

[kbellis]

Is it possible using OpenOffice to accomplish the following: create a new text file; given a simple format of text and variables, and a table of values which includes the desired file name, from each record in the list?

Thanks for any ideas on how I might approach accomplishing this using OpenOffice.

[MrProgrammer]

Is it possible using OpenOffice to accomplish the following: create a new text file; given a simple format of text and variables, and a table of values which includes the desired file name, from each record in the list?

Sure, in a new sheet use cells which create a script for your operating system which writes the desired data to the specified files. Then export the sheet as CSV, which is a text file format. Or copy those cells to a Writer document and save that as a text file. Using your operating system, run the generated script. For example for Mac or Linux a script might be:

cat >TestA.txt <<EOF
OneA,TwoA
ThreeA,FourA
EOF
cat >TestB.txt <<EOF
OneB,TwoB
ThreeB,FourB
FiveB,SixB
EOF

The cells with values TestA,txt, OneA, TWOA, …, TestB.txt, OneB, TwoB, … would be used in formulas in the sheet which creates the script. The first line might use ="cat >"&Data.A1&" <<EOF". The second: =Data.A2&","&Data.B2. Another approach for the generated script would be:

echo >>TestA.txt "OneA,TwoA"
echo >>TestA.txt "ThreeA,FourA"
echo >>TestB.txt "OneB,TwoB"
echo >>TestB.txt "ThreeB,FourB"
echo >>TestB.txt "FiveB,SixB"

There would be similar scripts for Windoze, though I no longer do any development for that. I note that you do not tell us your operating system in your forum signature.

You could also write all your original data to CSV files, then use Perl or another programming language to create your text files. Or you can use the macro facilities of OpenOffice to write data to files, however the Application Programming Interface (which you'll need to access cell contents) is dreadful. That is my least recommended way to accomplish your goal.

given a simple format of text and variables

To get further assistance you should attach a document, not a picture, demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply). Indicate the exact format you need for your files by way of examples which match your attachment. Depending on your sheet layout and desired text file format you may want to use [Tutorial] Rearrange rectangular data values or restructure data with Copy/Paste.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[kbellis]

RE: Windows 10 64-bit

gcpTemplate.gcp

Code: Select all

0,0,%GC1x%,%GC1y%,"GC1"
4000,0,%GC2x%,%GC2y%,"GC2"
4000,3000,%GC3x%,%GC3y%,"GC3"
0,3000,%GC4x%,%GC4y%,"GC4"
Method=Affine

(desired output example) DJI_0006NoMeta.gcp

Code: Select all

0,0,318778.91,228526.80,"GC1"
4000,0,318841.39,228412.19,"GC2"
4000,3000,318755.89,228365.58,"GC3"
0,3000,318693.41,228480.19,"GC4"
Method=Affine

Thank you Mr. Programmer for your reply.

I've attached the Calc-produced csv file of the list of records. The topmost gcpTemplate.gcp shows the desired format with the relevant fields designated as variables;

Code: Select all

%GC1x%

,

Code: Select all

%GC1y%

, etc., and the other fixed bits;

Code: Select all

0,0,

Code: Select all

"GC1"

, etc.

In the desired output example, shows the first record created into the file DJI_0006NoMeta.gcp

As noted, Windows 10 is the operating system, but I'm unfamiliar with script writing for Windows.

If (!?) I'm understanding correctly, which I very much doubt, in Calc, I created Sheet2 using the your outlined method and saved as csv (not exported, as exports are limited to PDF); i.e., [gcpListME2kWest - Sheet2.csv] - But then, still following your outline, it sounds that Calc's use ends, and another program, or external programming will be necessary.

I do not know Perl.

Sorry I didn't mention my OS at the top of the thread, which again, is Windows 10 64-bit. I note that you also do not tell us what you are using for an operating system in your forum signature, but by your remarks I gather it isn't Windows. Maybe someday, the programs that I need will run on Linux, but for now I use Windows.

The link to Andrew Pitonyak's page on OOMacros will take my remaining days on this planet to explore, though I'm sure will be worth the time and effort.

[Villeroy]

Save the csv file(s) in a dedicated directory.
File>New>Database...
[X] Connect to existing database
Type: Text
Specify the directory and the import details as you did in Calc
[X] Register the database
Save the database
Close and forget the database
###########################
Nothing has been converted, imported nor copied. Your data are still in the text file(s).
A database document is just a "bridge" to get tabular data from various sources into office documents.
If you have access to the database which exported your csv, then you may connect directly to that database and use it without any csv file.
###########################
In Writer:
menu:Insert>Fields>Other>Database... browse the registered database name, the table, the column
alterntatively: open the data source window (F4), browse the registered database name, the table and drag the column header into the Writer document.
When you are going to print the Writer document, the program will detect the placeholder fields. Follow the instructions.

[robleyd]

I note that you also do not tell us what you are using for an operating system in your forum signature

You may have overlooked the information in MrProgrammer's signature where the operating system is given as MacOS:

To help us better help you, please consider changing your signature to reflect the OS and Office suite type and versions you are using - How to update your signature

[kbellis]

Thanks Robleyd. My apologies to Mr. Programmer.

[kbellis]

@Villeroy - Perfect! Straight forward and exactly what was needed, barring the last bits of the extension (txt) which is small work using Rename-It!

Thank you so very much for your help. I had actually thought of doing something like a mail merge as I've written letters to a group of people, address labels and that sort of thing, but wasn't sure how to make it work in this situation. And the last bits in the process where the file name in the database was utilized cinched it!