I have text files in one folder ,i need to convert these files to excel files …where these excel files are used by the macro,ANY VBA or batchscript code to convert .txt to .xls
ample text file(tab delimited):
first_name middle_name last_name
—————- ——————– —————-
sam john abraham
ben tom william
jan lisa mary
Its not plain text files,its a tab delimited text files.
I tried with windows script
rename d:/test/*.txt *.xls
It works but while opening the .xls document its shows a
WARNING:
The file you are trying to open,’nmae.xls’,is in a different format than specified by the file extension.Verify that the file is not corrupted and is from a trusted source before opening the file.Do you want to open the file now?
if we press yes it opens the file with exact data but i dont want this warning to come in.
About the only thing you can do is convert them to CSV, which will import into Excel.
for /f “tokens=1-3” %%a in (infile.txt) do >> outfile.csv echo %%a,%%b,%%c
(assuming you just have three fields).
Using vbscript, workbooks.openastext will allow you to open a “raw” text file, using various options. (see:
http://msdn.microsoft.com/en-us/lib…
)
xl.workbook.openastext(“Inpfile”,,,1,4142,,True)
opens “Inpfile” as delimited (vs fixed-width), no quotes, tab delimited.
Then you don’t have to do the csv conversion.
followup note:
My version of excel is quite old, and did not support the above for testing, but it did allow this, and it worked on tab-separated fields with no surrounding quotes:
‘==== begin vbscript
Set xl = WScript.CreateObject(“Excel.application”)
xl.Visible = TRUE
xl.displayalerts=False
set books=xl.workbooks
books.open (“c:\vbs\a1”)
Steps to convert content from a TXT or CSV file into Excel
Open the Excel spreadsheet where you want to save the data and click the Data tab.
In the Get External Data group, click From Text.
Select the TXT or CSV file you want to convert and click Import.
Select “Delimited”. …
Click Next.