Data from excel to txt

I am trying to  copy data from excel to txt.  I cannot seem to get the output to lined up as per requirement
col1                                   col2             col3                        col4                              col5
            0.00                      0.00                0.00                       0.00                    -14523.33
  123456.00          125684.83      569888.00                      0.00                 8695443.00
         200.00                   0                    145.15                      0.00                          236.60

I want an output like this in the txt file

                       0.00                     0.00                      0.00                  0.00                 -14523.33
            123456.00          125684.83           569888.00                  0.00              8695443.00
                   200.00                     0.00                  145.15                 0.00                       236.60


I am getting messed up output like this
    0      0     0     0     -14523.33
 123456      125684.83     569888.00     0     8695443.00
     200     0     145.15     0     236.6


I tried to  Concatenate all cols in one cell and copy the output to a text file but still cannot get the output to lined up.
Inputs will be appreciated.
Nana HemaaDBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
send me your file and what is your procedure at the moment?
gilnovSystems AdministratorCommented:
use tabs instead of spaces on output.
Nana HemaaDBAAuthor Commented:
gilnov,
Can you elaborate? please...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Glenn RayExcel VBA DeveloperCommented:
Saving the file as a tab-delimited file (*.txt) won't work.  Try it and then open the file in Notepad.

It sounds like you want the data exported in fixed column widths.  You could replicate this on a separate sheet that concatenates the source data and adjusts the output.  How many characters does each column need to be?

-Glenn
Nana HemaaDBAAuthor Commented:
Yes the tab did not work.   I tried the concatenation  on col f and still could not get it lined up right.  Each character should be about 12 and spaces in between  about 10 or 13.
Glenn RayExcel VBA DeveloperCommented:
I usually use VBA to write out text files in these instances.  However if you only have five columns and want to export them to a fixed width of say, 12 characters per value (including decimals and minus sign, if needed), I should be able to put together a complex concatenation formula that will do just that.  You could then copy and paste those results directly into Notepad, review, and save.

Just a couple minutes; working on negative numbers.
Nana HemaaDBAAuthor Commented:
ok....Thanks.
Nana HemaaDBAAuthor Commented:
The VBA caught my attention too.  Maybe later on I will check with you on that because eventually I will have to automate the process via SSIS package coupled with other processes.
Glenn RayExcel VBA DeveloperCommented:
Told you it wouldn't be pretty:
=IF(Source!A1<0,TEXT(ABS(Source!A1),REPT(" ",12-FIND(".",TEXT(Source!A1,"0.00"))-1)&"-0.00"),TEXT(Source!A1,REPT(" ",12-FIND(".",TEXT(Source!A1,"0.00"))-1)&"0.00"))&IF(Source!B1<0,TEXT(ABS(Source!B1),REPT(" ",12-FIND(".",TEXT(Source!B1,"0.00"))-1)&"-0.00"),TEXT(Source!B1,REPT(" ",12-FIND(".",TEXT(Source!B1,"0.00"))-1)&"0.00"))&IF(Source!C1<0,TEXT(ABS(Source!C1),REPT(" ",12-FIND(".",TEXT(Source!C1,"0.00"))-1)&"-0.00"),TEXT(Source!C1,REPT(" ",12-FIND(".",TEXT(Source!C1,"0.00"))-1)&"0.00"))&IF(Source!D1<0,TEXT(ABS(Source!D1),REPT(" ",12-FIND(".",TEXT(Source!D1,"0.00"))-1)&"-0.00"),TEXT(Source!D1,REPT(" ",12-FIND(".",TEXT(Source!D1,"0.00"))-1)&"0.00"))&IF(Source!E1<0,TEXT(ABS(Source!E1),REPT(" ",12-FIND(".",TEXT(Source!E1,"0.00"))-1)&"-0.00"),TEXT(Source!E1,REPT(" ",12-FIND(".",TEXT(Source!E1,"0.00"))-1)&"0.00"))&IF(Source!F1<0,TEXT(ABS(Source!F1),REPT(" ",12-FIND(".",TEXT(Source!F1,"0.00"))-1)&"-0.00"),TEXT(Source!F1,REPT(" ",12-FIND(".",TEXT(Source!F1,"0.00"))-1)&"0.00"))

This assumes the first sheet is named "Source."  If you add a new sheet and insert this formula in cell A1 and copy down, you'll get fixed column results for your six columns of data.  You can then select, copy and paste the values in column A of the new sheet directly into Notepad and then save as text.

To make it more flexible, the attached example file has a variable called CS (columns) that you can change to re-assign the number of characters per column for all columns.  The formula above is set to 12 characters; the example workbook has 15.

