China DOS Union

How to directly convert a TXT file to an XLS file using batch processing

report.txt

1 13 45 0 4500 15615
2 13 45 0 4500 15600
3 26 255 0 25500 15601
4 18 81 0 8100 15602
5 29 188 0 18800 15605
6 25 150 0 15000 15606
7 84 333 12 33900 15609
8 20 105 1 10550 15610
9 26 128 0 12800 15611
10 14 149 0 14900 15612
11 5 33 0 3300 15613
12 0 0 0 0 15616
13 48 190 16 19800 15617
14 321 1702 29 171650

[ Last edited by xycoordinate on 2007-2-23 at 11:34 AM ]

xls
What format do you see when you open it with Notepad?

Can the numbers in the txt be accurately given to the xls file?

Of course, if the format is regular.

Now there's no time to write code. Here's an idea:

First, replace double spaces with single spaces, then replace single spaces with {TAB} + single space ({TAB}: tab character)

Then directly open the TXT file with Excel

After looking at the original existing xls document, it seems not in plain text form...

Then it's the simulated input on the 5th floor...

The script can reference this object Excel.Application

This shouldn't be possible. An XLS file is garbled when opened with Notepad.

sed "s/^*//;s/\+/\t/g" report.txt> result.xls

For downloading and using sed, please search the forum with "sed"

[ Last edited by amao on 2007-2-24 at 03:22 AM ]

If separated by spaces, tabs, or commas, you can directly open the TXT file with EXCEL, and EXCEL will ask you how to separate.
PS: Tabs are well separated. Copy to the clipboard, then open EXCEL and paste. If so, you need to use VBS.

The generated "test.xls" can be directly opened with Excel

@echo off

setlocal EnableDelayedExpansion

cd.>test.xls

for /f "delims=" %%n in (test.txt) do (

  set "str=%%n"

  set "str=!str:  = !"

  set "str=!str: ={TAB} !"

echo !str!

>>test.xls echo !str!

)

pause

{TAB}: Keyboard TAB tab

PS: Is there a problem with the last line of your data "14 321 1702 29 171650 ", missing the third data?

[ Last edited by zh159 on 2007-2-23 at 06:16 PM ]

Originally posted by zh159 at 2007-2-23 02:56 PM:
The generated "test.xls" can be directly opened with Excel

@echo off
setlocal EnableDelayedExpansion
cd.>test.xls
for /f "delims=" %%n in (test.txt) do (
set "str=%%n"
set "str=!str: = !"
set "str=!str: ={TAB} !"
echo !str!
>>test.xls echo !str!
)
pause

First replace double spaces with single spaces, then replace single spaces with {TAB} + single space ({TAB}: tab character)

Thank you!!!
NNNNNN!!!!!!!!!!!!!

Also:

cd.>test.xls

set "str=!str: = !"
set "str=!str: ={TAB} !"

The meaning is not very clear???
Is it this?

set /?
Environment variable substitution has been enhanced as follows:

%PATH:str1=str2%

This will expand the PATH environment variable, replacing each "str1" with "str2" in the expanded result.
To effectively remove all "str1" from the expanded result, "str2" can be empty.
"str1" can start with an asterisk; in this case, "str1" will match from the start of the expanded result to the first occurrence of the remaining part of "str1".

I can directly use tab to separate columns and it's OK!!!!


The 20070131.txt has the following format, directly changing the extension to xls will be as I wish!!!
1 13 45 0 4500 15615
2 13 45 0 4500 15600
3 26 255 0 25500 15601
4 18 81 0 8100 15602
5 29 188 0 18800 15605
6 25 150 0 15000 15606
7 84 333 12 33900 15609
8 20 105 1 10550 15610
9 26 128 0 12800 15611
10 14 149 0 14900 15612
11 5 33 0 3300 15613
12 0 0 0 0 15616
13 48 190 16 19800 15617
14 321 1702 29 171650

[ Last edited by xycoordinate on 2007-2-23 at 10:53 PM ]

Hehe, this is also simple`````

@echo off
type report.txt echo >>report.xls

Originally posted by chainliq at 2007-2-23 09:46 PM:
Hehe, this is also simple.`````

@echo off
type report.txt echo >>report.xls

F:\>type report.txt echo >>report.xls

report.txt


The system cannot find the specified file.
Error occurred while processing: echo

Why does it report an error?????

The report.xls is indeed generated, but the format is incorrect!!!