How to excel connection file (<name>.csv) always same name as excel file?

Question

I create a lot of analyzing data in several csv files. All of them have the same column schema (see below). So I created a single excel file which validates the data of one single csv file Screenshot Files in Folder.

From time to time new csv files come in. My idea is to copy my preset excel file and give these excel files same names as their corresponding csv files. If I then open an excel file I want it to import the data from the csv file with the same name as the current excel file. Currently I have to manually edit the connection file in each excel file Screenshot edit connection file.

I hope you can understand my issue. I'm using Excel 2010

CSV Schema:

duration;mixedRatio;numberOfObjects;numberOfThreads;operationTimeInterval;singleObjectSize;type;InitialHeapSize;InitialTenuringThreshold;MaxHeapSize;MaxNewSize;MaxTenuringThreshold;NewSize;OldSize;SurvivorRatio;AVG_ALLOCATION_RATE;AVG_COLLECTION_AMMOUNT;AVG_COLLECTION_AMMOUNT_PER_MAJOR_GC;AVG_COLLECTION_PER_MINOR_GC;AVG_FREE_OLD_GENERATIOIN_AFTER_MAJOR_GC_PERCENT;AVG_FREE_OLD_GENERATION_BEFORE_MAJOR_GC_PERCENT;AVG_FREE_SURVIVOR_SPACE_PERCENT;AVG_MAJOR_GC_DURATION;AVG_MAJOR_GC_GROWTH;AVG_MAJOR_GC_GROWTH_PERCENT;AVG_MAJOR_GC_INTERVAL;AVG_MINOR_GC_DURATION;AVG_MINOR_GC_INTERVAL;AVG_PROMOTION_RATE;AVG_STOP_THE_WORLD_PER_EVENT;GC_EVENTS_NUMBER;MAJOR_GC_NUMBER;MAJOR_GC_TO_MINOR_GC;MAJOR_GC_TO_TOTAL_DURATION;MAX_STOP_THE_WORLD_TIME;MEASUREMENT_DURATION;MINOR_GC_NUMBER;TIME_SPENT_IN_MAJOR_GC;TIME_SPENT_IN_MINOR_GCS;TOTAL_ALLOCATION;TOTAL_STOP_THE_WORLD_TIME;TOTAL_TIME_IN_GCS;TOTAL_TIME_IN_GCS_PERCENT;
2;0;6500000;6;0;100;mixed;6000;15;6000;2000;15;2000;4000;8;1394898;1765219;3423573;1570114;0.62153023;0.2356941;0.0;0.82017523;0;0.0;4.6355;0.30462942;1.5123948;29803;0.51193845;52;13;0.47297874;0.17799278;1.3885045;59.90287;39;10.662278;11.8805485;83558400;26.620798;22.542828;0.37632298;
2;0;6500000;6;0;100;mixed;6000;15;6000;2250;15;2250;3750;8;1312573;1589237;3151672;1728000;0.5956703;0.31491926;0.0;0.8667001;0;0.0;4.108385;0.3367038;2.0222418;60074;0.6222538;44;14;0.5457093;0.20094614;1.4543015;60.383358;30;12.133802;10.101116;79257600;27.379166;22.234919;0.36822924;

Can we see some code or something? It's not very clear what you are trying to explain and some of your code my illustrate it better. — psubsee2003
I added the csv schema and two screenshots and edited the description. Hope it helps. — BenTreeser

sabbahillel sabbahillel · Accepted Answer · 2014-02-19T13:04:40

Since the comment showed that what I suggested was not what you wanted, I removed that suggestion. Perhaps you can use the following to build the name that you need.

http://office.microsoft.com/en-us/excel-help/inserting-the-current-excel-file-name-in-a-cell-HA001034627.aspx

Enter the following formula in the cell in which you want to display the current file name with its full path and the name of the current worksheet: =CELL("filename")

Insert the current file name only (for example, test.xls)

Enter the following formula to insert the name of the current file in a cell: =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Note If you use this formula on an unsaved worksheet, you receive the error #VALUE!. When you save the worksheet, the error is replaced by the filename.

If you then use this to edit out the .xls and replace it with .csv, this may enable you to process.

How to excel connection file (<name>.csv) always same name as excel file?

1 Answers