I have an excel template called an estimate to complete (ETC) template, where consultants fill in the number of hours per month they believe it will take to complete an engagement. We have a batch scheduler that populates about 100 ETC templates per MD to fill out and they are posted to SharePoint. Once all of the files have been completed by the MDs and saved to SharePoint, we want to import the data into JDM. What is the best way/resource to take all of the excel files and get the hours by month data in each template into a single data source in JDM?
Date
Votes
4 comments
-
Harry Lewis Hi Morgan -
The easiest method would be to convert the Excel files to CSV files and import those into SQL. Then, in the JDM, use the SQL data source connector to bring the data into the project.
-
Morgan Healey Is there a way to save all 100 CSV files into one CSV file so I could use one total CSV file as the data source?
-
Harry Lewis Hello Morgan -
One could possibly create an Excel VBA macro that combines all the files into a single .CSV. Alternately, if your .CSV files do *not* have a row with header information, the Comman Prompt COPY command can be used to combine .CSV files:
e.g.,
C:\> COPY wb1.csv+wb2.csv+wb3.csv+wb4.csv+wb5.csv MyFinalFile.csv
If the individual .CSV files *do* have headers, you could always sort the ultimate file and manually remove the header rows.
-
David Zebrowitz If you're able to export all the csv's into a single folder and they all have the same columns, you can use the "Multiple Text File" datasource. There would be no need to merge the csv files.
Please sign in to leave a comment.