Strange issue importing CSV/XLS

[DWEd]

I've been successfully pulling in data and manipulating it for some time without issues, but I've just edited an old script to work with some new data and hit an odd problem.
I was pulling in CSV data using DBquery, but suddenly I started to get very strange outputs from my script. Assuming I'd accidental edited something I shouldn't I spent ages trawling through the code, only to find the DBquery wasn't working.
I've then made a new basic script using the example code and converted the csv to xls to see if that would help, but I still see the same issue.
The original data set is large but I've attached a cut down 35 odd line xls with just a car make and model fields.
As soon as the model field hits text it returns a blank value?
Like I said the original data was csv so no formatting or anything and I've tried setting the xls cell format to text and it makes no difference. I really can't work out why it's stopped working I'm running V14.3.11 and Office 2016.
I've attached the xls and here is the demo script:

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test1.xls;Extended Properties="Excel 8.0;Hdr=Yes;"
DBConnect>connStr,dbH

Let>SQL=select * from [Sheet1$]
DBQuery>dbH,SQL,exceldata,num_recs,num_fields
DBClose>dbH

let>x=1
let>outputdata=Make ~ Model%CRLF%

repeat>x
Let>make=exceldata_%x%_1
Let>model=exceldata_%x%_2
let>outputdata=%outputdata% %make% ~ %model%%CRLF%
let>x=x+1
until>x>35

MessageModal>%outputdata%

[DWEd]

Just done some more testing ....
If I make a really basic spreadsheet like:
Make Model
Car1 Text
Car1 1
Car1 1
Car1 Text
Car1 Text
Car1 1
Car1 Text
Car1 1
Car1 Text
Car2 Text
Car2 Text
Car2 Text

IT seems that if there are 4 or more cells in the top 9 Model rows that are integer then it only returns cells with integer values, otherwise it only returns cells with string values????
maybe I've not come across this before as data was always integer OR text and this is standard behavior, but I really need to pull both integer and string data in. I could inject a character into the data and remove it in macrosheduler as a workaround, but I must be doing something wrong here?

[Marcus Tettmar]

You need a schema file:
https://www.mjtnet.com/blog/2010/01/28/ ... s-as-text/

[DWEd]

Ahh fantastic, that solves my issue. Although doesn't explain why the xls approach didn't work, as you can't have a schema with an xls (I don't think???) so it should have taken the fact I'd formatted the column to be text.
But as you say if I go back to importing a CSV rather than an XLS I can use a schema file and it does work.

For reference here's the script I posted in my initial post revised to create a schema:
let>filelocation=c:\test\
let>csvfile=test1.csv

//delete schema.ini we'll make a new one
IfFileExists>%filelocation%schema.ini
DeleteFile>%filelocation%schema.ini
endif

//read the csv file in without schema
Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%filelocation%;Extended Properties='text;HDR=No;FMT=Delimited'
DBConnect>ConStr,dbH
let>dbstatement=select * from %csvfile%
DBQuery>dbH,dbstatement,csvdata,num_recs,num_fields


//Create schema.ini file
WriteLn>%filelocation%Schema.ini,wres,[%csvfile%]
Let>k=0
Repeat>k
Let>k=k+1
EditIniFile>%filelocation%Schema.ini,%csvfile%,Col%k%,Col%k% Text
Until>k=num_fields

//now requery using schema.ini file
DBQuery>dbH,dbstatement,csvdata,num_recs,num_fields

DBClose>dbH

//check the data
let>x=1
let>outputdata=Make ~ Model%CRLF%
repeat>x
Let>make=csvdata_%x%_1
Let>model=csvdata_%x%_2
let>outputdata=%outputdata% %make% ~ %model%%CRLF%
let>x=x+1
until>x>35
MessageModal>%outputdata%