0

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;
Community
  • 1
  • 1
BenTreeser
  • 93
  • 7

1 Answers1

0

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.

sabbahillel
  • 4,357
  • 1
  • 19
  • 36
  • It's not quite what I want. I want an excel file to automaticly import data from the csv file with the same name in the same folder. I do not want to exclusivly and manually edit the connection file in my excel file. I edited a little bit the description in my first post. I hope it helps you to understand my issue. – BenTreeser Feb 19 '14 at 13:36
  • Have you seen the question and answer at [this link](http://stackoverflow.com/questions/7876178/importing-excel-spreadsheet-data-into-another-excel-spreadsheet-containing-vba) which may be more what you are asking about. – sabbahillel Feb 19 '14 at 18:14