excelexcel-web-query

What are the options for a .iqy file?

I'm trying to understand the options for the .iqy file format. I'm trying to use it to feed CSV data from an external web source into Microsoft Excel but I'm not finding any canonical documentation to make sure that I'm doing it right.

Note: I'm doing a Q&A-style post and will be answering my own question.

Solution

  • Microsoft has very little documentation on their .iqy (or a Excel Web Query - Internet Inquiry) file and nothing about their allowed options or format. This because the .iqy file is normally created from a VBA Web Query output. But, if you reference the VBA QueryTable documentation instead and do some translation, you'll find this (simplified here):

    .iqy Options VBA Web Query Options Type .iqy Values Default Value
    ConsecutiveDelimitersAsOne WebConsecutiveDelimitersAsOne Boolean True/False False
    DisableDateRecognition WebDisableDateRecognition Boolean True/False False
    DisableRedirections WebDisableRedirections Boolean True/False False
    Formatting WebFormatting List All (1), RFT (2), None (3) RFT
    PreFormattedTextToColumns WebPreFormattedTextToColumns Boolean True/False True
    Selection WebSelectionType List EntirePage (1), AllTables (2), SpecifiedTables (3) AllTables
    SingleBlockTextImport WebSingleBlockTextImport Boolean True/False False

    Note: There are other .iqy options like SharePointApplication but aren't prefixed with "Web" and therefore weren't added to this list. You can find the rest of them under Properties.

    An example Web .iqy file is formatted in this style:

    WEB
1
https://www.example.com/file.csv

Selection=1
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False