VBA Import CSV File with different field types in various orders

[jaysunice]

I'd like to import a CSV file with VBA.

• Some of the columns should be formatted as text (i.e., numbers with leading or trailing zeros such as ZIP codes), others as numbers, some as dates, and others should be skipped.
• I know the proper data type by looking at the header row name, however, the order of the columns is not always the same upon import -- so it's not as easy as saying, column 1 = text, column 2 = number, etc... because they will come in differently.

The long way (I can think of): import csv, delete all but header row, determine data type, re-import csv with proper data type.

Is there a way to do this on-the-fly? I recorded the "Text Import Wizard" and it's a good start, but I'm getting lost on how to assign scan the header row (1st row) prior to assignment of the data type.

 

[p45cal]

You could use a little function like this one adapted from cpearson.com:

Sub test()
xxx = ReadHeaders("C:\Users\jaysunice\Documents\File.csv")
yyy = Split(xxx, ",")
End Sub

Function ReadHeaders(FName As String)
X = FreeFile
Open FName For Input Access Read As #X
If Not EOF(1) Then Line Input #X, ReadHeaders
Close #X
End Function

which reads the first line of a text file. You could then split it as in the commented out line and you have, if it's a proper comma separated variable file, an array of headers to plough through.

 

[p45cal]

You could then split it as in the commented out line and…

but it's not commented out is it? (line starts yyy =)

 

[jaysunice]

Yes, not commented, but a great start! Thanks!

 

[p45cal]

There is a serious error:
If Not EOF(1) Then Line Input #X, ReadHeaders
should read:
If Not EOF(X) Then Line Input #X, ReadHeaders