Importing a CSV file with ws.QueryTables.Add

[Cazforshort]

Hello, Im having trouble importing a csv file. The first row works fine, but the rest are all wonky. Here is a sample of the first and second row from the csv:




"Order # ","Purchased From (Store)","Purchased On","Bill to Name","Ship to Name","G.T. (Base)","G.T. (Purchased)",Status
202366,"RaD.com
Ra D
Ra D Default View
","Dec 26, 2015 12:07:25 PM","John Smith","John Smith",$70.00,$70.00,Pending




The order number goes into the correct cell, but it splits the RaD.com Ra D and Ra D Default View onto different rows in different cells. The date also gets cut into two cells as [,Dec 19][ 2015 12:07:25 PM"]


Heres how its supposed to look.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Purchased From (Store)[/TD]
[TD]Purchased On[/TD]
[TD]Bill to Name[/TD]
[TD]Ship to Name[/TD]
[TD]G.T. (Base)[/TD]
[TD]G.T. (Purchased)[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]202366[/TD]
[TD]RaD.com
Ra D
Ra D Default View[/TD]
[TD]Dec 26, 2015 12:07:25 PM[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD]$70.00[/TD]
[TD]$70.00[/TD]
[TD]Pending[/TD]
[/TR]
</tbody>[/TABLE]


Heres my code:

Sub ImportCSV(fname)
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = "temp" & Worksheets.Count + 1


With ws.QueryTables.Add( _
        Connection:="TEXT;" & fname, _
        Destination:=Range("A1"))
    .Name = "Temp" & Worksheets.Count + 1
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlMacintosh
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .Refresh BackgroundQuery:=False
    '.UseListObject = False
End With
End Sub

The RaD.com cell doesn't have to be formatted perfectly, it just all needs to be in one cell. I cant change the way the text file is formatted because there are thousands of them.