Importing csv data that uploads to onedrive

[ECB]

Hi again

I hope you are all well.

I'm looking for some guidance for how to set up.

I've managed to set up a csv file data dump to my one drive - this data dump is daily and contains survey responses. It is in a effective table format with headers. It always goes to the same file - the file is simply overwritten with the new information.

I'd like to use that data to create a simple excel dashboard to show number of surveys, surveys done that day - no great issue with this, I can easily set this up.

What I'm struggling to get my head round is how best to get the xslx file to talk to the csv file. The xlsx file will reside in the same folder on onedrive.

I think I could simply reference the cells in a data tab on the spreadsheet, but is there a better way? Could I use get external data - I only would really need the data to update when the spreadsheet was opened?

Does this make sense? Is there a better way? Is there anything I should consider?

ATB
B

 

[Alex Blakenburg]

The easiest repeatable way is to use Power Query.
The next time all you need to do is hit refresh.

 

[ECB]

Thank you very much for your help. This seems like a way forward.

I think I will need to do this through Visual Basic as I use excel for Mac or find a way through my 365 subscription to use excel on line.

I've had a quick look and it seems I'm going to need to learn something new - I'll have a look at the forums because I'm sure there will be a Visual Basic one here.

Edit: Okay...this is the visual basic forum...

I'll find a tutorial to get me moving on it, but in the meantime Microsoft has helpfully provided this sample code:

Sub CreateSampleList()
ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
"let" & vbCr & vbLf & _
"Source = {1..100}," & vbCr & vbLf & _
"ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
"RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
"in" & vbCr & vbLf & _
"RenamedColumns"
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [SampleList]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "SampleList"
.Refresh BackgroundQuery:=False
End With
End Sub

I'll try and work my way through what everything means, but could some kind person possibly point me to the bits that I need to change to get it to work,

and also what form the source should take it if both files reside on the same folder in OneDrive?

Many thanks for reading.

 

[ECB]

Brilliant, Power Query got me sorted and moving. Thank you.

It's really weird that I just needed a windows machine to set up the link then I have limited editing so I can revert to my Mac.

Some simple questions:

Can you use Power Query on an iPad?

Is it possible to use an online version of excel that has full power query editing? I suppose an interface to my onedrive excel files through a web based interface?

ATB
B

 

[Alex Blakenburg]

I’m afraid that you need you need Excel 2016, 2019 or 365 on either Windows or the Mac.
It’s not supported on Android, iOS or Online.
Refer: Power Query data sources in Excel versions
Remember that the 365 subscription is usually for families & friends, so you may be able to hook into that.

 

[ECB]

I’m afraid that you need you need Excel 2016, 2019 or 365 on either Windows or the Mac.
It’s not supported on Android, iOS or Online.
Refer: Power Query data sources in Excel versions
Remember that the 365 subscription is usually for families & friends, so you may be able to hook into that.

Brilliant thanks for your help.

 

[Alex Blakenburg]

Brilliant thanks for your help.

No worries, happy to help.