VBA - Import CSV - Delimiter issues

[Fishboy]

Hi guys,


I have been working on a VBA macro to import a CSV file into Excel 2010. On the whole it has been successful with most CSV files however with one particular batch the first "column" looks like this


item:45758:0:0:0:0:0:0


As such Excel is insisting on breaking the data up using the colons as delimiters despite this all being "one entry for one column". The rest of the CSV imports fine using commas as delimiters as intended but where this one column becomes eight columns, it is messing up all of the headers.


I have tried manually recording a macro to ensure that either A) colon is NOT used as a delimiter, or B) the entire first colum is not imported, but nothing seems to stop this from happening.


Can anyone see what I can do to prevebnt this in the code I am using below please?

' Sales_Import Macro
'


'
    Sheets("Item Lookups").Select
    Sheets("Raw Sales Data").Visible = True
    Sheets("Raw Sales Data").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\DBAH\FILENAME.csv", Destination:=Range( _
        "$A$1"))
        .Name = "FILENAME"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Raw Sales Data").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Item Lookups").Select
End Sub

Many thanks!

 

[Kenneth Hobson]

There are some your could try changing like .TextFileTextQualifier = xlTextQualifierDoubleQuote.

Otherwise, try obfuscating text file data and posting or post a sample text file to a free site like dropbox.

 

[SpillerBD]

Since column 1 is being imported as a Date value, the imported value must be in the consistent format.
You may need to import the values as Text first and then convert to your date values.
Or better yet read the text in the Macro and validate each one before actually recording to the sheet.