Import CSV into Excel by Column Name

[stevio]

Hi There,

I have the following code to import a csv file into Excel:

Sub ImportCSV()

Dim vPath As Variant
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set wb = Excel.ActiveWorkbook
Set ws = Excel.ActiveSheet

vPath = Application.GetOpenFilename("CSV (Comma Delimited) (*.csv),*.csv" _
, 1, "Select a file", , False)
''//Show the file open dialog to allow user to select a CSV file

If vPath = False Then Exit Sub
''//Exit macro if no file selected

Workbooks.OpenText Filename:=vPath, Origin:=xlMSDOS, StartRow:=1 _
    , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True _
    , FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat), _
    Array(3, xlTextFormat))
''//The fieldinfo array needs to be extended to match your number of columns

Columns.EntireColumn.AutoFit
''//Resize the columns

Sheets(1).Move After:=wb.Sheets(1)
''//Move the data into the Workbook

End Sub

This works fine, however I only want to import certain columns based on heading names which are always the same (Product, Product Code, etc) - the CSV could have these headings or not (should bomb out if not)

Any suggestions on how to import by column name? I've search this post, couldn't really find an answer

 

[GHolden]

Hi, you can connect to the csv file using ADO which allows you to use named columns

E.g.

Sub readChuteFull()
Dim xlcon As ADODB.Connection
Dim xlrs As ADODB.Recordset

Set xlcon = New ADODB.Connection
Set xlrs = New ADODB.Recordset

Dim currentDataFilePath As String
Dim currentDataFileName As String
Dim nextRow As Integer


currentDataFilePath = rootPath & "Data\Alarm Data\"

xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"
xlcon.ConnectionString = "Data Source=" & currentDataFilePath & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
xlcon.Open

currentDataFileName = "TBF_AlarmExport" 

xlrs.Open "SELECT Duration,Area,Name,Description FROM [" & currentDataFileName & ".csv]", xlcon
xlrs.MoveFirst

nextRow = Worksheets("Raw Data").UsedRange.Rows.Count + 1

Worksheets("Raw Data").Cells(nextRow, 1).CopyFromRecordset xlrs

xlrs.Close

xlcon.Close

Set xlrs = Nothing
Set xlcon = Nothing
End Sub

There are two ways to write error-free programs; only the third one works.

 

[SkipVought]

hi,

I NEVER open a .csv text file using the method in your post. NEVER!

I would IMPORT the .csv file via Data > Get External Data > Text Files... wher you have much better control defining the data type of each column of data, which often is important when importing DATES or numbers that are really identifiers not use in math.

Once the table is in the sheet, you can then either delete unnecessary columns, or query the required structure from another sheet, or use the PivotTable Wizard to get the required columns on another sheet. Use youor macro recorder to 'automate.'

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]