Excel QueryTables.Add from URL Comma Delimited

Question

I have a server that returns large amounts of comma separated data in an http response. I need to import this data into excel.

I have this working by passing the contents to a temp file and then reading the temp file as a csv, but this process seems inefficient. The query tables can read directly from the http response, but it puts each line of data into a single cell, rather than separating into one cell per comma.

Is it possible to read comma separated data from an http response directly into excel from a C# excel add-in?

Thanks!

    public static void URLtoCSV(string URL, Excel.Worksheet destinationSheet, Excel.Range     destinationRange, int[] columnDataTypes, bool autoFitColumns)
    {
        destinationSheet.QueryTables.Add(
            "URL;" + URL,
        destinationRange, Type.Missing);
        destinationSheet.QueryTables[1].Name = URL;
        destinationSheet.QueryTables[1].FieldNames = true;
        destinationSheet.QueryTables[1].RowNumbers = false;
        destinationSheet.QueryTables[1].FillAdjacentFormulas = false;
        destinationSheet.QueryTables[1].PreserveFormatting = true;
        destinationSheet.QueryTables[1].RefreshOnFileOpen = false;
        destinationSheet.QueryTables[1].RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
        destinationSheet.QueryTables[1].SavePassword = false;
        destinationSheet.QueryTables[1].SaveData = true;
        destinationSheet.QueryTables[1].AdjustColumnWidth = true;
        destinationSheet.QueryTables[1].RefreshPeriod = 0;
        destinationSheet.QueryTables[1].Refresh(false);

        if (autoFitColumns == true)
            destinationSheet.QueryTables[1].Destination.EntireColumn.AutoFit();

    }

I solved just the same problem on my own: http://stackoverflow.com/questions/14252762/how-to-parse-line-by-line-winhttp-response-utf-8-encoded-csv Good luck! — Peter L., Jan 24 '13 at 07:07

score 3 · Answer 1 · answered Feb 11 '13 at 02:14

The easier solution than the one you reference is to use the type of "TEXT" instead of URL. TEXT supports all CSV imports, including from HTTP sources. URL appears to be designed to handle screen scraping more than anything else.

e.g. in your case:

destinationSheet.QueryTables.Add("URL;" + URL,

becomes

destinationSheet.QueryTables.Add("TEXT;" + URL,

And for those stumbling upon this post asking the same question but with VB scripting in Excel, the complete solution would look like:

' Load new data from web
With ActiveSheet.QueryTables.Add(Connection:="TEXT;http://yourdomain.com/csv.php", Destination:=Range("$A$1"))
.TextFileCommaDelimiter = True
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.Refresh BackgroundQuery:=False
End With

Excel QueryTables.Add from URL Comma Delimited

1 Answers1

Linked