Execute Report saves copy & copies files

[tdsmart]

Hi all,

I know there's many ways of making Office interface with the operating system, such as shell(/path/to/script), but I'm wondering if Basic can copy files from a list generated in a report?

To clarify; I have a database storing information pertaining to a long list of documents and one of these fields will be a text string containing a file location. Say I generate a Report, the end result I want to achieve is a new folder created which contains a PDF copy of the report listing all files, and a subfolder containing copies of the files listed in the report.

How do you all think the cleanest way of looking at that would be? Basic? Shell to Python script (the only programming language I'm really comfortable with right now)?

[tdsmart]

Chugging along I found some useful material (mostly on this forum - you could double the size of the Base handbook with this forum) and here's an outline of the plan of attack for this job:

1 - I have a table with a list of all documents.

2 - A Query is set up to select all documents in a subset determined by user input on a parameter when the query is run (any way to make the user input into a listbox?).

3 - I'm working on a pretty report listing all relevant data for the filtered documents.

4 - Now a tricky part: How to export only one column - 'filepath/filename' - to a csv list and not show this ugly list on my report?

5 - Jumping the gun I'm aiming to execute a shell() command to link to a windows .BAT file to read the text list & copy files. Windows, I know... sad but company policy etc etc. Can be done in one line:
http://www.sidesofmarch.com/index.php/a ... text-file/

Any thoughts would be appreciated.

[tdsmart]

Does anyone have any insight onto why Libreoffice 4.1.3.2 fails to connect to a database after executing an export to csv command in direct SQL? That's how I'm tackling point # 4 above, with this SQL command:
http://techierambles.blogspot.com.au/20 ... -base.html

The command works as described, however on exiting Libreoffice Base & reopening the .odb file, if I click on the Tables tab of the Database Pane I get the error message that it canot connect to the database;

SQL Status: S1000
Error code: -78

error in script file line: 10 column size is required in statement [CREATE TEXT TABLE "TESTOUT1.CSV"("CRT_ID" VARCHAR(100),"ITM_SAP" INTEGER,"ITM_SER" VARCHAR(100),"ITM_ID" INTEGER,"FILEPATH" VARCHAR]

Do I need to explicitly break the connection to the csv file somehow? I will be running a script to delete these single-use csv files, so I'm concerned that the error message recalls my "SELECT X INTO TEXT..." command and tries to execute an "CREATE TEXT TABLE ..." command. Further, I cannot directly enter SQL commands in that version of the .odb file anymore, getting the same error on trying to open the SQL command window.

...Can someone explain this behavior?

[DACM]

Take a look at your SQL table structure because it appears to be a conflict between driver capabilities. Evidently, the CSV driver requires an explicit VARCHAR length.

"FILEPATH" VARCHAR

should read:

"FILEPATH" VARCHAR(100)

[tdsmart]

That makes sense - the SQL generating the error is trying to create a table from the command I used to export to CSV, however a catch is that the exported data is coming from a calculated field in a View (currently, although I want to export from a parameter Query this gives me a completely different error). How do you specify the VARCHAR length in the GUI editor of a Query / View?

[DACM]

How do you specify the VARCHAR length in the GUI editor of a Query / View?

Use the CAST function.
...

[tdsmart]

Thanks DACM, that link was very educational.