vba 将 CSV 数据从网络服务导入 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7687086/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import CSV data from web service into Excel
提问by Adamski
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.
我编写了一个简单的 web 服务，它返回大量的 csv 数据。我将使用 Excel 的“来自 Web 的数据”功能以表格格式将其导入 Excel。
Is there a way to get Excel to automatically parse the csv fields returned into individual columns as part of the import operation?
有没有办法让 Excel 自动解析作为导入操作的一部分返回到各个列中的 csv 字段？
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.
目前我这样做的唯一方法是首先将数据导入到单个列中，然后编写 VBA 代码来选择数据并使用TextToColumns. 这感觉混乱/容易出错。
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.
我的另一种选择是修改 Web 服务器以将数据作为 HTML 提供。但是，我不愿意这样做，因为在每个 csv 字段周围添加标签会极大地影响返回的数据量。
采纳答案by Robert Ilbrink
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
它的作用是打开一个制表符分隔的文件并读取 Excel 表格中的数据
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
罗伯特·伊尔布林克
回答by Talon06
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.
根据您运行的 excel 版本，您应该能够在 excel 中打开 .csv 并使用 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.
此外，如果您可以修改您的 csv 以根据逗号“,”拆分列，而不是选项卡，excel 将直接打开它，而无需对其进行格式化。然而，我知道这有时可能是一个问题，具体取决于您导入的数据，因为如果数据包含逗号，则它必须在引号内。根据我的经验，最好的方法是尽可能在每个领域都使用引用。
Hope this helps.
希望这可以帮助。
回答by uadrive
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.
我现在实际上正在创建一个产品来在 XML 和 JSON for Excel 中执行此操作。我知道逗号分隔在 Excel 中确实有效，但有一些警告。一种解决方法是在文本周围放置一些“”，放在“来自 Web 的数据”功能的分隔符之间。然而，这仍然存在问题。我确实发现，尽管它的大小增加了，但 XML 是快速周转的最佳选择。我能够创建服务并将 Excel 文档交给我的项目经理，他可以随时更新。