-Glenn
EE-Q_28922202.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nana HemaaDBAAuthor Commented:
Voww Thanks.   I will  give it a ty.
Glenn RayExcel VBA DeveloperCommented:
I just realized that I am processing six (6) columns instead of five.  I duplicated the fourth column with zeros by accident.

You can remove the last part of the compound formula:
&IF(Source!F1<0,TEXT(ABS(Source!F1),REPT(" ",12-FIND(".",TEXT(Source!F1,"0.00"))-1)&"-0.00"),TEXT(Source!F1,REPT(" ",12-FIND(".",TEXT(Source!F1,"0.00"))-1)&"0.00"))
byundtMechanical EngineerCommented:
If I understand the need, you can simplify the 5-column concatenation to:
=RIGHT(REPT(" ",12) & TEXT(B1,"0.00"),12) & RIGHT(REPT(" ",22) & TEXT(C1,"0.00"),22) & RIGHT(REPT(" ",22) & TEXT(D1,"0.00"),22) & RIGHT(REPT(" ",22) & TEXT(E1,"0.00"),22) & RIGHT(REPT(" ",22) & TEXT(F1,"0.00"),22)

This formula allows a fixed 12 spaces for the first number (including two decimal places). The number will be padded with leading spaces as required. If the number is between -1 and 1, then the leading 0 will be displayed. The sign (if negative) will be placed before the number.

Succeeding numbers in the concatenation are treated the same, except that a gutter of 10 spaces is allowed between each column (12 + 10 = 22). Both the REPT and the RIGHT parts of the formula have to specify 22.

The formula may be easier to understand if you put ALT + Enter linefeeds in it:
=RIGHT(REPT(" ",12) & TEXT(B1,"0.00"),12) & 
RIGHT(REPT(" ",22) & TEXT(C1,"0.00"),22) & 
RIGHT(REPT(" ",22) & TEXT(D1,"0.00"),22) & 
RIGHT(REPT(" ",22) & TEXT(E1,"0.00"),22) & 
RIGHT(REPT(" ",22) & TEXT(F1,"0.00"),22)

****IMPORTANT****  You must use a monospaced font to see everything lined up. I used Courier New for this purpose.
byundtMechanical EngineerCommented:
VBA code to do the above concatenation should be put in a regular module sheet. You use it with a worksheet formula like:
=Concatenator(B1:F1,"0.00",12,10)
Function Concatenator(rg As Range, sNumberFormat As String, iColumnWidth As Integer, iGutterWidth As Integer) As String
Dim cel As Range
Dim s As String
For Each cel In rg.Cells
    If s = "" Then
        s = Right(Application.Rept(" ", iColumnWidth) & Format(cel.Value, sNumberFormat), iColumnWidth)
    Else
        s = s & Right(Application.Rept(" ", iColumnWidth + iGutterWidth) & Format(cel.Value, sNumberFormat), iColumnWidth + iGutterWidth)
    End If
Next
Concatenator = s
End Function

Open in new window

byundtMechanical EngineerCommented:
Sample file showing both formulas and VBA
PadToFixedWidthQ28922202.xlsm
Glenn RayExcel VBA DeveloperCommented:
Much better function than my original.
Nana HemaaDBAAuthor Commented:
Thanks guys really appreciate your input.  I am trying it out.
Nana HemaaDBAAuthor Commented:
both work great thanks guys..

byundt   how do I incorporate the VBA section into a VBscript.  I will use it in SSIS package.  I have hundreds of records to process daily so automating the process will be great.
byundtMechanical EngineerCommented:
Nana Hemaa,
I am not a VBscript expert, and cannot comment on how best to integrate the user defined function into your workflow.

That said, if your VBscript code can open an Excel workbook, a Workbook_Open sub in that workbook will run automatically. The Workbook_Open sub can then call another macro that applies the user defined function to certain cells, either in one of its own worksheets or in another workbook. After the function has done its work, the macro would then replace the cells with the UDF formulas with the values the formulas return.

If you need more guidance than that, I will defer to Glenn Ray. Or you might consider reposting the question along with my code and a sample workbook in the VBscript Zone.

Brad
Nana HemaaDBAAuthor Commented:
Thanks guys
byundtMechanical EngineerCommented:
Nana Hemaa,
If you want to follow the suggestion I made of letting a Workbook_Open event macro direct all the modifications, I'd be delighted to help you as needed on the Excel side. That would leave you with the singular task of opening an Excel workbook using VBscript.

To follow that approach, please continue posting in this thread. I would like to see a sample workbook showing your starting point and what you would like the final product to be. If your workbook contains a single worksheet, then you could post a workbook with "before" and "after" worksheets.

Brad
Nana HemaaDBAAuthor Commented:
Brad ...thanks  you are so helpful.  I will  post tomorrow  morning.   I am amazed  at how helpful  you all are.  I normally  post questions @ the  database group.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.