locked
How to refresh/append data RRS feed

  • Question

  • Hello. Does anyone know where I can find resources on how to create pivot data models based off of csv or excel files and then have the data update as each new month/period is added to a folder? For example, I might have sales information in a file that comes monthly. For the new data, it adds a column. I would like to have this refreshed monthly. How do I do? 
    Thursday, November 17, 2016 10:53 PM

Answers

  • Hi Alex,

    For one thing, you can import data from one CSV file and append to an existing sheet using using VBA script as below, then add into data model.

    Sub Append_CSV_File()
        
        Dim csvFileName As Variant
        Dim destCell As Range
        
        Set destCell = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)      'CHANGE SHEET NAME
        
        csvFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
        If csvFileName = False Then Exit Sub
        
        With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=destCell)
            .TextFileStartRow = 2
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        
        destCell.Parent.QueryTables(1).Delete
    
    End Sub

    For another thing, you can append or refresh this information to the (same) data using SQL Server Integration Services (SSIS) which is a platform for data integration and workflow applications is appropriate. Please create  a Package by using SSIS application, then create SQL Server Agent job to run this package periodically. In the SSIS flat file destination, uncheck the "Overwrite data in file" checkbox.

    Finally, if you have published your workbook to a PowerPivot Gallery or Sharepoint site that supports PowerPivot, you or the Sharepoint administrator can create a schedule for automatically updating the data in the workbook. More details, please review the article.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 21, 2016 3:28 AM