excel - 尝试将 CSV 文件导入名为 IMPORT 的工作表
问题描述
这是我的 Txt 文件的样子...这是通过一个旧但有用的工具导出的:
这是我在网上找到的代码:
Option explicit
Sub ReadInCommaDelimFile()
Dim rFirstCell As Range 'Points to the First Cell in the row currently being updated
Dim rCurrentCell As Range 'Points the the current cell in the row being updated
Dim sCSV As String 'File Name to Import
Dim iFileNo As Integer 'File Number for Text File operations
Dim sLine As String 'Variable to read a line of file into
Dim sValue As String 'Individual comma delimited value
'Prompt User for File to Import
sCSV = Application.GetOpenFilename("CSV Files, *.TXT", , "Select File to Import")
If sCSV = "False" Then Exit Sub
'Clear Existing Data
ThisWorkbook.Worksheets("IMPORT").Cells.Delete
'wsData.Cells.Delete 'Use this method if you set the vb-name of the sheet
'Set initial values for Range Pointers
Set rFirstCell = Range("A2")
Set rCurrentCell = rFirstCell
'Get an available file number
iFileNo = FreeFile
'Open your CSV file as a text file
Open sCSV For Input As #iFileNo
'Loop until reaching the end of the text file
Do Until EOF(iFileNo)
'Read in a line of text from the CSV file
Line Input #iFileNo, sLine
Do
sValue = ParseData(sLine, "','")
If sValue <> "" Then
rCurrentCell = sValue 'put value into cell
Set rCurrentCell = rCurrentCell.Offset(0, 1) 'move current cell one column right
End If
Loop Until sValue = ""
Set rFirstCell = rFirstCell.Offset(1, 0) 'move pointer down one row
Set rCurrentCell = rFirstCell 'set output pointer to next line
Loop
'Close the Text File
Close #iFileNo
End Sub
Private Function ParseData(sData As String, sDelim As String) As String
Dim iBreak As Integer
iBreak = InStr(1, sData, sDelim, vbTextCompare)
If iBreak = 0 Then
If sData = "" Then
ParseData = ""
Else
ParseData = sData
sData = ""
End If
Else
ParseData = Left(sData, iBreak - 1)
sData = Mid(sData, iBreak + 1)
End If
End Function
这是我的结果:
无论我尝试什么,我总是被引号和逗号卡住。
这是工作代码:
Option Explicit
Sub ReadInCommaDelimFile()
Dim rFirstCell As Range 'Points to the First Cell in the row currently being updated
Dim rCurrentCell As Range 'Points the the current cell in the row being updated
Dim sCSV As String 'File Name to Import
Dim iFileNo As Integer 'File Number for Text File operations
Dim sLine As String 'Variable to read a line of file into
Dim sValue As String 'Individual comma delimited value
Dim sValue2 As String 'Individual comma delimited value
'Prompt User for File to Import
sCSV = Application.GetOpenFilename("CSV Files, *.TXT", , "Select File to Import")
If sCSV = "False" Then Exit Sub
'Clear Existing Data
ThisWorkbook.Worksheets("IMPORT").Cells.Delete
'wsData.Cells.Delete 'Use this method if you set the vb-name of the sheet
'Set initial values for Range Pointers
Set rFirstCell = Range("A2")
Set rCurrentCell = rFirstCell
'Get an available file number
iFileNo = FreeFile
'Open your CSV file as a text file
Open sCSV For Input As #iFileNo
'Loop until reaching the end of the text file
Do Until EOF(iFileNo)
'Read in a line of text from the CSV file
Line Input #iFileNo, sLine
Do
sValue = ParseData(sLine, ",")
If sValue <> "" Then
sValue2 = Left(sValue, Len(sValue) - 1)
sValue2 = Right(sValue2, Len(sValue2) - 1)
rCurrentCell = sValue2 'put value into cell
Set rCurrentCell = rCurrentCell.Offset(0, 1) 'move current cell one column right
End If
Loop Until sValue = ""
Set rFirstCell = rFirstCell.Offset(1, 0) 'move pointer down one row
Set rCurrentCell = rFirstCell 'set output pointer to next line
Loop
'Close the Text File
Close #iFileNo
End Sub
Private Function ParseData(sData As String, sDelim As String) As String
Dim iBreak As Integer
iBreak = InStr(1, sData, sDelim, vbTextCompare)
If iBreak = 0 Then
If sData = "" Then
ParseData = ""
Else
ParseData = sData
sData = ""
End If
Else
ParseData = Left(sData, iBreak - 1)
sData = Mid(sData, iBreak + 1)
End If
End Function
解决方案
您的最后一次代码迭代表明您的CSV
文件已保存为*.txt
文件。
如果确实如此,您可以使用Workbooks.OpenText
允许您正确解析数据的方法打开它,包括处理单引号文本限定符字符。
这不会像 QueryTables 方法那样创建表。
然后将这个新打开的工作簿中的数据复制到IMPORT
当前工作簿中的工作表中。
例如:
Option Explicit
Sub ReadInCommaDelimFile()
Dim sCSV
Dim WB As Workbook, dataWS As Worksheet
sCSV = Application.GetOpenFilename("CSV Files (*.txt),*.txt", , "Select File to Import")
If sCSV = False Then Exit Sub
ThisWorkbook.Worksheets("IMPORT").Cells.Clear
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=sCSV, _
textqualifier:=xlTextQualifierSingleQuote, _
consecutivedelimiter:=True, _
Tab:=False, _
semicolon:=False, _
comma:=True, _
Space:=False, _
other:=False
Set WB = ActiveWorkbook
Set dataWS = WB.Worksheets(1)
dataWS.UsedRange.Copy ThisWorkbook.Worksheets("IMPORT").Range("A2")
WB.Close savechanges:=False
End Sub
推荐阅读
- pandas - 为什么我在有或没有列选择的情况下使用 pandas groupby() 获得不同的组大小数?
- group-by - “错误:n() 只能在数据上下文中调用”
- javascript - 如何在使用节点 js 调用 API 时获得准确的响应状态码?
- android - 如何仅使用 simpledateformat 更改包含月份和年份的字符串
- html - 如何修复页面以在谷歌缓存中正确显示
- android - 我正在尝试让代码工作以解码并显示原始 H264 tcp/ip 视频流。有人可以诊断出什么问题吗?
- c# - 以下使用 ResidentArrayGeneric 的 Hybridizer 代码有什么问题?
- here-api - 此处地图:在同一页面上的不同地图上加载多个 KML 文件并在每个地图上启用事件
- jquery - 如何在 JQuery 中使用 $.get 修复类型错误(不是函数)
- linux - 读取输入文件时出错:密钥已过期