How do I import text file with both tab delimiter and text qualifier " using VBA coding.

Question

0.00/5 (No votes)

See more:

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

VB

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

Posted 13-Sep-16 6:00am

Member 12737742

Updated 13-Sep-16 10:44am

Patrice T

v5

Add a Solution

2 solutions

Solution 2

I would try something like:

VB

lineitems = Split(LineFromFile, vbCrLf & vbTab)
For Scan = 0 To UNound(lineitems)-1
    If Left(lineitems(scan),1)= """" And Right(lineitems(scan),1) = """" Then
        lineitems(scan) = Mid(lineitems(scan), 2, lineitems(scan).Length - 2)
    EndIf
Next

The loop just check each strings for embedded in quotes and remove them.

[Update]
Beware of

VB

lineitems(scan) = Replace(lineitems(scan), Chr(34), "")

It replace quotes everywhere! Not only around the value.

Posted 13-Sep-16 10:44am

Patrice T

Updated 14-Sep-16 1:10am

v5

Comments

Member 12737742 14-Sep-16 2:41am

its showing object required in the second line.
vbCrLF and vb Tab is not working together. the first line of the data is coming in one cell and its running the rest of the data

Patrice T 14-Sep-16 2:55am

Which second line ?
Check variables spelling.

Member 12737742 14-Sep-16 3:00am

i will add the for loop within the do until loop?

Patrice T 14-Sep-16 3:33am

Yes just after
lineitems = Split(LineFromFile, vbCrLf & vbTab)
in your code

Member 12737742 14-Sep-16 4:29am

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, vbTab)

For i = 1 To UBound(lineitems)
If Left(lineitems(i), 1) = """" And Right(lineitems(i), 1) = """" Then
lineitems(i) = Mid(lineitems(i), 2, lineitems(i).ubound(lineitems) - 2)
End If
Next
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)
ActiveCell.Offset(x, 10).Value = lineitems(10)
ActiveCell.Offset(x, 11).Value = lineitems(11)
ActiveCell.Offset(x, 12).Value = lineitems(12)

x = x + 1
Loop

Close #1

End Sub

i did this the but its not delimiting the quotes

Patrice T 14-Sep-16 4:39am

do not change this
lineitems(scan).Length
it is the length of the string

Member 12737742 14-Sep-16 4:48am

Sub importfile()
Dim scan As Integer
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, vbTab)
For scan = 1 To lineitems.Length
If Left(lineitems(scan), 1) = """" And Right(lineitems(scan), 1) = """" Then
lineitems(scan) = Mid(lineitems(scan), 2, lineitems(scan).Length - 2)
End If
Next
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)
ActiveCell.Offset(x, 10).Value = lineitems(10)
ActiveCell.Offset(x, 11).Value = lineitems(11)
ActiveCell.Offset(x, 12).Value = lineitems(12)

x = x + 1
Loop

Close #1

End Sub
if i write this it is showing run time error
object required where the for loop is starting

Patrice T 14-Sep-16 5:25am

The for loop was to remain
For scan = 0 To UBound(lineitems)-1

Member 12737742 14-Sep-16 5:42am

yes it is working to some extent but
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,70 29 10
3350607 500150 500 150 TT manual SIMPLE Market Stall Small GRA 2,259" 1,168,516, 28 43
3350607 610199 610 199 TT manual SIMPLE Market Stall Large North LUK 1,326" 3230,53,612 40 27

under the universe column the quotes in the last place is staying

Member 12737742 14-Sep-16 6:50am

Sub importedrfile()
Dim scan As Integer
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, vbTab)
For scan = 0 To UBound(lineitems) - 1
lineitems(scan) = Replace(lineitems(scan), Chr(34), "")
Next
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)
ActiveCell.Offset(x, 10).Value = lineitems(10)
ActiveCell.Offset(x, 11).Value = lineitems(11)
ActiveCell.Offset(x, 12).Value = lineitems(12)

x = x + 1
Loop

Close #1

End Sub

i tried with this and it worked.
thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-09-13T07:52:00

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]

VB

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:

VB

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