Convert file

[Gilbert Pereira]

I have a .txt text file in column form. I want to covert this file into a CSV file to import to Excel.Does any anyone have any ideas.
This is partial of the text file :


TEST2EDI Allocated 32,239,616 Free: 1,211,208



Partial of Excel sheet:

TEST2EDI Allocated 32,239,616
Free 1,211,208
Used 31,028,408
%Used 96.24%
Growth


TEST2EIX Allocated 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000 2,048,000
Free 1,890,800 1,890,800 1,890,800 1,890,800 1,890,800 1,890,800 1,890,800 1,890,728 1,890,728 1,890,728 1,890,728 1,890,584 1,890,584 1,890,584 1,890,584 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,368 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,296 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,152 1,890,080 1,890,080 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,890,008 1,889,864 1,889,864 1,889,864 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,648 1,889,504 1,889,504 1,889,504 1,889,504 1,889,504 1,889,504 1,889,504 1,889,504 1,889,504 1,889,504 1,889,432 1,889,432 1,889,432 1,889,432 1,889,432 1,889,432 1,889,432 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,360 1,889,288 1,889,288 1,889,288 1,889,288 1,889,288 1,889,288 1,889,288 1,889,288 1,889,288 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568 1,888,568
Used 157,200 157,200 157,200 157,200 157,200 157,200 157,200 157,272 157,272 157,272 157,272 157,416 157,416 157,416 157,416 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,632 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,704 157,848 157,848 157,848 157,848 157,848 157,848 157,848 157,848 157,848 157,848 157,848 157,848 157,920 157,920 157,992 157,992 157,992 157,992 157,992 157,992 157,992 157,992 157,992 157,992 157,992 157,992 157,992 157,992 158,136 158,136 158,136 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,352 158,496 158,496 158,496 158,496 158,496 158,496 158,496 158,496 158,496 158,496 158,568 158,568 158,568 158,568 158,568 158,568 158,568 158,640 158,640 158,640 158,640 158,640 158,640 158,640 158,640 158,640 158,640 158,640 158,640 158,640 158,712 158,712 158,712 158,712 158,712 158,712 158,712 158,712 158,712 159,432 159,432 159,432 159,432 159,432 159,432 159,432 159,432 159,432 159,432 159,432 159,432
%Used 7.68% 7.68% 7.68% 7.68% 7.68% 7.68% 7.68% 7.68% 7.68% 7.68% 7.68% 7.69% 7.69% 7.69% 7.69% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.70% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.71% 7.72% 7.72% 7.72% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.73% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.74% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.75% 7.78% 7.78% 7.78% 7.78% 7.78% 7.78% 7.78% 7.78% 7.78% 7.78% 7.78% 7.78%
Growth 0 0 0 0 0 0 72 0 0 0 144 0 0 0 216 0 0 0 0 0 0 0 0 0 0 0 0 0 72 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 144 0 0 0 0 0 0 0 0 0 0 0 72 0 72 0 0 0 0 0 0 0 0 0 0 0 0 0 144 0 0 216 0 0 0 0 0 0 0 0 0 0 0 0 0 144 0 0 0 0 0 0 0 0 0 72 0 0 0 0 0 0 72 0 0 0 0 0 0 0 0 0 0 0 0 72 0 0 0 0 0 0 0 0 720 0 0 0 0 0 0 0 0 0 0 0

