Getting error message when trying to read CSV and XLSX file

[deepa_samota]

I am using following script code for reading CSV and XLSX file.

Issue with Excel File :

JavaScript runtime error.
External table is not in the expected format

function GetCol1Unique()
{
var MyExcelRecords;
var fileName = "C:\\Users\\"+Sys.UserName+"\\Downloads\\data.xlsx";
MyExcelRecords = DDT.ExcelDriver(fileName,"Export",true)
MyExcelRecords.ADOCommandObject.CommandText = "SELECT DISTINCT [Policy ID] from [Export$]";
var rowCount=0;
while (!MyExcelRecords.EOF())
{
rowCount++;
MyExcelRecords.Next();
}
Log.Message(rowCount);
DDT.CloseDriver(MyExcelRecords.Name);
}

Issue with CSV File
In CSV file reading we are getting error :Database object is read only”.

var latestFile.Path = "C:\\Users\\"+Sys.UserName+"\\Downloads\\data.csv";

if (aqFile.Exists(latestFile.Path))
{
var csvFile =DDT.CSVDriver(latestFile.Path)
var count =0;
while (!csvFile.EOF())
{
count++;
csvFile.Next();
}

Log.Message(count);
DDT.CloseDriver(csvFile.Name);
}

[rraghvani]

Excel example,

CSV example,

[deepa_samota]

Thanks for the reply

[deepa_samota]

Thanks for the reply.

Please find attached csv file which I am using to read data (using script trying to get row count)

It is returning double row count.

[rraghvani]

The CSV file is not in a format that DDT.CSVDriver likes. If I change the file format, then it works,

[deepa_samota]

Is any way to save file in correct format using script.

[rraghvani]

I saved the file using Excel. You will have to write a script to remove quotes and replace tabs with a comma.

[deepa_samota]

Could you help me out to give some outline to write script ?

[rraghvani]

See ReadLine Method and replace mthod.

[deepa_samota]

I have tried but no luck to save file in correct format for further access.