how to convert a text file to excel file?

August 30, 2012 at 10:17:47
Specs: Windows XP
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

See More: how to convert a text file to excel file?

Report •


#1
August 31, 2012 at 04:11:03
I don't think plain text files can be converted to Excel files.

Report •

#2
August 31, 2012 at 05:17:35
About the only thing you can do is convert them to CSV, which will import into Excel. Post one of your text files so we can see if we can help.

Tony


Report •

#3
August 31, 2012 at 09:34:04
sample text file(tab delimited):

first_name middle_name last_name
---------------- -------------------- ----------------
sam john abraham
ben tom william
jan lisa mary


no problem if we can convert from .csv to excel also...we will ask the client to load the files in csv format.


Report •

Related Solutions

#4
August 31, 2012 at 09:35:10
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.


Report •

#5
August 31, 2012 at 10:25:39
This may help.

Tony


Report •

#6
September 1, 2012 at 08:07:53
Can't you just open them one by one in Excel.

Excel will see that they are not regular xls files and suggest you open them as delimited, then you tick the box for the type of delimiter you have. And voila, you can save it as a regular xls file.

Nigel

Wind slow


Report •

#7
September 1, 2012 at 10:24:11
No when i open the excel it always shows a warning message which i have mentioned earlier.when i click yes,for the warning message...it opens up with correct data watever was present in .txt

I dont want anything manual,its all supposed to be automated.


Report •

#8
September 1, 2012 at 10:31:35
Tony,
you have any resolution for this?

Report •

#9
September 2, 2012 at 20:54:09
don't rename the files to ".xls" extension. make it ".csv", and put commas between each field (in front of the TAB, unless you remove the tabs entirely). A fairly simple batchfile can replace the tabs with commas:
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")


Report •


Ask Question