TEST2FIN Allocated 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128 8,880,128
Free 8,167,384 8,167,384 8,167,312 8,167,312 8,166,664 8,166,592 8,166,592 8,166,592 8,166,592 8,166,592 8,166,520 8,166,448 8,166,448 8,166,448 8,166,448 8,166,376 8,166,160 8,166,160 8,166,160 8,166,088 8,166,088 8,166,088 8,165,512 8,165,008 8,164,936 8,164,936 8,164,864 8,164,864 8,164,864 8,164,864 8,164,864 8,164,864 8,164,792 8,164,792 8,164,792 8,164,792 8,164,792 8,164,792 8,164,792 8,164,792 8,164,792 8,164,792 8,164,792 8,164,216 8,164,216 8,164,144 8,164,072 8,164,000 8,164,000 8,164,000 8,163,928 8,163,928 8,163,928 8,163,272 8,163,128 8,163,128 8,163,128 8,163,128 8,163,136 8,163,136 8,163,136 8,163,104 8,163,104 8,163,104 8,163,032 8,162,960 8,162,888 8,162,832 8,162,832 8,162,832 8,162,832 8,162,760 8,162,760 8,162,688 8,162,616 8,162,544 8,162,544 8,162,544 8,162,544 8,162,544 8,162,472 8,161,504 8,161,504 8,161,504 8,161,504 8,161,504 8,161,512 8,161,440 8,161,440 8,161,440 8,161,368 8,161,368 8,018,752 7,927,496 7,927,440 7,927,440 7,927,200 7,927,200 7,927,200 7,927,056 7,926,768 7,926,320 7,926,104 7,925,528 7,925,456 7,925,336 7,924,976 7,924,688 7,924,328 7,923,760 7,922,392 7,922,400 7,922,328 7,921,608 7,920,600 7,920,600 7,919,952 7,919,664 7,919,664 7,919,664 7,919,520 7,917,632 7,917,288 7,916,864 7,916,576 7,916,408 7,914,832 7,914,336 7,914,048 7,913,352 7,912,784 7,912,784 7,912,784 7,912,536 7,885,824 7,887,184 7,885,816 7,885,248 7,885,248 7,885,232 7,884,680 7,880,336 7,879,784 7,878,392 7,848,936 7,848,968 7,848,824
Used 712,744 712,744 712,816 712,816 713,464 713,536 713,536 713,536 713,536 713,536 713,608 713,680 713,680 713,680 713,680 713,752 713,968 713,968 713,968 714,040 714,040 714,040 714,616 715,120 715,192 715,192 715,264 715,264 715,264 715,264 715,264 715,264 715,336 715,336 715,336 715,336 715,336 715,336 715,336 715,336 715,336 715,336 715,336 715,912 715,912 715,984 716,056 716,128 716,128 716,128 716,200 716,200 716,200 716,856 717,000 717,000 717,000 717,000 716,992 716,992 716,992 717,024 717,024 717,024 717,096 717,168 717,240 717,296 717,296 717,296 717,296 717,368 717,368 717,440 717,512 717,584 717,584 717,584 717,584 717,584 717,656 718,624 718,624 718,624 718,624 718,624 718,616 718,688 718,688 718,688 718,760 718,760 861,376 952,632 952,688 952,688 952,928 952,928 952,928 953,072 953,360 953,808 954,024 954,600 954,672 954,792 955,152 955,440 955,800 956,368 957,736 957,728 957,800 958,520 959,528 959,528 960,176 960,464 960,464 960,464 960,608 962,496 962,840 963,264 963,552 963,720 965,296 965,792 966,080 966,776 967,344 967,344 967,344 967,592 994,304 992,944 994,312 994,880 994,880 994,896 995,448 999,792 1,000,344 1,001,736 1,031,192 1,031,160 1,031,304
%Used 8.03% 8.03% 8.03% 8.03% 8.03% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.04% 8.05% 8.05% 8.05% 8.05% 8.05% 8.05% 8.05% 8.05% 8.05% 8.05% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.06% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.07% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.08% 8.09% 8.09% 8.09% 8.09% 8.09% 8.09% 8.09% 8.09% 8.09% 8.09% 8.09% 9.70% 10.73% 10.73% 10.73% 10.73% 10.73% 10.73% 10.73% 10.74% 10.74% 10.74% 10.75% 10.75% 10.75% 10.76% 10.76% 10.76% 10.77% 10.79% 10.79% 10.79% 10.79% 10.81% 10.81% 10.81% 10.82% 10.82% 10.82% 10.82% 10.84% 10.84% 10.85% 10.85% 10.85% 10.87% 10.88% 10.88% 10.89% 10.89% 10.89% 10.89% 10.90% 11.20% 11.18% 11.20% 11.20% 11.20% 11.20% 11.21% 11.26% 11.26% 11.28% 11.61% 11.61% 11.61%
Growth 0 72 0 648 72 0 0 0 0 72 72 0 0 0 72 216 0 0 72 0 0 576 504 72 0 72 0 0 0 0 0 72 0 0 0 0 0 0 0 0 0 0 576 0 72 72 72 0 0 72 0 0 656 144 0 0 0 -8 0 0 32 0 0 72 72 72 56 0 0 0 72 0 72 72 72 0 0 0 0 72 968 0 0 0 0 -8 72 0 0 72 0 142,616 91,256 56 0 240 0 0 144 288 448 216 576 72 120 360 288 360 568 1,368 -8 72 720 1,008 0 648 288 0 0 144 1,888 344 424 288 168 1,576 496 288 696 568 0 0 248 26,712 -1,360 1,368 568 0 16 552 4,344 552 1,392 29,456 -32 144

