Concatenation of a formula

[Bodach]

I am trying to concatenate an "import" formula so that data from several csv files can be correlated in one master file. I derived the formula by manually copying data from one sheet to the other. I then replaced the specific file reference with a cell reference, A1. Cell A1 in the master sheet contains the name of the required file.

The formula I'm using is ;

=CONCATENATE("='file:///home/rob/CSV Files/IG.", A1, ".csv'#$'IG.", A1, "'").F3

But when I run this a 501 error is returned. I'm hoping someone can point out my mistake.

Thanks for your help!

[robleyd]

The same question at AskLibreOffice with more information.

[MrProgrammer]

=CONCATENATE("='file:///home/rob/CSV Files/IG.", A1, ".csv'#$'IG.", A1, "'").F3

You have the CONCATENATE function followed by a period. Period is not a valid Calc operator so you get an error.

If you're trying to construct a reference using formulas, you'll undoubtedly want to use the INDIRECT function. Essentially your formula is ="some text".F3 and you probably want =INDIRECT("some text"&".F3"). However it seems as if you are attempting to reference a cell in a CSV file. CSV files do not have cells, nor do they have sheets. They are text files, not spreadsheets.

Edit: Removed incorrect information about external references to CSV files

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.

[Bodach]

Thanks for your response. I had not considered that CSV files were text files, something I may try to make use of in the future, since I open in a spreadsheet, hence converting them into a Calc document. Consequentially, I am working with two spreadsheets at this stage. To copy data from one spreadsheet into another requires the entry of a text string in the relevant cell, at least, this what I thought was required. I am using concatenation to try to build an appropriate string, but am getting the 501 error. I have still to research "Indirect", but remain puzzled as to why the simple, concatenated string is giving me the error code.

[Zizi64]

Thanks for your response. I had not considered that CSV files were text files, something I may try to make use of in the future, since I open in a spreadsheet, hence converting them into a Calc document. Consequentially, I am working with two spreadsheets at this stage. To copy data from one spreadsheet into another requires the entry of a text string in the relevant cell, at least, this what I thought was required. I am using concatenation to try to build an appropriate string, but am getting the 501 error. I have still to research "Indirect", but remain puzzled as to why the simple, concatenated string is giving me the error code.

Please upload a sample *txt file, a sample *ods file - and the macro code here.

[Bodach]

Thanks for the offer of help. However, the original files contain a lot of personal information, so I don't want to post them publicly and it would be a considerable effort to create dummy files.

The sequence of events is that I receive blocks of data as a csv file. To access this I open it in a spreadsheet which gives me a table of the data. I can copy and paste this into a master spreadsheet in its entirety. The mastersheet, in turn, manipulates the data to give me the information I require. However, doing this results in a large number of date stamps and references being copied and subsequently stored; this information having zero value in the final document. As an alternative I can copy individual columns from the csv table to the mastersheet, but this is labour intensive and slow. I can copy single cells from the csv table using the sequence of selecting a cell in the master, entering "=", highlighting a cell in the csv table and hitting "enter". This enters a text string in the master sheet cell and the data for that cell is copied.

What I am trying to do, and failing, is to recreate that text string, using concatenation which contains a reference to a cell in the master sheet, say A1. Mastersheet.A1 would contain an identifier for the csv table which when concatenated with the static information would allow the copying of only the required data to the mastersheet in a single operation, that is by changing the identifier in the reference cell.

I hope my explanation is sufficiently clear to allow you to understand the problem. To me, generating the text string seems a simple process, yet I always have a 501 error returned, even if I use the INDIRECT() function suggested by others.

If you can guide me through my confusion I would be very grateful!

[Zizi64]

the original files contain a lot of personal information

Preapere a sample with dummy data, but with same structure like the original one.

I hope my explanation is sufficiently clear to allow you to understand the problem.

The sample files are the best explanations.

[Villeroy]

Open the file in Writer.
Call Edit>Find&Replace and check the "Regular expression" option.
Replace all [:alpha:] with X
This will replace all letters with X keeping. The remaining figures are meaningless.
Save the file under another name and upload to this forum.

[robleyd]

Is there any reason why you don't choose to not import the unnecessary columns during the import process? In the Field section of the import dialog you'll see a depiction of how the file will look on import. Simply click on the header of the columns you don't want to import and select Hide from the Column Type dropdown that is activated when a column is selected. You can select multiple non-contiguous columns with Ctrl + Click

[Bodach]

Thank you - another new procedure which I did not know was possible! I will explore it further.

However, this doesn't quite achieve the automation I was looking for. Each file would have to be treated manually before being imported. I had hoped to automate the importation as much as possible.

I will work up a sample files and post them as soon as possible.

Thanks to all for their help and for the new techniques I am learning!

[Bodach]

I have now created simplified files which represent my situation. I would be grateful if you could look at them and advise on how I might achieve my objective. Thanks!

dummy1.17.csv is a very simplified csv file as it displays in Calc.

dummy1.17.csv

(219 Bytes) Downloaded 258 times

A simplified master sheet was also created, master.ods, which is attached blank.

master.ods

(8.45 KiB) Downloaded 109 times

The desired output is shown in desired.ods which shows the data copied manually using the technique of:

enter "=" | highlight the appropriate cell on dummy1.17.csv | press "enter". It is this technique I would like to operate automatically, populating the master with different data each time the reference in C3 is altered. The text string in cell B9 of desired.ods is:

='file:///home/rob/path/dummy1.17'#$dummy1.F2

I had hoped that the formula:

CONCATENATE("='file:///home/rob/path/", C3, ".17'#$", C3).F2

would achieve this automatically for all desired cells, but it fails with an error code 501. This also happens is the formula is wrapped in INDIRECT().

I trust my explanations are clear and any suggestions or solutions will be gratefully received!

[Bodach]

Clearly I haven't mastered the technique for attachments either.

Here is desired.ods

desired.ods

(10.36 KiB) Downloaded 131 times

Also, ignore reference to columnCopy - another file I created but which I have decided is not necessary.