Data from excel to txt

Nana Hemaa
Nana Hemaa used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raheman M. AbdulMessaging and Directory Services

Commented:
send me your file and what is your procedure at the moment?
gilnovSystems Administrator

Commented:
use tabs instead of spaces on output.

Author

Commented:
gilnov,
Can you elaborate? please...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
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

Author

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 Developer
Top Expert 2014

Commented:
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.

Author

Commented:
ok....Thanks.

Author

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.
Excel VBA Developer
Top Expert 2014
Commented:
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

Author

Commented:
Voww Thanks.   I will  give it a ty.
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
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 Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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 Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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 Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Sample file showing both formulas and VBA
PadToFixedWidthQ28922202.xlsm
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Much better function than my original.

Author

Commented:
Thanks guys really appreciate your input.  I am trying it out.

Author

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 Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

Commented:
Thanks guys
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial