CSV Import while skipping "column" data from CSV

[chipsworld]

OK...I have some code that imports a csv into a sheet, but I don't want ALL of the data...

I need to be able to skip certain "fields" in the line string from the CSV. How can I accomplish that with the below?

Sub ImportCSV(customerfilename, unitnam, targetSheet)
Dim rw As String

With ThisWorkbook.Sheets("Template").QueryTables.Add(Connection:= _
"TEXT;" & customerfilename, Destination:=ThisWorkbook.Sheets("Template").Range("$C$2"))
.Name = "unitnam"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.Refresh BackgroundQuery:=False
'.Delete BackgroundQuery:=True
'.UseListObject = False
End With
end sub

 

[Joe4]

After importing, just delete the fields you do not want.

 

[chipsworld]

Joe4...thanks, but not automated! I think I figured it out...

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

this should do the trick I believe. Should have just done more research before posting! LOL

 

[Joe4]

Joe4...thanks, but not automated! I think I figured it out...

Glad you figured it out.
Note you CAN automate the deletion of columns!

 

[chipsworld]

Joe4...Yes. The problem here is that the data is being imported to a preset template, so no ability to modify once put in the sheet.

The idea is to make it as simple as possible for the end user and maintain a consistent form for distribution.

 

[Joe4]

Joe4...Yes. The problem here is that the data is being imported to a preset template, so no ability to modify once put in the sheet.

The idea is to make it as simple as possible for the end user and maintain a consistent form for distribution.

Gotcha. Obviously, we do not know those details unless you tell us.

 

[chipsworld]

Joe4...OK, I guess this doesn't work the way I thought.

The columns marked with "9" simply do not import the data...they still show up as a blank column on the sheet...

How can I skip them so that they are not imported at all? I am trying to eliminate the columns altogether...

 

[Joe4]

I am partial to the old "Workbooks.OpenText" method, which is what you used to record when you used the File Import Wizard. That does not leave blank columns for columns that are skipped over. Documentation here: Workbooks.OpenText(String, Object, Object, Object, XlTextQualifier, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object) Method (Microsoft.Office.Interop.Excel)

Here is some code I copied out of an old procedure I have:

Workbooks.OpenText Filename:=dataFile, _
Origin:=65001, startRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True

I think the only parts you would need to change are the parts in red, your file name, and the field array (which you already have above in your previous posts).

 

[chipsworld]

OK..recorded a macro, and used those settings...and, now it works???

I am very confused. Thanks though...

 

[chipsworld]

Thanks Joe4... Ill play around and see what I can mess up next!