Forum Discussion

Joe_in_Baltimore's avatar
Joe_in_Baltimore
Copper Contributor
Nov 28, 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 ...
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
Diamond Contributor
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

Resources