Reading from a .csv and .xls file via ODBC

[AnSt]

Example:

class ReadFromFile : Test
Main()
Database csv
csv.Connect("Driver="{Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Temp\;")
csv.Execute("select * from Report.csv")
List<List<Object>> readLines
List<Object> readLine
Boolean end = false
while(!end)
readLine = {}
csv.FetchToList(readLine)
if (readLine.Count() != 0)
readLines.Add(readLine)
else
end = true
csv.Disconnect()
Print(readLines)

Database excel
excel.Connect("Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);Dbq=C:\Temp\Report.xlsx;")
excel.Execute("select * from [Worksheet1$A1:M10]")
readLines = {}
end = false
while(!end)
readLine = {}
excel.FetchToList(readLine)
if (readLine.Count() != 0)
readLines.Add(readLine)
else
end = true
excel.Disconnect()
Print(readLines)

ATTENTION:
If you notice information lacks in the Print() statement may be you have to set up an own System DSN in the ODBC Data Source Administrator.
For the example I set up a "Microsoft Text Driver (*.txt; *.csv) DSN with directory "C:\Temp" and the format "CSV" for Tables "default" and Characters "OEM" (Rows to Scan: 25).

[RochTourigny]

Thank you very much!
Our application exports data to xlsx file format, I was unable to verify the files with the File compare command nor with the use of Excel.
Using your method works like a charm, I can focus on a section of the worksheet cells where important data is present.

[RochTourigny]

I recently added more test to a plan and ran into some issues.
After 32 test I had the following:
DatabaseException: (08004) [Microsoft][ODBC Excel Driver] Too many client tasks.


I now use the following connect string
excel.Connect ("Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);Provider=Microsoft.ACE.OLEDB.12.0;Dbq={FullPathToFile};Extended Properties='{ExtendedProperty};HDR=YES;'")

where ExtendedProperty = "Excel 8.0" for xls or "Excel 12.0 Xml" for xlsx

The ODBC driver is:
"microsoft access database engine 2010" 32 bit or 64 bit depending on your Excel version.