Import CSV Macro Problems

[sajjaddaya]

Hi Guys,

I have managed to come up with the following macro. The problem I have is that some of the data are imported in text format, while other data are imported in general format.

I have a large data set, with more then 5,000 rows, and 2,000 columns, and I would like ALL the data imported at text. How can I modify this macro to make that happen?

Sub ImportData()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\*****\ATTRIBUTES-ModifiedExport.csv", Destination:=Range("$A$1"))
        .Name = "ATTRIBUTES-ModifiedExport.csv"
        .FieldNames = False
        .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
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = _
            Array(xlTextFormat)
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks in advance for all your help!
Sajjad

 

[sajjaddaya]

Problem solved, here's the solution:

Public Sub OpenCsv()
    ' I don't expect any more columns than 256 in my environment, so I can
    ' just fill this array and call it done.
    Dim columnFormats(0 To 2555) As Integer
    For i = 0 To 2555
        columnFormats(i) = xlTextFormat
    Next i
    Dim filename As Variant
    filename = Application.GetOpenFilename("All Files (*.*),*.*", 1, "Open", "", False)
    ' If user clicks Cancel, stop.
    If (filename = False) Then
        Exit Sub
    End If
 
    Dim ws As Excel.Worksheet
    Application.Workbooks.Add
    Set ws = Excel.ActiveSheet
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Sheets("Sheet3").Delete
        Application.DisplayAlerts = True
    With ws.QueryTables.Add("TEXT;" & filename, ws.Cells(1, 1))
        .FieldNames = True
        .AdjustColumnWidth = True
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileOtherDelimiter = "|"
        ''// This array will need as many entries as there will be columns:
        .TextFileColumnDataTypes = columnFormats
        .Refresh
    End With
End Sub