如何使用制表符分隔符和文本限定符“导入文本文件”使用VBA编码。 [英] How do I import text file with both tab delimiter and text qualifier " using VBA coding.
本文介绍了如何使用制表符分隔符和文本限定符“导入文本文件”使用VBA编码。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在订单项行中合并两个分隔符。我如何一次性拆分数据。如果我只给vbtab数据导入正确但是有双引号
Sub importfile()
打开 E :\ anaken\excel n vba\project \DATA SETS_VBA.txt 对于输入 As #1
x = 0
直到EOF( 1 )
行输入#1,LineFromFile
lineitems = Split(LineFromFile,vbCrLf& vbTab)
ActiveCell.Offset (x, 0 )。值= lineitems( 0 )
ActiveCell.Offset(x, 1 )。值= lineitems( 1 )
ActiveCell.Offset(x, 2 )。值= lineitems(< span class =code-digit> 2 )
ActiveCell.Offset(x, 3 )。值= lineitems( 3 )
ActiveCell.Offset(x, 4 )。值= lineitems( 4 )
ActiveCell.Offset(x, 5 )。值= lineitems( 5 )
ActiveCell.Offset(x, 6 )。值= lineitems( 6 )
ActiveCell.Offset(x, 7 )。值= lineitems( 7 )
ActiveCell.Offset(x, 8 )。值= lineitems( 8 )
ActiveCell.Offset(x, 9 )。值= lineitems( 9 )
x = x + 1
循环
关闭#1
结束 Sub
数据
期间单位代码地区代码类型代码MT / TT贸易面板子商店类型MBD(地区)宇宙数PCV宇宙数理想样品(当前)可用样品
3350607 500199 500 199 TT手册SIMPLE Market Stall Large GRA1,046363,657,70629 10
3350607 500150 500 150 TT手册SIMPLE Market Stall Small GRA2,2591,168,516,331 28 43
3350607 610199 610 199 TT手册SIMPLE Market Stall Large North LUK1,326323,053,61240 27
3350607 610150 610 150 TT manual SIMPLE Market Stall Small North LUK1,473130,342,51723 29
3350607 660199 660 199 TT手册SIMPLE Market Stall Large North LUK2,9617,213,883,34838 14
i想要划界标签和双引号
解决方案
为什么要强行打开门?
使用<小时ef =https://msdn.microsoft.com/en-us/library/office/ff837097.aspx> OpenText方法 [ ^ ]将文本文件作为新工作簿加载和解析,其中包含一个包含已解析文本文件的工作表数据。
我建议您在运行之前开始记录宏文本导入向导 [ ^ ],然后完成录制。接下来,您将能够根据需要改进该代码。
Workbooks.OpenText文件名:= E:\ annaken \excel n vba\project \DATA SETS_VBA .txt,_
原产地:= 852,StartRow:= 1,DataType:= xlDelimited,TextQualifier:= _
xlDoubleQuote,ConsecutiveDelimiter:= False,Tab:= True,分号: = False,_
逗号:= False,空格:= False,其他:= False,FieldInfo:= Array(数组( 1 , 1 ),_
数组( 2 , 1 ),数组( 3 , 1 ),数组( 4 , 1 ),数组( 5 , 1 ),数组( 6 , 1 ),数组( 7 , 1 ),数组( 8 ,< span class =code-digit> 1 ),_
数组( 9 , 1 ),数组( 10 , 1 ),数组( 11 , 1 ),数组( 12 , 1 ),数组( 13 , 1 )),_
TrailingMinusNumbers:= True
另一种方法是使用 QueryTables.Add [ ^ ]方法:
Dim wsh 作为工作表
设置 wsh = ThisWorkbook.Worksheets.Add After:= ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
with wsh.QueryTables.Add(Connection:= _
TEXT; E:\ annaken \ excel n vba\project \DATA SETS_VBA.txt,目的地:=范围(
A
1)_
)
.Name = wsh.Name
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 852
.TextFileStartRow = 1
。 TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes =数组( 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 )
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:= False
结束 使用
How do i incorporate both the delimiters in the line items line. how do i split the data at one go.if i give only vbtab the data is imported correctly but there are double quotes left out
Sub importfile()
Open "E:\anaken\excel n vba\project\DATA SETS_VBA.txt" For Input As #1
x = 0
Do Until EOF(1)
Line Input #1, LineFromFile
lineitems = Split(LineFromFile, vbCrLf & vbTab)
ActiveCell.Offset(x, 0).Value = lineitems(0)
ActiveCell.Offset(x, 1).Value = lineitems(1)
ActiveCell.Offset(x, 2).Value = lineitems(2)
ActiveCell.Offset(x, 3).Value = lineitems(3)
ActiveCell.Offset(x, 4).Value = lineitems(4)
ActiveCell.Offset(x, 5).Value = lineitems(5)
ActiveCell.Offset(x, 6).Value = lineitems(6)
ActiveCell.Offset(x, 7).Value = lineitems(7)
ActiveCell.Offset(x, 8).Value = lineitems(8)
ActiveCell.Offset(x, 9).Value = lineitems(9)
x = x + 1
Loop
Close #1
End Sub
the data is
Period Cell Code Region Code Type Code MT/TT Trade Panel Sub Store Type MBD (Region) Universe Number PCV Universe Number Ideal Sample (current) Usable Sample
3350607 500199 500 199 TT manual SIMPLE Market Stall Large GRA "1,046" "363,657,706" 29 10
3350607 500150 500 150 TT manual SIMPLE Market Stall Small GRA "2,259" "1,168,516,331" 28 43
3350607 610199 610 199 TT manual SIMPLE Market Stall Large North LUK "1,326" "323,053,612" 40 27
3350607 610150 610 150 TT manual SIMPLE Market Stall Small North LUK "1,473" "130,342,517" 23 29
3350607 660199 660 199 TT manual SIMPLE Market Stall Large North LUK "2,961" "7,213,883,348" 38 14
i want to delimit the tabs and the double quotes
解决方案
Why to force doors wide open?
Use OpenText method[^] to load and parse a text file as a new workbook with a single sheet that contains the parsed text-file data.
I'd suggest to start recordding macro before you run Text Import Wizard[^], then finish recording. Next, you'll be able to improve that code to your needs.
[EDIT#1]
Workbooks.OpenText Filename:="E:\anaken\excel n vba\project\DATA SETS_VBA.txt", _ Origin:=852, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _ TrailingMinusNumbers:=True
Another way is to use QueryTables.Add[^] method:
Dim wsh As Worksheet Set wsh = ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) With wsh.QueryTables.Add(Connection:= _ "TEXT;E:\anaken\excel n vba\project\DATA SETS_VBA.txt", Destination:=Range("
A
1") _ ) .Name = wsh.Name .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With
这篇关于如何使用制表符分隔符和文本限定符“导入文本文件”使用VBA编码。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文