How do I download a csv into a vba array?

Question

I want to import data into MS Access 2010 tables, but the data I import will depend on the csv file I download. As such, I need to first import the CSV file from a URL into an VBA container of some sort.

This post seems to discuss the loading of CSV files into vba arrays, but not from a URL...

So far I've done this to download the CSV file:

Public Function GetFileFromURL(ByVal url As String) As Object Dim myURL As String myURL = "http://www.somesite.com/file.csv" Set GetFileFromURL = CreateObject("Microsoft.XMLHTTP") GetFileFromURL.Open "GET", myURL, False GetFileFromURL.Send End Function Sub DownloadSomething() GetFileFromURL("ichart.yahoo.com/table.csv?s=GOOG&a=0&b=1&c=2000&d=0&e=31&f=2010&g=d&ignore=.csv") End Sub

This runs, but I have no idea what to do with that GetFileFromURL object; At this point it's presumably just a list of strings, but how do I even get at those, and when I do, how do I convert them to arrays?

I would consider just downloading the file in a directory and reading it from there. If the script just returns a bunch of strings, then it will be hard to process. Of course you can read it into an array and parse it. The question is if this will not bring you in trouble once the csv changes its inherent format. — Trace
– Trace, Commented Oct 22, 2013 at 7:33

Paul M · Accepted Answer · 2013-10-29 03:27:08Z

I don't know if this would help you. This is a routine I used for a quality control program. The csv file had 4 rows with the max 20 lines, hence arrData(80) but It can be less than 20 lines also. I used this to take dimensions off an autocad drawing (which never was more than 20) and fill in the text boxes.

Kim is correct, it must be the same form of csv format each time.

Private Sub Import_Click() On Error GoTo HandleError RoutineName = Form.Name & " Import_Click Event" Dim Detline As String 'Record form selected file Dim I As Integer 'Index for rows (table & array) Dim FF As Integer 'next file number available for use by the Open statement Dim dlgOpen As FileDialog Set dlgOpen = Application.FileDialog(msoFileDialogOpen) With dlgOpen With .Filters .Clear .Add "Import csv or txt", "*.csv; *.txt", 1 .Add "All Files", "*.*", 2 End With .InitialFileName = "c:\QControl\" .FilterIndex = 1 .AllowMultiSelect = False .Title = "Select document to import" .ButtonName = "Ok" .Show If .SelectedItems.Count > 0 Then SelectedFile = .SelectedItems(1) End If End With ReDim arrData(80) DoCmd.GoToRecord acDataForm, "Main QC Form", acNewRec I = 1 ' initalize counter FF = FreeFile 'Use FreeFile to supply a file number that is not already in use. Open SelectedFile For Input As #FF Do While Not EOF(FF) 'Read selected file line by line Line Input #FF, Detline arrData = Split(Detline, ",") Me.Controls("letter" & I) = arrData(0) Me.Controls("printdim" & I) = arrData(1) Me.Controls("stepnumber" & I) = arrData(2) Me.Controls("Gage" & I) = arrData(3) I = I + 1 'increment counter Loop RunCommand acCmdSaveRecord ' Save the Record you're looking at Me.Requery ' Force the Recordset of the Form to update Me.Form1.Requery Close #FF 'close text file DoCmd.GoToRecord , , acLast Exit_Import_Click: DoCmd.SetWarnings True Exit Sub HandleError: RtnCode = ErrorHandler (EH_NumError:=Err.Number,DescError:= Err.Description,NameRoutine:=RoutineName) Resume Exit_Import_Click End Sub

Collectives™ on Stack Overflow

How do I download a csv into a vba array?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related