Facebook

Course Name Start Date Time Duration Registration Link
No Training Programs Scheduled ClickHere to Contact
Please mail To sudhakar@qtpsudhakar.com to Register for any training

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