locked
Powerpivot Data Connection (Text file to SQL Server) RRS feed

  • Question

  • Hi,

    I have created a powerpivot data model with tons of measures & KPIs. Originally the source of my data models were .csv text files. To make auto refreshing work better, I migrated all the underlying tables & schema to a SQL Server DB (ensuring the schema remains exactly the same as the text files). Now I am trying to point my data model from the text file source to SQL db connection. I am not sure how to do that other than laboriously re-creating the data model manually. When I go to "existing connections" and edit a text connection, I dont see any option to change to SQL server connection.

    Please help!!


    Siva

    Tuesday, July 10, 2012 12:34 AM

Answers

  • Hi,

    You have to add another connection its obviously not existing. Also, just to be clear or so we are on the same page, you used an ODC file for all of your connection information and you did not store the logic embedded in Excel.

    1. You opened SSM
    2. Created a new DB
    3. Right Clicked the New DB and Chose Import Data > Flat File > Column Names in in First Row of Data
    4. Then previewed the result ? Click next and you didn't have any errors

    I have changed data sources in SQL and using an ODC. But I have never attempted to change a data source embed within Excel. You should be able to clean up the data source information doing a search and replace. But again I always use ODC files when working with Excel and Visio so that I can distribute them for other folks to use and make any changes necessary to the data source.

    -Ivan


    Ivan Sanders My LinkedIn , My Blog, @iasanders, BI in SP2013, SP2013 Content Packs.

    Thursday, April 4, 2013 10:20 AM

All replies

  • Hi,

    You have to add another connection its obviously not existing. Also, just to be clear or so we are on the same page, you used an ODC file for all of your connection information and you did not store the logic embedded in Excel.

    1. You opened SSM
    2. Created a new DB
    3. Right Clicked the New DB and Chose Import Data > Flat File > Column Names in in First Row of Data
    4. Then previewed the result ? Click next and you didn't have any errors

    I have changed data sources in SQL and using an ODC. But I have never attempted to change a data source embed within Excel. You should be able to clean up the data source information doing a search and replace. But again I always use ODC files when working with Excel and Visio so that I can distribute them for other folks to use and make any changes necessary to the data source.

    -Ivan


    Ivan Sanders My LinkedIn , My Blog, @iasanders, BI in SP2013, SP2013 Content Packs.

    Thursday, April 4, 2013 10:20 AM
  • Hi,

    Is your issue resolved, please let us know so I can get this ticket off my plate...

     

    -Ivan


    Ivan Sanders My LinkedIn , My Blog, @iasanders, BI in SP2013, SP2013 Content Packs.

    Wednesday, April 17, 2013 3:40 AM
  • Thanks Ivan. It worked and this can be closed.

    Siva

    Thursday, April 18, 2013 4:07 AM
  • Then you should mark the post as the answer!
    Thursday, April 18, 2013 4:18 AM