Convert text into Excel
 

I have a text file created and wish to create a MS-Excel spreadsheet
w/columns the 1st column should be static containing row information
while the row could have variable data to append.

See Sample File and compare output file: SInce newbie to excel VBA
stuff help/guidance will be appreciated.


Sample File:
-----------------
Client: PSHBGMCGAPP01
Backup ID: PSHBGMCGAPP01_1101705374
Policy: McGregorServersDailyBackup
Policy Type: MS-Windows-NT
Sched Label: MonthlyFull
Schedule Type: Full Backup
Retention Level: 6 months (6)
Backup Time: 11/29/2004 00:16:14
Elapsed Time: 000:36:58
Expiration Time: 06/03/2005 01:16:14
Compressed: no
Encrypted: no
Kilobytes: 11845986
Number of Files: 26292
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Client: PSHBGMCGAPP01
Backup ID: PSHBGMCGAPP01_1101531734
Policy: McGregorServersDailyBackup
Policy Type: MS-Windows-NT
Sched Label: DailyIncremental
Schedule Type: Differential Incremental Backup
Retention Level: 6 weeks (2)
Backup Time: 11/27/2004 00:02:14
Elapsed Time: 000:11:15
Expiration Time: 01/08/2005 00:02:14
Compressed: no
Encrypted: no
Kilobytes: 369501
Number of Files: 2202
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Required output:
================================================== ==============================
col 1 Col 2 Col 3
Client PSHBGMCGAPP01 PSHBGMCGAPP088
Backup ID PSHBGMCGAPP01_1101705374
PHSNSNNNS01_1919
Policy: McGregorServersDailyBackup XXXX
Policy Type: MS-Windows-NT XXXX
Sched Label: MonthlyFull XXXX
Schedule Type: Full Backup XXXX
Retention Level: 6 months (6)
Backup Time: 11/29/2004 00:16:14
Elapsed Time: 000:36:58
Expiration Time: 06/03/2005 01:16:14
Compressed: no
Encrypted: no
Kilobytes: 11845986
Number of Files: 26292
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Convert text into Excel
 

go to File=Open and open the file. This will take you through the text
import wizard. In the first screen select fixed width. Then if necessary
designate where you want you columns.

--
Regards,
Tom Ogilvy

"Aqua Flow" wrote in message
om...
I have a text file created and wish to create a MS-Excel spreadsheet
w/columns the 1st column should be static containing row information
while the row could have variable data to append.

See Sample File and compare output file: SInce newbie to excel VBA
stuff help/guidance will be appreciated.


Sample File:
-----------------
Client: PSHBGMCGAPP01
Backup ID: PSHBGMCGAPP01_1101705374
Policy: McGregorServersDailyBackup
Policy Type: MS-Windows-NT
Sched Label: MonthlyFull
Schedule Type: Full Backup
Retention Level: 6 months (6)
Backup Time: 11/29/2004 00:16:14
Elapsed Time: 000:36:58
Expiration Time: 06/03/2005 01:16:14
Compressed: no
Encrypted: no
Kilobytes: 11845986
Number of Files: 26292
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Client: PSHBGMCGAPP01
Backup ID: PSHBGMCGAPP01_1101531734
Policy: McGregorServersDailyBackup
Policy Type: MS-Windows-NT
Sched Label: DailyIncremental
Schedule Type: Differential Incremental Backup
Retention Level: 6 weeks (2)
Backup Time: 11/27/2004 00:02:14
Elapsed Time: 000:11:15
Expiration Time: 01/08/2005 00:02:14
Compressed: no
Encrypted: no
Kilobytes: 369501
Number of Files: 2202
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Required output:

================================================== ==========================
====
col 1 Col 2 Col 3
Client PSHBGMCGAPP01 PSHBGMCGAPP088
Backup ID PSHBGMCGAPP01_1101705374
PHSNSNNNS01_1919
Policy: McGregorServersDailyBackup XXXX
Policy Type: MS-Windows-NT XXXX
Sched Label: MonthlyFull XXXX
Schedule Type: Full Backup XXXX
Retention Level: 6 months (6)
Backup Time: 11/29/2004 00:16:14
Elapsed Time: 000:36:58
Expiration Time: 06/03/2005 01:16:14
Compressed: no
Encrypted: no
Kilobytes: 11845986
Number of Files: 26292
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Convert text into Excel
 

First... I would suggest changing (if possible) the original input files
into a column format. If not possible ccontinue reading below...

You are going to run out of columns once you hit "IV" (256). I would
suggest converting the data into rows.

I have done this before but due to the fact that each ASCII output file is
unique you will probably have to VBA code each individual file to read and
identify the first portion of the line... "strID = left(strline,8)" (for "
Client:")... The remaining data in the line "Strdata =
trim(right(strLine,len(strLine)-8)) can be coded into the columns A, B, C,
etc... (note that the "8" will depending on the amount of text you use to
identify each line / piece of data.

strLine would be a "line input #1, strLine" from a "open C:\filename.ext for
input as #1"... and then loop through the text file.





"Aqua Flow" wrote in message
om...
I have a text file created and wish to create a MS-Excel spreadsheet
w/columns the 1st column should be static containing row information
while the row could have variable data to append.

See Sample File and compare output file: SInce newbie to excel VBA
stuff help/guidance will be appreciated.


Sample File:
-----------------
Client: PSHBGMCGAPP01
Backup ID: PSHBGMCGAPP01_1101705374
Policy: McGregorServersDailyBackup
Policy Type: MS-Windows-NT
Sched Label: MonthlyFull
Schedule Type: Full Backup
Retention Level: 6 months (6)
Backup Time: 11/29/2004 00:16:14
Elapsed Time: 000:36:58
Expiration Time: 06/03/2005 01:16:14
Compressed: no
Encrypted: no
Kilobytes: 11845986
Number of Files: 26292
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Client: PSHBGMCGAPP01
Backup ID: PSHBGMCGAPP01_1101531734
Policy: McGregorServersDailyBackup
Policy Type: MS-Windows-NT
Sched Label: DailyIncremental
Schedule Type: Differential Incremental Backup
Retention Level: 6 weeks (2)
Backup Time: 11/27/2004 00:02:14
Elapsed Time: 000:11:15
Expiration Time: 01/08/2005 00:02:14
Compressed: no
Encrypted: no
Kilobytes: 369501
Number of Files: 2202
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)

Required output:
================================================== ==============================
col 1 Col 2 Col 3
Client PSHBGMCGAPP01 PSHBGMCGAPP088
Backup ID PSHBGMCGAPP01_1101705374
PHSNSNNNS01_1919
Policy: McGregorServersDailyBackup XXXX
Policy Type: MS-Windows-NT XXXX
Sched Label: MonthlyFull XXXX
Schedule Type: Full Backup XXXX
Retention Level: 6 months (6)
Backup Time: 11/29/2004 00:16:14
Elapsed Time: 000:36:58
Expiration Time: 06/03/2005 01:16:14
Compressed: no
Encrypted: no
Kilobytes: 11845986
Number of Files: 26292
Primary Copy: 1
Image Type: 0 (Regular)
Keyword: (none specified)
Ext Security Info: no
File Restore Raw: no
Image Dump Level: 0
File System Only: no
Object Descriptor: (none specified)
Multiplexed: no
TIR Available: no
Backup Copy: Standard (0)