Excel Again

[m204prgmr]

Good morning,

I keep revisiting this and I'm not sure if it has ever been answered.

I have a flat file formated as follows;

 

first 6 rows are alpha characters

The Remainder fo the document is numeric, 4 columms.

 

Below is a paste of what it looks like (edited for HIPAA)

 

The information below is generated from a custom procedure that can be edited

if need be and is sent to TSO. Retrieval of this and appending an extension is very open. I can say I want it as a .txt or .xls or .csv or .doc or whatever.

 

My question is this, how do I handle this file so that ME can create it as a .csv and can anyone share how they did it? I have tons of these and it would save hours of time if I automate flat files into .csv.

Thanks,

Robert

 

 

CUSTPROC (XXX.DCRREJCT.PAYE)

TYPE E PAYMENT REJECTS FOR XXX AS OF 09-12-2005

------------------------------------------------------------------------

 

ENCOUNTER SERV DATE TRANS CODE AMOUNT

------------------------------------------------------------------------

123456789a 20050906 XX101253 -137.46

123456789b 20050908 XX101147 7.00

1076261599 20050908 XX101147 -7.00

[Lars_Sandberg]

Hi, for such text-editing job, you need a good editor.

I use UltraEdit for such jobs, where the tast you ask for can be done in seconds, whether you need csv or tsv (tab separated files)

http://www.ultraedit.com/

 

And if you have many of these files ME controlling UltraEdit can be helpfull.

 

regards Lars

[randallc]

Hi,

I presume you want all the text file appended as 1 csv line?; [if you have lots of txt files to process, set "T1" through "repeat with folders", set full file path of files to "T1"]

<REM2:text fileread; initialise><ASCIIC:2:1:10><ASCIIC:3:1:13><TMVAR2:08:03:02:000:000:><TMVAR2:19:03:00:000:000:CRLF><ASCIIC:3:1:9><TMVAR2:19:03:00:000:000:TAB><VSETMISC:T1:Installation Path><TMVAR2:19:01:00:000:000:InstallPath><REM2:Set File Name to Parameter1><TVAR2:01:01:%InstallPath%\File.txt><TMVAR2:19:01:00:000:000:Parameter1><REM2:Set from prompt if needed><IFOTH:01:2:%Parameter1%><TVAR2:01:01:%Parameter1%><ELSE><TVAR2:01:01:%InstallPath%\File.txt><TVAR2:01:02:FFile name?FFCenter:Center><TMVAR2:19:01:00:000:000:Parameter1><DOFILE:05:NN:%T1%>%InstallPath%\temp.txt><ENDIF><TMVAR2:19:01:00:000:000:FileName><REM2:Retrieve first line, count commas; add one for number fields answer><TVAR2:01:04:%InstallPath%\File.txt><TMVAR2:21:01:01:000:000:%CRLF%,><TMVAR2:20:01:00:000:000:%InstallPath%\File.csvT><LAUNCHDEL2:0:01%InstallPath%\File.csv>

// text fileread; initialise

Variable Set %T2% to ASCII Char of 10

Variable Set %T3% to ASCII Char of 13

Variable Modify String: Append %T2% to %T3%

Variable Modify String: Save %T3% to Environment Variable

Variable Set %T3% to ASCII Char of 9

Variable Modify String: Save %T3% to Environment Variable

Set Variable %T1% to "Installation Path"

Variable Modify String: Save %T1% to Environment Variable

// Set File Name to Parameter1

Variable Set String %T1% "%InstallPath%\File.txt"

Variable Modify String: Save %T1% to Environment Variable

// Set from prompt if needed

If File Exists "%Parameter1%"

  Variable Set String %T1% "%Parameter1%"

Else

  Variable Set String %T1% "%InstallPath%\File.txt"

  Variable Set String %T1% from Prompt

  Variable Modify String: Save %T1% to Environment Variable

  Copy File or Files: "%T1%"

End If

Variable Modify String: Save %T1% to Environment Variable

// Retrieve first line, count commas; add one for number fields answer

Variable Set String %T1% from File: "File.txt"

Replace "%CRLF%" with "," in %T1%

Variable Modify String: Append %T1% to Text File

Program Launch: "File.csv"

Best, Randall

EDIT' [if you have lots of txt files to process, set "T1" through "repeat with folders", set full file path of files to "T1"]

Edited September 12, 2005 by randallc

[m204prgmr]

Lars,

I downloaded the UltraEdit demo and will look at it. It has been years since I've used it and boy has it changed.

 

Randal,

As always, I admire the hard work you do. thank you

 

I will try both suggestions.

 

Lars,

Can you share how you would use ME in conjunction with UltraEdit to totally automate this? Thanks

[m204prgmr]

Randal,

Your code created the file but it was all on one row extending all the way out to column IV with an error message that the file did not load completly... .

 

What I am looking to do is to take a .txt file like this and create a .csv using ME

 

1076261599 20050906 GW101253 -137.46

1076261599 20050908 GW101147 7.00

1076261599 20050908 GW101147 -7.00

1076261599 20050908 GW101147 73.00

1076261599 20050908 GW101147 -73.00

1089063001 330 20050808 GW999995 -2700.00

1089063001 M21 20050808 GW999995 -5400.00

1089063001 20050901 GW104984 -167.00

1093868602 M22 20050822 GW555557 115.10

1093868602 C76 20050822 GW555557 -115.10

1093868602 C76 20050824 GW555557 115.10

1093868602 M22 20050824 GW555557 -115.10

1093868602 M22 20050826 GW101675 4966.63

1093868602 M22 20050826 GW101675 -4966.63

1093868602 M22 20050826 GW999987 115.10

1093868602 D25 20050826 GW999987 -115.10

1093868602 D25 20050831 GW101147 115.10

[randallc]

[EDIT]Hi again;

Perhaps I have mis-understood, or messed up the script?

Do you want the whole file on 1 line with commas instead of "CRLF", or do you want commas in the "csv" file to replace the "space"s?

Here with the spaces;

<REM2:text fileread; initialise><ASCIIC:2:1:10><ASCIIC:3:1:13><TMVAR2:08:03:02:000:000:><TMVAR2:19:03:00:000:000:CRLF><ASCIIC:3:1:9><TMVAR2:19:03:00:000:000:TAB><VSETMISC:T1:Installation Path><TMVAR2:19:01:00:000:000:InstallPath><REM2:Set File Name to Parameter1><TVAR2:01:01:%InstallPath%\File.txt><TMVAR2:19:01:00:000:000:Parameter1><REM2:Set from prompt if needed><IFOTH:01:2:%Parameter1%><TVAR2:01:01:%Parameter1%><ELSE><TVAR2:01:01:%InstallPath%\File.txt><TVAR2:01:02:FFile name?FFCenter:Center><TMVAR2:19:01:00:000:000:Parameter1><DOFILE:05:NN:%T1%>%InstallPath%\temp.txt><ENDIF><TMVAR2:19:01:00:000:000:FileName><REM2:Retrieve first line, count commas; add one for number fields answer><TVAR2:01:04:%InstallPath%\File.txt><TMVAR2:21:01:01:000:000: ,><TMVAR2:17:01:00:000:000:%InstallPath%\File.csvT><LAUNCHDEL2:0:01%InstallPath%\File.csv>

Best, Randall

[m204prgmr]

Randal,

Bada Bing! Thank you very much. I think the hardest work is over.

I also think I should have given more information. Forgive me