在Excel中使用QueryTables导入csv引用的换行符 [英] Import csv with quoted newline using QueryTables in Excel

问题描述

不幸的是，如果csv文件包含引用的换行符，那么结果与您直接用excel打开csv文件时会有什么不同。与通常的进口设施不同， QueryTables.add（）假定任何换行符，无论是否引用，都是该行的结尾。

有没有办法呢？我更喜欢一个解决方案，它不涉及预先修改传入的csv文件来删除换行符，但是我也可以在前面提出建议。我想要在最终的excel文件单元格中添加换行符。

我的宏的相关部分：

  Sub LoadMyFile（）
'查询感兴趣的表
使用ActiveSheet.QueryTables.Add（Connection：=TEXT;_ 
& ThisWorkbook.Path&\& Range（A1）。值&.csv，_ 
目的地：=范围（$ A $ 2））
 .Name = ActiveSheet.Name 
 .FieldNames = True 
 .RowNumbers = False 
 .FillAdjacentFormulas = False 
 .PreserveFormatting = True 
 .RefreshOnFileOpen = False 
 .RefreshStyle = xlOverwriteCells 
 .SavePassword = False 
 .SaveData = True 
 .AdjustColumnWidth = True 
 .RefreshPeriod = 0 
 .TextFilePromptOnRefresh = False 
 .TextFilePlatform = 437 
 .TextFileStartRow = 1 
 .TextFileParseType = xlDelimited 
 .TextFileTextQuali fier = xlTextQualifierDoubleQuote 
 .TextFileConsecutiveDelimiter = False 
 .TextFileTabDelimiter = False 
 .TextFileSemicolonDelimiter = False 
 .TextFileCommaDelimiter = True 
 .TextFileSpaceDelimiter = False 
 .TextFileColumnDataTypes = Array（1，1，1，1，1）
 .TextFileTrailingMinusNumbers = True 
 .Refresh BackgroundQuery：= False 
 End with 
 End Sub 
  

这里是一个引用换行符的csv文件示例

 firstCol，secondCol
name1，data 
 one
name 
 2，data two
  

宏从单元格A1读取文件名（减去.csv扩展名），并假定csv文件与包含宏的excel文件。

我在Windows 7机器上使用32位Office Professional 2010。

解决方案

导入这样的CS V文件（数据点中的换行符）仅适用于Workbooks.Open，仅使用区域设置格式的CSV（分隔符，文本分隔符），则使用Excel。

 设置wb = Workbooks.Open（文件名：=C：\Users\axel\Desktop\test.csv，本地：= True）
 
 aData = wb.Worksheets（1）.UsedRange.Value 
 lRows = UBound（aData，1）
 lCols = UBound（aData，2）
 
 With ActiveSheet 
 .Range（.Cells（1，1），.Cells（lRows，lCols））。Value = aData 
结束
 
 wb.Close 
  

问候语

Axel

I have written a visual basic macro to load a csv file into Excel that I use quite frequently.

Unfortunately, if the csv file contains quoted newlines, the result is different from what you would get if you opened the csv file directly with excel. Unlike the usual import facility, QueryTables.add() assumes any newline it runs into, whether quoted or not, is the end of the row.

Is there a way around this? I'd prefer a solution that did not involve pre-modifying the incoming csv files to remove the newlines, but I'm open to suggestions on that front as well. I do want to have newlines in the resulting excel file cells, though.

The relevant part of my macro:

Sub LoadMyFile()
    ' Query the table of interest
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
      & ThisWorkbook.Path & "\" & Range("A1").Value & ".csv", _
      Destination:=Range("$A$2"))
        .Name = ActiveSheet.Name
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Here's an example csv file with quoted newlines

"firstCol","secondCol"
"name1","data
one"
"name 
2","data two"

The macro reads the file name (minus the .csv extension) from cell A1 and assumes the csv file is in the same directory as the excel file containing the macro.

I'm using 32 bit Office Professional 2010 on a windows 7 machine.

解决方案

the import of such CSV files (newlines in data-points) works only with Workbooks.Open and only with CSVs in the locale format (delimiter, text-delimiter), the Excel is used.

Set wb = Workbooks.Open(Filename:="C:\Users\axel\Desktop\test.csv", Local:=True)

aData = wb.Worksheets(1).UsedRange.Value
lRows = UBound(aData, 1)
lCols = UBound(aData, 2)

With ActiveSheet
 .Range(.Cells(1, 1), .Cells(lRows, lCols)).Value = aData
End With

wb.Close

Greetings

Axel

这篇关于在Excel中使用QueryTables导入csv引用的换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！