[Jon Pinkley]

Gilbert,

It isn't exactly clear what you want to do.

What created the data?

I selected the text starting with the line that stared with TEST2EIX to the end of your post, pasted into notepad, saved as .txt and opened with Excel (2000). This brought up a text import wizard, and I selected "Delimited" then clicked next, selected "Space" and everything got selected in what appears to be the correct order. Note that the "growth" rows have 1 less element than the others, and that it is growth to next column, not growth from last. (I would have expected it to have an additional 0 at the beginning).

You can save that as .csv or .xls or whatever. That's one way to convert the file.

If you are looking for the file to be created correctly to begin with, then you need to know what the file should look like. You could save the file from excel in .csv and take a look at it.

There are ways to convert the text file on VMS, but since there is a relatively easy way to load the file into excel as is, unless you do this frequently, I would just go through the import wizard. It's a few clicks, but not a big deal.

Jon

P.S. I am attaching the output in the .csv format that excel created, but I have saved as a .txt file so it will be displayed as text.

[Phil.Howell]

if you try to import a text file into excel using Data, Import External Data, Import Data, then it should give you the option to import it as "fixed-width" or even space delimited. If you have ms access available, you can do it in a similar way, but it also allows you to retain the import specification (field names, length, offset) for future use.
Phil

[Gilbert Pereira]

I did not mean to send all that data. I created the text file using a command procedure.

The secound part is part of the spread sheet , one column with the labels to the right in another column.

After some thought, I beleive that I just should write a procedure and write out the file with dilimiters to import to Excel so that it will update the required cells.

[Jon Pinkley]

If you are going to write it in the command procedure to be imported into excel, then I would just save two rows per device, allocated and used, everything else can be derived by excel.

Jon

[Hoff]

While CSV is best avoided whenever possible due to its inherent flaws, here is how to deal with CSV -- if you really have to:

http://64.223.189.234/node/644

In general, libcsv or the Perl CSV modules or such are what you want you want to use, if you're stuck using some product that requires CSV.

You'll end up writing some DCL, C or C++, perl, php or core data -- whatever language you are comfortable coding in -- to get your current file over into CSV or XML or such. Your code reads this data file, then uses C sscanf or perl or php or such code to sklork the data file in, and toss it out into libcsv or such. SMOP.

With an experienced (pick your language) programmer on OpenVMS, this is probably about an hour's worth of coding and testing and documentation time. Quick-and-dirty would be less.

In DCL, this is nested loops of READ commands and f$element calls to process the lines of data.

Microsoft Windows systems can have VB or other such tools available, which could also be used to convert the file directly on Windows.

Getting the file itself (either this one, or the resulting CSV file) over to Microsoft Windows means you use Wordpad (which can read more formats than Notepad), or using CONVERT on OpenVMS to move from the typical sequential file formats on OpenVMS over to the Stream or StreamLF typically for use on Windows.

Stephen Hoffman
HoffmanLabs LLC

[John Gillings]

Gilbert,

If this is timeline data, why not output it in T4 format so it can be integrated with other T4 data on your system?

Lose the % signs and commas in the numbers, start each line with a timestamp, and rotate your data 90 degrees, so column headings rather than row labels.

Lookup the T4 data format on the HP OpenVMS site, or at www.trendsthatmatter.com