Forum Discussion

Joe_in_Baltimore's avatar
Joe_in_Baltimore
Copper Contributor
Nov 29, 2021

CSV import into office 365 data model

How do I set up an ODC to import a csv file from the current working directory (i.e., without having to specify specify the full file path, just the name of the current working directory)?

 

Thank you?

BI & Data Analysis
  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Nov 29, 2021

    Hi Joe_in_Baltimore 

     

    there is an Excel worksheet function that returns the current directory of the active workbook:

    =INFO("DIRECTORY")

    Enter this function in a cell, lets say A1 and give it a name (e.g. "Source")

    Load that cell into Power Qery with "Data | From Table/Range"

     

    In the list of applied steps, remove all automatically applied steps apart from the first one ("Source"), then do a right-click in the cell with the path and choose "Drill Down":

    Select "File | Close & Load To..." and then create a connection only. Now you can use the source path as a parameter in your query.

    Next, import your CSV manually with "Data | From Text/CSV", select the file manually in the Import Data dialog box and choose "Transform" in the preview windows to open the Power Query editor.

    In the editor, select the first applied step ("Source") and change the formula that is shown in the formula bar:

    Replace the hard coded file path with the name of the first query (= "Source") and just leave the file name in quotation marks. It needs to be concatenated with the & sign.

    Most likely, you will get a Formula.Firewall error. Goto menu "File | Options and settings | Query Options"

    Then goto the Privacy settings of the current workbook and choose "Ignore the Privacy Levels...."

    Click on "Refresh Preview" and now you have your query to a dynamical source path.

    Then goto "File | Close & Load to..." and tick the checkbox "Add this data to the Data Model" 

    And you are done.

    This way, the query will always import the definde CSV file from the current directory which is defined in cell A1 (the cell with the range name "Source")

     

    If you also need the file name to be more flexible, you can add either to the INFO-formula. In this case, do not forget to update the Source-step in the Power Query editor.

     

     

     

     

     

     

     

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Nov 29, 2021

      Martin_Weiss 

      As a comment, Ignore privacy settings is a common practice, however better to avoid if possible. The workaround could be

      let
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content]{0}[Column1],
    SourceCSV = Csv.Document(
        File.Contents(
            Source & "DataSource.csv"),
            [Delimiter=",", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    SourceCSV
      • Martin_Weiss's avatar
        Martin_Weiss
        Bronze Contributor
        Nov 29, 2021

        Hi SergeiBaklan 

        thanks for your solution, I really like it.

        I was never very happy about ignoring the privacy settings, but did not found a proper solution. So your proposal is really appreciated.

         

        Thanks a lot!

         

Resources