Import CSV data from a web service in Excel

Question

I wrote a simple web service that returns large amounts of csv data. I will import this into Excel in a tabular format using the Excel function "Data from the Internet".

Is there a way to get Excel to automatically parse the csv fields returned into individual columns as part of the import operation?

Currently, the only tool I do for this is to first import the data into a single column, and then write the VBA code to select the data and split it using TextToColumns . This seems messy / error prone.

Another option I have is to change the web server to serve data like HTML. However, I do not want to do this, because adding tags around each csv field will greatly affect the amount of data returned.

Answer 1

Adam

Here is what I use. I found the kernel somewhere on the Internet, but I don’t know where.

What he does is open a tab-delimited file and read data on an excel sheet

 If Answer1 = vbYes Then 'I asked prior if to import a tab separated file Sheets("ZHRNL111").Select 'Select the sheet to dump the data On Error Resume Next With ActiveSheet If .AutoFilterMode Then .ShowAllData 'undo any autofilters End With Sheets("ZHRNL111").Cells.Clear 'remove any previous data On Error GoTo 0 Range("A1").CurrentRegion.Delete Fname = MyPath & "\LatestReports\Report-111.tsv" Open Fname For Input As #1 iRow = 1 Line Input #1, Record On Error Resume Next Do Until EOF(1) P = Split(Record, vbTab) For iCol = 1 To 14 Cells(iRow, iCol) = P(iCol - 1) Next iCol iRow = iRow + 1 Line Input #1, Record Loop On Error GoTo 0 Close 1 End If 

Hi,

Robert Ilbrink

Answer 2

Depending on the version of excel you are using, you should be able to open .csv in excel and use the text to columns function built into excel.

In addition, if you can change your csv to separate columns based on "," instead of tabs, excel will open it directly, without having to format it. I know, however, this can sometimes be a problem depending on the data you import, because if the data contains a comma, it should be inside the quotes. In my experience, the best way is to use quotes on each field, if possible.

Hope this helps.

Answer 3

I am actually creating a product right now to do this in both XML and JSON for Excel. I know that comma separator works in Excel with some caveats. One way is to put some “around” the text between the delimiters for the “Data From Web” function. However, there are problems with this. I found that despite the increase in size, XML was the best option for a quick transition. I managed to create a service and pass on to my project manager an Excel document that he could update at any time.