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.

brettdj · Accepted Answer · 2011-12-25 01:24:32Z

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

edited Dec 25, 2011 at 1:24

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

answered Oct 12, 2011 at 9:59

Robert Ilbrink

7,9732 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zrz Over a year ago

How does it play with column delimiters inside data ? it has to be escaped somehow, and the Split is ignorant of that, isn't it ?

Talon06 · Accepted Answer · 2011-10-10 18:19:15Z

1

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.

answered Oct 10, 2011 at 18:19

Talon06

1,7963 gold badges28 silver badges51 bronze badges

Comments

uadrive · Accepted Answer · 2011-10-25 02:59:05Z

1

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.

answered Oct 25, 2011 at 2:59

uadrive

1,26914 silver badges25 bronze badges

1 Comment

Adamski Over a year ago

Thanks. I actually built something around a web service serving up csv in the end and then relying on the end user performing a "Text To Columns" operation on the data. It's far from ideal given the huge volumes of data I'm importing though.

Collectives™ on Stack Overflow

Import CSV data from web service into Excel

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related