Announcement

Collapse
No announcement yet.

Update Multiple Sheets From CSV File

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • LonghornShane
    started a topic Update Multiple Sheets From CSV File

    Update Multiple Sheets From CSV File

    I have a fairly large Excel Workbook with 76 tabs. The workbook works just fine, but I would like to automate the manual process a little further.

    In the first tab I have a list. The second tab there is a template. I have written code to build the template in a new tab based on the original list.

    Each week I will have data "dumped" from another program into a .csv file that will need to be added to most all of the tabs. I would like to automate this process as much as possible. If somebody would like to take this on.. please let me know. Also, there will be some more work to do on seperating data out.

    Thanks,
    LonghornShane

    Tried numerous times to post the file... Tried zipping it up and still no luck...

    Please send message if interested.

  • Ger Plante
    replied
    Re: Update Multiple Sheets From CSV File

    Sorry for not replying before now Longhorn...

    So to put this in simpler terms, update the early and late columns in the worksheets for each of the given date periods, with data from the CSV file.

    Dont worry about the "CUM" line in the CSV file. If it's an "evaluated" figure, it can be ignored.

    I have some additional questions -
    When I open the CSV file there is a list of dates with numbers underneath for each relevant sheet - however do these numbers represent the early or late columns? If you know what I mean. I would expect two numbers per dated period in the CSV file (one for early and one for late). Or is the whole CSV file representing an "Early" (or "Late") period.

    As you mentioned above, some worksheets are an accumulation of data from "sub" sheets underneath it. However, your CSV file contains RAW data (i.e. numbers) for those sheets on those dates... but those sheets on those dates are already prepopulated with formulas. What is someone supposed to do in that instance? e.g Sheet TP_01.06 contains dates in the worksheet, but also contains RAW data in the CSV file...

    Basically the key thing here for me is to understand how the CSV file should be mapped to each sheet.

    Sorry if I missed something obvious.
    Ger

    Leave a comment:


  • LonghornShane
    replied
    Re: Update Multiple Sheets From CSV File

    I will try and answer these questions as best as possible.

    The first thing that needs addressed is what a WBS is and how it works. (very short summary)

    Sample WBS
    1
    1.1
    1.1.1
    1.2
    1.2.1
    1.2.2

    The workhours in WBS "element" 1.1 will be a total of 1.1

    1.2 will be a total of 1.2.1 and 1.2.2

    So 1.1 AND 1.2 will be totaled into the top tier of "1".

    Formula: 1=(1+2); 1.1=(1.1.1); 1.2=(1.2.1+1.2.2)

    **********************************************************

    The values of each WBS element will transfer into the "LATE PLAN" and "EARLY PLAN" in each corresponding tab. The dates START IN CELL B132, the "EARLY PLAN" STARTS IN CELL C132 and the "LATE PLAN" STARTS IN CELL E132.
    **********************************************************

    The CUM is simply a total of the WBS elements as you are progressing through the weeks / months / years.. etc. I can take that out of the CSV file if it would make things easier...
    **********************************************************
    Also, will the name in the CSV file always follow the format of "6-TP MAST.99.99.99", which transforms into a worksheet name of "TP_99.99.99"
    YES!

    The 6-TP MAST.99.99.99 will ALWAYS follow that format and transform into the tabs.

    **********************************************************

    Will the worksheet name in the CSV file always exist as a worksheet in the workbook? (i.e. is it guaranteed that a sheet will always exist for the data in the CSV file....
    It doesn't have to be, but it could if necessary. One thing to point out.. These CSV files will be "dumped" from a program on a weekly basis. And essentially that is why I am attempting to automate this as efficiently as possible.
    ***********************************************************

    Finally will the 7 day periods in the CSV file always exist in the given worksheet (or at least can you guarantee that all the periods start on the same [7 day] date).
    YES! These week ending periods will always stay the same.

    Feel free to send me a message and I am more than willing to talk about this on the phone or via email if it makes things easier. Of course the amount and payment method etc. will be worked through this website.

    Leave a comment:


  • Dave Hawley
    replied
    Re: Update Multiple Sheets From CSV File

    I hope this clears up any misunderstandings.
    Sure does, thanks.

    Leave a comment:


  • Ger Plante
    replied
    Re: Update Multiple Sheets From CSV File

    Hi Longhorn... I have made the connection between the CSV file and the tab names, but thats about as far as I got. the CSV file contains 7 day week periods along the top, but its not clear to me where/how the numbers underneath for each sheet should be entered in the given worksheet name, or more specifically where they should be entered into the given worksheet name...

    Also, there is "CUM" line in the CSV file, which is borderline rude, but that aside, I cant figure out how the CUM line is calculated / derived, or if it is relevant to the given worksheet name, or relevant at all to this problem.

    Also, will the name in the CSV file always follow the format of "6-TP MAST.99.99.99", which transforms into a worksheet name of TP_99.99.99"

    Will the worksheet name in the CSV file always exist as a worksheet in the workbook? (i.e. is it guaranteed that a sheet will always exist for the data in the CSV file....

    Finally will the 7 day periods in the CSV file always exist in the given worksheet (or at least can you guarantee that all the periods start on the same [7 day] date).

    I'm honestly not sure yet if I would have time to take it on (I'm technically on holidays), but in case any other Excel people are watching, they will need the above information too.

    Cheers for now,
    Ger

    Leave a comment:


  • LonghornShane
    replied
    Re: Update Multiple Sheets From CSV File

    Files are at the following...

    http://www.savefile.com/projects/808692329

    Leave a comment:


  • LonghornShane
    replied
    Re: Update Multiple Sheets From CSV File

    Thanks for the input and replies.

    I will try and go to savefile.com and get it posted there.

    Dave,

    It truly depends on how many "bells and whistles" are going to be added to this file. I understand a stated amount needs to be added to the post, but until somebody tells me what they will attempt to do than it is somewhat hard to estimate the "job".

    It could be anywhere from $50-$500 dollars.. Again... Depending on what type of work will be done.

    I hope this clears up any misunderstandings. I am truly trying to abide by the rules set forth by this wonderful website.

    Thanks,

    Longhorn

    Leave a comment:


  • Dave Hawley
    replied
    Re: 74 Tabs that contain Charts and data that will accumulate (WBS)

    LonghornShane, you MUST state an amount you are willing to pay else this thread will be deleted.

    Leave a comment:


  • Ger Plante
    replied
    Re: 74 Tabs that contain Charts and data that will accumulate (WBS)

    Hi Longhorn, sounds like the file may have been a bit too big.

    Maybe try posting on a hosted site like save file. http://www.savefile.com

    If no success there, post back and I'll PM you email address and I'll take a look a closer look at the job / requirements. Hard to say yet how much work is involved without seeing the file and a sample CSV file.

    By the way, I'm assuming the job (requirements) is basically take a csv file, read it and update some/all of the 76 sheets. In which case, what is the criteria for updating each sheet (i.e. how do I/code know which sheet to update from the data in the CSV file).

    Cheers,
    Ger

    Leave a comment:


  • LonghornShane
    replied
    Re: 74 Tabs that contain Charts and data that will accumulate (WBS)

    Willing to pay a reasonable amount....

    Leave a comment:

Working...
X