Importing csv data that uploads to onedrive

ECB

Board Regular
Joined
Mar 3, 2009
Messages
55
Office Version
  1. 365
Platform
  1. MacOS
  2. Mobile
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,138
Office Version
  1. 365
Platform
  1. Windows
The easiest repeatable way is to use Power Query.
The next time all you need to do is hit refresh.
 
  • Like
Reactions: ECB

ECB

Board Regular
Joined
Mar 3, 2009
Messages
55
Office Version
  1. 365
Platform
  1. MacOS
  2. Mobile
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

Board Regular
Joined
Mar 3, 2009
Messages
55
Office Version
  1. 365
Platform
  1. MacOS
  2. Mobile
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,138
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
  • Like
Reactions: ECB

Forum statistics

Threads
1,136,643
Messages
5,676,965
Members
419,665
Latest member
allsmilees2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top