Excel VBA Schema Definition File for CSV

I want to post an issue that took me some time to figure out. I don't know how to best describe it in the Title so it looks as though I entered like I would a google search so bear with me.

I am doing some work in Excel writing a custom macro for work. The intent of the macro is to reference a CSV file and fill an Excel sheet in a specified manor.

The CSV is my data source and it contains around 20 columns of data. That data is in differing formats. Some columns are of decimal type, some are of string type and some of date type. One column in particular was giving me a lot of issues which I will further explain below but was as follows:
1234567
8888999
1111222
2334344BBNN
255534ERERE
1223311
7634534

My issue is when I do a select on the CSV file it sees this column as a number and 7 characters in length. So what happens when I am feeding this data into my custom objects the fields that are not numbers 2334344BBNN for example come up blank. My belief is that the method I am calling is reading the first line and using that to build some sort of the schema definition for this file. Well that first line is not an accurate representation of the data.

Here is how I am doing the select:

Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=""text;HDR=No;FMT=Delimited"""

Set oRS = CreateObject("ADODB.RECORDSET")
oRS.Open "SELECT * FROM " & strFileName, oConn, 3, 3


strFilePath and strFileName both come from an input box I open for the user to choose there CSV.

I did a lot of searching on this issue and ultimately posted in a form specific to Excel issues. I will see if I can find the post. Basically the gentleman who responded provided me with a solution that work well for me. http://forums.devx.com/showthread.php?t=173508

The file name is schema.ini that you would create and look like below for example:

[mydata.csv]
ColNameHeader=False
Format=Delimited(,)
MaxScanRows=0
CharacterSet=ANSI
col1=Field1 Text Width 10
col2=Field2 Text Width 10
col3=Field3 Text Width 10
col4=Field4 DateTime
col5=Field5 Text Width 10
col6=Field6 Long
col7=Field7 Text Width 3
col8=Field8 Long
col9=Field9 Long
col10=Field10 Long
col11=Field11 Long
col12=Field12 Long
col13=Field13 Text Width 10
col14=Field14 Long
col15=Field15 Long
col16=Field16 Text Width 10
col17=Field17 Text Width 10
col18=Field18 Text Width 10
col19=Field19 Long

What I did was build this schema file on the fly. I had the filename that I needed in strFileName. It was simply building the function to create this file. Here is the function I used. I should note that you need to add a reference to the following as well. VBE Tools > References Microsoft Scripting Runtime

Function CreateSchemaFile(ByVal strFileName As String, ByVal strFilePath As String) As Boolean
Dim FSO As FileSystemObject
Dim FSOFile As TextStream
Dim FilePath As String
Dim NoOfLoop As Integer

FilePath = strFilePath & "\Schema.ini" ' create a test.txt file or change this

Set FSO = New FileSystemObject
' opens file in write mode
Set FSOFile = FSO.OpenTextFile(FilePath, 2, True)
FSOFile.WriteLine ("[" & strFileName & "]")
FSOFile.WriteLine ("ColNameHeader=False")
FSOFile.WriteLine ("Format=Delimited(,)")
FSOFile.WriteLine ("MaxScanRows=0")
FSOFile.WriteLine ("CharacterSet=ANSI")
FSOFile.WriteLine ("col1=Field1 Text Width 5")
FSOFile.WriteLine ("col2=Field2 Text Width 8")
FSOFile.WriteLine ("col3=Field2 Text Width 30")
FSOFile.WriteLine ("col4=Field4 DateTime")
FSOFile.WriteLine ("col5=Field5 Text Width 10")
FSOFile.WriteLine ("col6=Field6 Long")
FSOFile.WriteLine ("col7=Field7 Text Width 10")
FSOFile.WriteLine ("col8=Field8 Long")
FSOFile.WriteLine ("col9=Field9 Double")
FSOFile.WriteLine ("col10=Field10 Long")
FSOFile.WriteLine ("col11=Field11 Double")
FSOFile.WriteLine ("col12=Field12 Long")
FSOFile.WriteLine ("col13=Field13 Text Width 5")
FSOFile.WriteLine ("col14=Field14 Text Width 6")
FSOFile.WriteLine ("col15=Field15 Text Width 4")
FSOFile.WriteLine ("col16=Field16 Double")
FSOFile.WriteLine ("col17=Field17 Text Width 5")
FSOFile.WriteLine ("col18=Field18 Text Width 1")
FSOFile.WriteLine ("col19=Field19 Text Width 5")
FSOFile.WriteLine ("col20=Field20 Text Width 5")
FSOFile.WriteLine ("col21=Field21 Text Width 10")
FSOFile.Close
CreateSchemaFile = True
End Function

The only thing that I haven’t tested yet is what rights do users have to write out to the location where the CSV exists as this file has to exist in the same folder as the file it is referencing.

Anyway, I hope this helps someone else out and maybe me again in the future.