Micro Focus QTP (UFT) Forums
CSV to EXCEL conversion - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: CSV to EXCEL conversion (/Thread-CSV-to-EXCEL-conversion)

CSV to EXCEL conversion - neerukonda9 - 10-22-2009

Hi all

I am running csv to excel conversion code in QTP9.5 , the execution and results are fune except that some of the "date" field data in CSV is converted to "numbers" in excel.

Any comments or help is appreciated.

Many thanks in advance

below is the code I am using

'Function Name : CSV to excel
'Fucntion Description: 'VBScript to convert CSV file to Excel - number format issue
'Test data used : test data
'Date : 21/04/09
'Written by :
'*******************************************************************
Code:
Function csvtoexcel

Dim fldr, f, file,strOrigFile, strFile, fso, strDirectory
Dim objExcel

strOrigFile = "C:\QTPV6\results\actual\200906019043a.csv"
'******************************************************
'CONVERTS THE FILE TO AN EXCEL FILE
'******************************************************
Code:
strFile = replace(strOrigFile,".csv",".xls")

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open strOrigFile

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Application.Quit

'******************************************************
'FORMAT THE EXCEL FILE AND SAVE IT
'******************************************************

Code:
objExcel.Workbooks.Open strFile

objExcel.columns("A:A").numberformat="0"
objExcel.columns("C:C").numberformat="0"


objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

End Function


RE: CSV to EXCEL conversion - venkatbatchu - 10-23-2009

Hi,
Thanks for the posting here,
i tried with your cide but i oserved that i am able to render the date field instead of number (This might be the setting in Excel --Format Cells-->Number-->Date-->Select the appropriate format)
If possible could you attach the .cv files also

RE: CSV to EXCEL conversion - Saket - 10-23-2009

@neerukonda9 - always wrap your code to make your post more readable. refer Mycode section in help.