Facebook

For any corporate training requirements Please mail To sudhakar@qtpsudhakar.com
Course Name Date Time Duration Type Location
An important update from QtpSudhakar. I stopped giving classroom training in MindQ Systems. I have bigger plans and I want to put an end to regular classroom training because very less people are practicing. From now onwards every session of mine is going to have explanation and practice. Everyone should practice in presence of mine and should go out of classroom with practical knowledge. So I started my own institute AnyTechLabs in Madhapur. Please reach out to +91-8328152362 for course details. Visit AnyTechLabs.com for address details.
Selenium + Cucumber 22nd September 9 AM IST to 2.00 PM IST 4 WeekEnds Theory + Practical AnyTechLabs
Selenium + Cucumber 8th October 7.30 AM IST to 9.00 AM IST 45 Days Theory+Practical AnyTechLabs

A book "Cracking the QTP interview" authored by Sudhakar.Read More. Buy From FlipKart

Thursday, July 23, 2009

Import CSV File Data in to QTP Datatable

'************************************************************

Method1
'************************************************************
If the CSV file is having data in below style then use this method.
'**************************************
UserName, EmailId, PhoneNumber               
'Treating this row as Columns
sudhakar, ksrbalu@gmail.com, 1234567890
balu, ksr_balu@yahoo.com, 9999999999

'**************************************
'************************************************************
Function ImportCsvFiletoDatatable(CsvFilePath,SheetName,HeaderDelimiter)
Dim filePath
Dim fso
Dim f
Dim fData
Dim arrData
Dim CsvValue
Dim CsvSheet
Dim CsvFirstLine
Dim CsvColumns
Dim ColumnIndex
Dim rIndex
Dim cIndex
 
filePath=CsvFilePath    'Specify file Path
 
'Open CSV File using File System Object
Set fso=createobject("scripting.filesystemobject")
Set f  = fso.OpenTextFile(filePath)
 
CsvFirstLine=f.readline    'Treating like first line is the column names
 
CsvColumns=split(CsvFirstLine,HeaderDelimiter)    'Split the line using HeaderDelimiter
 
Set CsvSheet=DataTable.GetSheet(SheetName)    'Get the Specified sheet
 
'Add the splitted values as Datatable Columns
For ColumnIndex=lbound(CsvColumns)  to ubound(CsvColumns)
CsvSheet.addparameter CsvColumns(ColumnIndex),""
Next
 
 
While not f.AtEndOfStream
 
rIndex=f.Line-1    'Specify Row index
fData=f.ReadLine    ' Read CSV File Line
arrData=split(fData,",")    'Split the line
cIndex=1    'Specify Column Index
CsvSheet.SetCurrentRow(rIndex)    'Set Row in the Datatable
 
' Add values in Datatable
For Each CsvValue In arrData
CsvSheet.getparameter(cIndex).value=CsvValue
cIndex=cIndex+1
Next
 
Wend
 
f.Close
Set fso=Nothing
 
End Function
'************************************************************
 
'Calling this Function
ImportCsvFiletoDatatable "C:\Sample.csv","Action1","," 

'************************************************************
Method2
'************************************************************
If the CSV file is having data in below style then use this method.
'**************************************
UserName: 
sudhakar,balu
EmailId:ksrbalu@gmail.com, ksr_balu@yahoo.com
PhoneNumber: 12345678909999999999
'**************************************
'************************************************************
 
Function ImportCsvFiletoDatatable(CsvFilePath,SheetName,HeaderDelimiter)
 
Dim filePath
Dim fso
Dim f
Dim fData
Dim arrData
Dim CsvValue
Dim CsvSheet
Dim CsvFirstLine
Dim CsvColumns
Dim ColumnIndex
Dim rIndex
Dim cIndex
 
filePath=CsvFilePath    'Specify file Path
 
'Open CSV File using File System Object
Set fso=createobject("scripting.filesystemobject")
Set f  = fso.OpenTextFile(filePath)
 
Set CsvSheet=DataTable.GetSheet(SheetName)    'Get Sheet based on the name
 
While not f.AtEndOfStream
 
CsvLine=f.readline
arrData=split(CsvLine,HeaderDelimiter,2)    'Split the line
 
Set CsvParameter=CsvSheet.addparameter(arrData(0),"") 'Adding first word as datatable column
 
For CsvArrIndex=1 to ubound(arrData)
 
CsvData=split(arrData(CsvArrIndex),",")
rIndex=1
For  Each CsvValue In CsvData
CsvSheet.setcurrentrow(rIndex)
CsvParameter.value=CsvValue    'Adding data to the datatable column
rIndex=rIndex+1
Next
Next
 
Wend
 
f.Close
Set fso=Nothing
 
End Function
'************************************************************
 
'Calling this Function
ImportCsvFiletoDatatable "C:\Sample.csv","Action1",":" 

6 comments :

  1. Hi
    Below details having CSV file, Can you please write code for those Requirement?
    EMP Emp Name
    12345 Rahul
    12346 Kiran
    12345 Kiran
    12345 Kiran
    12346 Rahul
    12347 Sudhakar
    12347 Sudhakar
    12345 Kiran

    IN CSV file, EMP number is duplicate values, i dont want use duplicate values. Excuate the Unique values

    ReplyDelete
  2. Cool. Worked like magic:)

    ReplyDelete
  3. Very nice. Works great except my csv data has comma imbedded in a field so when i do a split(data, ",") things get all messed up.

    oh well. thanks.

    ReplyDelete
  4. It is possble to Add the new column in medle of the data table after import the csv file.

    ReplyDelete