Topic starter
29/11/2018 12:41 am
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:TempReport.xlsx;”)
excel.Execute(“select * from [Worksheet1$A1]”)
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).
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.
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.
Contact Our Team
