Parsing space/tab separated Text file and embedding into XL file

Question

Hi i have my text file in this format

**4  1250000209852   01 XXXX              XXXX      V      3054XXX, XXXX J.           73227DUONG, DUC H.                   
672XXX COMM HOSP         40352405                 RO
                  07/07/201010/05/2010HO                                      
331.5  XXX NL PRESS XXX                                                               70450        1       .00          OPA07/07/2010WEB       01/01/0001  
07/06/201007/07/2010                                                                       WEB       01   01/01/0001SI         0    0     85 MDO   CCHP C     0   01  125003/15/1925CT HEAD/XXX/W/O DYE                    0**

and my output should be in Excel in this format

Auth Status Member LNAME Member FNAME MI MEMBID DOB HPNAME DIAGCODE

Provider ID PCP Last Name PCP First Name PCP MI Requested Vendor Service Type 

AUTHNO REQDATE AUTHDATE EXPRDATE

Here I edited and elaborated my situation:

So how do i export the txt contents to XL on the fly or guide me through the steps for mapping columns of two different files. Actually my inputs are in space separated text file, or Excel File or MS Access database, any of them. But output is in excel only. So I need some logic to load the file and map and transfer the text to resultant file in XL format (97-2003).

Answer 1

I'm slightly confused by your source file...how many rows of data are being displayed?

When you say

how do i export the txt contents to XL on the fly

are you trying to avoid opening the text file in Excel and choosing between "fixed width" or "tab deliminated" and having Excel format it for you?