Importing a text file data into an excel file

[ganesha]

Hi,

I have a requirement of importing a text file which contains data in
tabular format into excel. I am able to do the same with below code.
Does anyone has a better alternative as I am facing issues of
sometimes the data moving into different columns resulting in wrong
data with the below code :

columnheadings=Array("Item Name","Buyer","Qty")
startrow=18
Call UdfImporttxtFiletoExcel("c:\recontextfile.txt","c:
\recontextfile.xls",columnheadings,startrow)
Public function
UdfImporttxtFiletoExcel(txtFilename,excelFilename,columnheadings,startrow)
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.DisplayAlerts = False
ExcelObj.Workbooks.Add
ExcelObj.Visible = true
ExcelObj.Workbooks.OpenText txtFilename,,startrow,1
ExcelObj.Columns("A:A").select
ExcelObj.selection.TextToColumns,2
xlcolum=1
For arRow=0 to ubound(columnheadings)
ExcelObj.Cells(1,xlcolum) =
columnheadings(arRow)
xlcolum=xlcolum+1
next
Set NewSheet = ExcelObj.Sheets.Item(1)
ExcelObj.Rows("1:1").Select
ExcelObj.Selection.Font.Bold=True
ExcelObj.Rows("1:65000").Select
ExcelObj.Selection.Font.size=8
ExcelObj.ActiveWorkbook.SaveAs excelFilename,1
ExcelObj.ActiveWorkbook.close
Set ExcelObj = Nothing
end function

e.g. My text file contains
Item Name Buyer Qty
-------------- ---------- ----------
15-3292-01 Yellig, Thomas 25
15-3292-01 Yellig, Thomas john 50

Sometimes the function will force the Buyer name : Yellig, Thomas john
to trespass into the 3rd column in excel so the Qty will be pasted in
4th column.

Has anyone got a workaround for importing txt files into xls or a
resolution for above issue?

Regards,
Ganesh

[Shirish K]

Hi Ganesha,

 

QTP has inbuild import capability for xls and tabulated txt file. Just use following single line code will work for your tabulated txt file.

 

Datatable.Import "C:\123.txt"

 

123.txt shold have proper tabulated format. To get better tabulated formated .txt file create first excel file with data you wanted to import. and saveas the same file with Save As Type "Text (Tab delimited) " and use this saved .txt file in above code.

 

Hope this will work for u.

 

-Shirish

 

[Ganesh muralidharan]

1. Datatable.Import "C:\123.txt" is only importing the txt file into the local data sheet.
It is in text file format only. On converting from text to columns I am getting issues with the data.

 

2. I am not clear with your second workaround. Kindly clarify.

 

Meanwhile I have attached a sample txt file for your reference. Kindly look into it and update me.
 

[Biswajit Agarwal]

Dear All,

 

This issue seems to be a good one.. can anybody please answer this..

 

On 7/12/07, ganesha <mgkg...@gmail.com> wrote:

[Ganesh muralidharan]

Finally I resolved this issue by passing column width as below thru Array

Public function UdfImporttxtFiletoExcelReconReport(txtFilename,excelFilename,columnheadings,startrow)

Set ExcelObj = CreateObject(" Excel.Application")
ExcelObj.DisplayAlerts = False

ExcelObj.Workbooks.OpenText txtFilename,,startrow,2,,,,,,,,,Array(Array(0, 2), Array(20, 2), Array(70, 1), Array(80, 1))
ExcelObj.Columns("D:D").select
ExcelObj.selection.TextToColumns ,2

xlcolum=1
For arRow=0 to ubound(columnheadings)
ExcelObj.Cells(1,xlcolum) = columnheadings(arRow)
xlcolum=xlcolum+1
next
Set NewSheet = ExcelObj.Sheets.Item (1)
ExcelObj.Rows("1:1").Select
ExcelObj.Selection.Font.Bold=True
ExcelObj.Rows("1:65000").Select
ExcelObj.Selection.Font.size=8
ExcelObj.ActiveWorkbook.SaveAs excelFilename,1
ExcelObj.ActiveWorkbook.close
Set ExcelObj = Nothing
end function