Import CSV data from web service into Excel

Question

I have written a simple web service that returns large volumes of csv data. I will to import this into Excel in a tabular format using Excel's "Data From Web" function.

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

At present the only means I have for doing this is to first import the data into a single column and then write VBA code to select the data and split it using TextToColumns. This feels messy / error-prone.

The other alternative I have is to modify the web server to serve back the data as HTML. However, I'm reluctant to do this as adding tags around each csv field will greatly impact the volume of data returned.

Answer 1

Adamski,

Here is something that I use. I found the core somewhere on the internet, but don't know where.

What it does is it opens a tab separated file and reads the data in 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

Regards,

Robert Ilbrink

Answer 2

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

Also, if you could modify your csv to split columns based on commas "," instead of tabs excel would open it directly without the need to format it. I know however this can sometimes be a problem depending on the data you are importing because if the data contains a comma it must be inside quotations. In my experience the best way is to use quotations on every 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 comma delimited does work in Excel, with some caveats. One way around it is to put some "" around the text in between the delimiters for the "Data From Web" feature. There are still issues with that however. I did find that despite it's increased size, XML was the best option for quick turn around. I was able to create the service and hand my project manager the Excel document which he could update at anytime.