Text to Columns via VBA module 1

[cpjeffm]

I've got a VBA module built to import a text file but need some help with one thing. The problem is, field 7 of the text file can contain commas and if commas are present, it's splitting the text up instead of leaving the commas in field 7. I also need to leave the double quotes in the data if they are present in the text file. Here is an example of the text file.

"1234","0240","AF-483",47.20,0.00,"NE0004","000, NEW, (ID GMP08A)",0,"","","WT6A112","ma29",1,"99","W",3/3/16,"0000007021","A","",76034,0,"","0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00,"","1097-ma29-76034","",0.00,"459D95A0","","",,"","","1097-ma29-VNE0004","",,"","",""

Here's my VBA code.

Sub ImportINVENT_CAP()
Dim fName As String, LastRow As Long

Sheets("INVENT.CAP").Select
Range("B14").Select

fName = Application.GetOpenFilename("Cap Files (*.cap), *.cap")
If fName = "False" Then Exit Sub

LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
Destination:=Range("B" & LastRow))
.Name = "sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Range("Q14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "mm/dd/yy;@"

Range("AX14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "mm/dd/yy;@"

Range("BC14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "mm/dd/yy;@"

Cells.Select
Selection.ColumnWidth = 250

Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit

Range("B14").Select
End Sub

I tried changing the .TextFileTextQualifier = line to xlTextQualifierDoubleQuote and that fixed the issue with the commas in field 7 but now I'm missing the double quotes in the data.

Any help would be greatly appreciated.

 

[SkipVought]

hi,

Change

.TextFileTextQualifier = [s]xlTextQualifierNone[/s] xlDoubleQuote

Naturally I didn't IMPORT, but using Text to Columns I got columns 7 to be
[tt]
000, NEW, (ID GMP08A)
[/tt]

BTW, you ought to make the DATE formatting decisions in the IMPORT feature.




Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

That works Skip but I lose my double quotes after the import. I need to keep the commas in field 7 and also keep the double quotes post import.

 

[mintjulep]

First, stop using .Select.

Change the delimiter.

Substitute something else for the commas where they are used as delimiters. That is, for example, substitute the three characters ";" for the three characters ",".

Or something else if there is the possibility of a semi-colon in your data.

 

[cpjeffm]

The string fields are in quotes but the numeric fields aren't. If I replace "," with ";", it substitutes only for the string fields.

 

[SkipVought]

You want to have QUOTES in your data?

What a mess!

Do you think that you need QUOTES in your Excel data because you will eventually export another .csv text file?

Why do you need QUOTES in Excel?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

We're using the spreadsheet to verify that the data in the text file lines up with our internal spec. The spec contains examples of each field and those examples include the quotes so the data imported needs to match those examples. Basically, I need to split the fields in excel but the fields need to look exactly like they do in the text file.

 

[SkipVought]

Well you can’t have it both ways.

First off, you are IMPORTING your TEXT fields as GENERAL, rather than TEXT! In fact you’re IMPORTING all your fields as GENERAL, rather than making discrete type decisions for each column.

So if you were to designate the text columns to be imported as TEXT, then you could write a VBA procedure to bracket the data in your TEXT columns with QUOTES.

BTW, this statement reflects the fact that you are IMPORTING all the data as GENERAL...

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

Ok I have my arrays fixed. Every field with an array of 2 needs to be wrapped in quotes. How do I do that in VBA?

.TextFileColumnDataTypes = Array(2, 2, 2, 1, 1, 2, 2, 1, 2, 2, 2, 2, 1, 2, 2, 1, 2, 2, 2, 1, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 1, 1, 1, 1, 2, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 2, 1, 2, 2, 2)

 

[SkipVought]

Dim rng As Range, r As Range

For Each rng In Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows(1))
   If rng.NumberFormat = "@" Then
      For Each r In Intersect(ActiveSheet.UsedRange, rng.EntireColumn)
         r.Value = """ & r.Value & """
      Next
   End If
Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

I can't get it to work. Do I put that inline in my existing macro or build a second macro to run after the first?

 

[SkipVought]

You can put this in your macro AFTER the import.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Ah,

You’re appending this IMPORT under existing data.

So LastRow is the row to test, so...

For Each rng In Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows(LastRow))
   If rng.NumberFormat = "@" Then
      For Each r In ActiveSheet.Range(ActiveSheet.Cells(LastRow, rng.Column), rng.End(xlDown))
         r.Value = """ & r.Value & """
      Next
   End If
Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cpjeffm]

