Can't force field to be double instead of text despite schema.ini file

[mountainclimber]

I have a bunch of csv files that I import via VBA into tables in Access. I also have a schema.ini file in the same directory as the csv files that I am importing. Despite fields being designated as double type in the ini file, they are turned into text type fields in Access. This happens every time there are a lot of null values in the field at the top. If I have at least one non-null value in the first 10 or 20 fields the field gets turned into a number type.

After some research my assessment is that the shcema.ini file isn't doing anything and Access is determining the data type of the field, since the behavior I am seeing is exactly what Access's default behavior is like (if a schema.ini file is not used).

So that leads me to believe I have something wrong with my schcema.ini file.

Here is what my schema.ini file looks like:

[ForClsDatedModel_2015 0702_1004-1254.csv]
ColNameHeader=True
Format=CSVDelimited
"Ticker"=TEXT
"WT Def BSS MF-WT"=INTEGER
"Cyc BSS MF-WT"=DOUBLE
"WT Cyc BSS MF-WT"=INTEGER
"Gr BSS MF-WT"=DOUBLE
"WT Gr BSS MF-WT"=DOUBLE
"NT BSS MF-WT"=DOUBLE
"WT NT BSS MF-WT"=INTEGER
"WT BSS (All Ts)"=DOUBLE
"No Cust Ind BSS MF-WI"=DOUBLE
"WI No Cust Ind BSS MF-WI"=DOUBLE
"BSS MF-AE"=DOUBLE
"BSS MF-AE Score"=DOUBLE
"Cross BSS"=DOUBLE
"Cross BSS Score"=DOUBLE

...etc....

"Avg Qtrly NIM"=DOUBLE
"Avg Qtrly PROFITS TO TOTAL LOANS"=DOUBLE
"yoy -0 PROFITS TO LL RESERVE GROWTH"=DOUBLE
"yoy -1 PROFITS TO LL RESERVE GROWTH"=DOUBLE
"yoy -2 PROFITS TO LL RESERVE GROWTH"=DOUBLE
"yoy -3 PROFITS TO LL RESERVE GROWTH"=DOUBLE
"Ann PROFITS TO LL RESERVE GROWTH"=DOUBLE
"Avg Qtrly PROFITS TO LL RESERVE Gr"=DOUBLE
"PROFITS TO LL RESERVE Gr"=DOUBLE
"WT PROFITS TO LL RESERVE Gr"=INTEGER
"WI PROFITS TO LL RESERVE Gr"=INTEGER
"WG PROFITS TO LL RESERVE Gr"=INTEGER
"Rk PROFITS TO LL RESERVE Gr"=INTEGER
[ForClsDatedModel_2015 0702_1-250.csv]
ColNameHeader=True
Format=CSVDelimited
"Ticker"=TEXT
"Fundamental Ticker"=TEXT
"Name"=TEXT
"Custom Industry"=TEXT
"Crescat Industry"=TEXT
"GICS Sector"=TEXT
"GICS Industry Group"=TEXT
"GICS Industry"=TEXT
"GICS Sub-Industry"=TEXT
"Russell Sector"=TEXT
"Currency Being Used"=TEXT
"Report CCY"=TEXT
"Market Status"=TEXT
"IU"=INTEGER
"GM"=INTEGER
"L_fs_S"=DOUBLE
"Or"=INTEGER
"LC"=DOUBLE
"JM"=DOUBLE
"Side"=DOUBLE
"Holding"=DOUBLE
"Theme"=INTEGER

...etc....

"1M Proj Adj Return"=DOUBLE
"Rk 1M Proj Adj Return"=DOUBLE
"3M Proj Adj Return"=DOUBLE
"Rk 3M Proj Adj Return"=DOUBLE
"6M Proj Adj Return"=DOUBLE
"Rk 6M Proj Adj Return"=DOUBLE
"1Y Proj Adj Return"=INTEGER
"Rk 1Y Proj Adj Return"=DOUBLE
"non-neg PE4QT"=DOUBLE
"non-neg PE4QF"=DOUBLE
"non-neg PCFO4QT"=DOUBLE
[ForClsDatedModel_2015 0702_1255-1505.csv]
ColNameHeader=True
Format=CSVDelimited
"Ticker"=TEXT
"WI EP MF-WI"=INTEGER
"WI MF-WI (All Ts)"=INTEGER
"Beta (10 yr or 5 yr)"=DOUBLE
"Rk Beta (10 yr or 5 yr)"=DOUBLE
"Vol30"=DOUBLE
"Rk Vol30"=DOUBLE
"Vol90"=DOUBLE
"Rk Vol90"=DOUBLE

...etc...

What is wrong with that schema.ini file? Thanks!

[ranman256]

If the target table your import the text file into, already has the field defined, there should not be3 a problem.
have the target field set as string and it CANT turn back into a number.

[mountainclimber]

If the target table your import the text file into, already has the field defined, there should not be3 a problem.
have the target field set as string and it CANT turn back into a number.

The table doesn't exist when the program starts and I need the field data types to be determined programmatically at run time. I.e. I think I need to determine the data type with a schema.ini file.

[ranman256]

make the table exist and you wont have the problem.

[mountainclimber]

make the table exist and you wont have the problem.

I'd like to get the schema.ini working. It is a valid way of doing it.

[ssanfu]

From what I have read, I don't think spaces and special characters (ie the dash) are allowed in field names.

See this 2 page article by Doug Steele"
http://www.databasejournal.com/featu...-MS-Access.htm


You could try the method here
ACC: How to Create a Schema.ini File Programmatically
https://support.microsoft.com/en-us/kb/155512

to create the Schema.ini File.



All of the examples I saw had examples that looked like
[Employees.txt]
ColNameHeader=True
CharacterSet=ANSI
Format=TabDelimited
Col1=EmployeeID Integer 3
Col2=LastName Char Width 20




I use import specifications, but I'm going to have to read more about Schema.ini Files


Good luck...

[mountainclimber]

All of the examples I saw had examples that looked like
[Employees.txt]
ColNameHeader=True
CharacterSet=ANSI
Format=TabDelimited
Col1=EmployeeID Integer 3
Col2=LastName Char Width 20

Yes, I have been to that link and a ton of other ones. I have read many times that the Col# part can be omitted when it isn't fixed-width. So that is what I am doing.

In another forum I was told:"So you can omit Col1=, Col2=, etc. as you did, but the = belongs between Col(n) and the column name, not between name and type. So replace the = by space."

I read that to mean I should do this:

"column name here" DOUBLE

If so, that doesn't make any sense for an ini file. Ini files are suppose to have an = sign so the key and the value can be determined via WritePrivateProfileString in VBA. It is not an easy thing for me to change so I want to be as sure as possible.

thank you!

[mountainclimber]

I figured it out and posted an answer here:
http://stackoverflow.com/questions/3...-for-ms-access

Well, I figured part of it out anyway.