.csv import echoing data in additional columns

[JimSnyder]

I am using Excel 2003 to import a pipe delimited log with 39 columns into a spreadsheet. What I am seeing is that roughly the last 11 columns of data are being added repeatedly beyond the 39th column and shifting upward a row at a time after several replications.

This macro was initially recorded from doing the import manually, but I never looked out at the trailing columns until I needed to do some data specific manipulation and discovered the problem. Searching the archives is hard because I cannot describe the problem easily for the search engine.

Here is the import:

Dim Filename As String

Filename = Application.GetOpenFilename( _
FileFilter:="ComplianceWire Log Files, *.log", _
Title:="Select a ComplianceWire Log")

If (Filename <> "False") Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Filename, Destination:=Range("A1"))
.Name = "Abbott_edms_" & Format(Date, "mmddyyyy")
.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 = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Else
MsgBox "No file selected"
Exit Sub
End If

I prepared sample a sample input log file and the resultant output file, but see no way to present them here.

 

[RichardMGreen]

It looks like you've done the same thing I did a while ago.
You used the ".add" which installs an new import in the sheet instead of just updating the current one.
You'll need to delete your current data sheet and reimport manually for the first one.
The adjust your code to remove the .add and the next line start with .connection (IIRC) and carried on from there.

Hope this helps.

 

[JimSnyder]

I started with a new spreadsheet, pasted in my macro, ran it, checked the spreadsheet, and the additional rows are gone. I had been doing iterative development of the macro in the same spreadsheet and did not realize the problem it was causing. My sincere thanks for both the quickness and the accuracy of your reply!

 

[RichardMGreen]

No problem. Like I said I ran foul of the same problem.