It's replacing all of my text fields with " & r.Value & "

 

[SkipVought]

use

r.Value = """" & r.Value & """"

Sorry, was coding from my iPad. Usually have my laptop and actually code solutions in Excel VBA.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mintjulep]

When you need to include the " character inside a string it is less confusing to assign it to a variable.

strQuote = """
myString = "Hello"
? myString
Hello
? strQuote & myString & strQuote
"Hello"

For reasons that I don't understand, in VBA this looks like

Public Sub test()

Const aQuote as String = """"

Debug.Print aQuote

End Sub

 

[Andrzejek]

Or you can do it this way:

Debug.Print [blue]Chr(34)[/blue] & "Hello" & [blue]Chr(34)[/blue]

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

.TextFileColumnDataTypes = Array(2, 2, 2, 1, 1, 2, 2, 1, 2, 2, 2, 2, 1, 2, 2, 1, 2, 2, 2, 1, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 1, 1, 1, 1, 2, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 2, 1, 2, 2, 2)

Yes, BUT you also have 3 DATE columns in your table that you also need to assign a MDY date type.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mintjulep]

Or you can do it this way:

You would think so. But VBA is not happy with

Const aQuote as String = Chr(34)

 

[cpjeffm]

I've run into an issue Skip. There's not always going to be data in every field in every line in the text file and that's causing some issues.

In the code below...
1) Field 17 in line 5 is blank
2) Field 22 is blank for all lines except for record 4

When I run the macro...
1) Field 17 - I'm getting quotes for the first 4 lines but not in line 6
2) Field 22 - I'm getting quotes in lines 1, 2 and 3, even though there's no data in those fields, then I'm getting a quotes in line 4 where there's data, then no quotes in lines 5 and 6.

"1234","0240","AF-
483",47.20,0.00,"NE0004","TEST1,TEST2",0,"","","WT6A112","ma29",1,"99",
"W",3/3/16,"0000007021","A","",76034,0,"","0","","1",0,"","","","","","
","","","","",,"",0,0.00,0.00,"","1097-ma29-
76034","",0.00,"459D95A0","","",,"","","1097-ma29-VNE0004","",,"","",""

"2345","0719","AA-
85530C",695.00,0.00,"M70803","TEST",0,"","","W1C322","ma29",1,"246","W"
,9/19/17,"0000039482","C","",212298,0,"","0","","1",0,"","","","","",""
,"","","","",,"",0,0.00,0.00,"","1097-ma29-
212298","",0.00,"3D3EF391","","",,"","","1097-ma29-
VM70803","",,"","",""

"3456","0719","EB-
50928",65.00,0.00,"M70803","TEST",0,"","","WT5BIN4","ma29",1,"246","W",
9/19/17,"0000039479","C","",212294,0,"","0","","1",0,"","","","","","",
"","","","",,"",0,0.00,0.00,"","1097-ma29-
212294","",0.00,"CCB415AB","","",,"","","1097-ma29-
VM70803","",,"","",""

"4567","0719","FA-
60047",125.00,0.00,"M70803","TEST",0,"","","WT6B313","ma29",1,"246","W"
,9/19/17,"0000039489","A","",212292,0,"W","0","","1",0,"","","","","","
","","","","",,"",0,0.00,0.00,"","1097-ma29-
212292","",0.00,"00687953","","",,"","","1097-ma29-
VM70803","",,"","",""

"5678","0719","HA-
51546",125.00,0.00,"M70803","TEST",0,"","","WT5BIN2","ma29",1,"246","W"
,9/19/17,"","C","",212295,0,"","0","","1",0,"","","","","","","","","",
"",,"",0,0.00,0.00,"","1097-ma29-
212295","",0.00,"7B4576D7","","",,"","","1097-ma29-
VM70803","",,"","",""

"6789","0719","LB-
51308",95.00,0.00,"M70803","TEST",0,"","","WT6B110","ma29",1,"246","W",
9/19/17,"0000039486","A","",212289,0,"","0","","1",0,"","","","","","",
"","","","",,"",0,0.00,0.00,"","1097-ma29-
212289","",0.00,"3E3BE998","","",,"","","1097-ma29-
VM70803","",,"","",""

Is there any way to only put quotes in the fields where data is
present?