×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Transfering Data from Excel To File Editor

Transfering Data from Excel To File Editor

Transfering Data from Excel To File Editor

(OP)
I am trying to copy data (numbers and Letters) from an excel spreadsheet to a file editor to use the file in a FORTRAN program.  I am doing this by copying in excel and then pasting the file editor.

It seems like no matter how wide or narrow I make the rows in excel, the data is always copied in the same location in the file editor.

Is there something I can do in excel so that when I copy the data to the file editor it places it in the location I want it to be.

THANKS

RE: Transfering Data from Excel To File Editor

I have no idea what you might mean by "the data is always copied in the same location in the file editor".

But, the width of a column in Excel has no relationship to number of characters in a cell.  If you have a cell with a single character, and make the column width the full width of your screen, there is still a single character in the cell.  If you have a cell with 100 characters, and narrow the column so that only 1 character is visible, there are still 100 characters in the cell.

If you want to pad the data in cells, you should look into custom formats.

RE: Transfering Data from Excel To File Editor

I used to use Visual Basic routines (VBA macros) in Excel to transfer information from my spreadsheets to plain text files for use as input to other programs. Might you do the same? It would certainly be easier to "click a button" than to manually copy and paste.

RE: Transfering Data from Excel To File Editor

You can save in CSV format and use an intervening cell filled with the appropriate number of spaces and the numbers formatted as required.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Transfering Data from Excel To File Editor

Hi there:

How about if you try to save your MS Excel data file in the txt format and just import such a file into your FORTRAN editor.

From the txt file, cut and past should work too.

Thanks,

G. Feric, PE
http://engware.i-dentity.com

RE: Transfering Data from Excel To File Editor

(OP)
Let me see if I can explain what I am trying to do a little better.  

Currently I have this file in txt format that has the information aligned something like this:
1130 11 Y T -21.00   -0.96   10.79 R
1140 11 Y T -518.00  -0.96   10.79 L
1150 12 Y T  21.00   -0.96   10.79 R    
1160 12 Y T  518.00  -0.96   10.79 L  
1170 11 X U -187.50   0.96    8.96  
1180 12 X U -187.50   0.96    8.96

I created an excel file that generates the information I want, but when I copy and paste it to the txt file it looks like this:  Actually once I posted this thread the information is aligned better.  When I copy it to the txt file the last four columns are further to the right.
1220    21    Y    C     -34670    3.420        
1230    22    Y    C     -34670    3.420        
1240    21    Y    U    -200.00    -0.4200    10.8300    
1250    22    Y    U    -200.00    0.0000    11.2500    
1260    11    Y    T       -451.50    -0.6700    10.0000    L
1270    13    Y    T     451.50    -0.6700    10.0000    L

These two files are different, but the information is similar.  I tried saving the excel file as a txt file but it still did not work.

Appreciate your help in trying to copy the information from excel to a txt file, which is aligned similar to the first example I provided above.

THANKS

RE: Transfering Data from Excel To File Editor

Create yet one more column in excel that concatenates and formats the data to be exported and then just export that column.

RE: Transfering Data from Excel To File Editor

Why can't you save the file as "Formatted Text" *.prn format?

This forces the output into Fortran-like output format.  You'll need to insert empty columns between columns with letters that follow columns with numbers.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Transfering Data from Excel To File Editor

When you paste information that was copied from Excel, it gets pasted with Tab characters between each column.  You need one more column that concatenates all the other columns with spaces in between.  So if your Excel sheet has

CODE

 A    B C D   E        F       G   H
-------------------------------------
1130 11 Y T -21.00   -0.96   10.79 R
1140 11 Y T -518.00  -0.96   10.79 L
1150 12 Y T  21.00   -0.96   10.79 R    
1160 12 Y T  518.00  -0.96   10.79 L  
1170 11 X U -187.50   0.96    8.96  
1180 12 X U -187.50   0.96    8.96

then your Column I should have the formula

=TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1)

Then just copy Column I to your text file.

RE: Transfering Data from Excel To File Editor

(OP)
Thanks, I think I am almost getting there. I input empty columns between columns with letters that follow colummns with numbers.  The prn files looks good.  Is there a way to reduce the number of spaces between columns? Right now a letter starts about 8 spaces after a number, I would like that letter to start about a space after a number.

THANKS

RE: Transfering Data from Excel To File Editor

You don't need the prn file.  Read my post above.  handleman's TRIM equation will work just fine if you always want exactly one blank space between each value (works well if what comes out of each column is always exactly the same number of characters.  A more general version would be

=LEFT(TRIM(A1)&"     ",5))&LEFT(TRIM(B1)&"   ",3))&...

The spaces are used to pad the column value to something longer than desired which is then truncated using LEFT.  Include one more space than the maximum width of the column and have the second argument of each LEFT equal to one more than the desired width and you will have a minimum of one space between columns but each column will line up vertically and any empty cells will be replaced with spaces.

RE: Transfering Data from Excel To File Editor

(OP)
I am trying to figuere out a little more what this formula does. Here is the information I have in my file:
First Column:  4 Characters
Second Column: 1 AND 2 Charcters
Third Column: 1 AND 4 Characters
Fourth Column: 3 to 7 Characters but could have more
etc.

I tried the follwing formula but my values were not aligned.  One thing I noticed is that the zeros at the end were removed.  I also put a 10 because I wanted to make sure my data was not truncated.  Is there any way I can post my excel spreadsheet and see if you guys can help me out with what I want to do.

THANKS


=LEFT(TRIM(A38)&" ",10)&LEFT(TRIM(B38)&" ",10)&LEFT(TRIM(C38)&" ",10)&LEFT(TRIM(D38)&" ",10)  etc.

RE: Transfering Data from Excel To File Editor

If you want a length of 10, there need to be 10 spaces between the quotes.  The LEFT function won't generate more length if it isn't there, that's why the spaces to guarantee that you always have a long enough string for LEFT to operate on if you want consistent results without regard to the actual values.

RE: Transfering Data from Excel To File Editor

(OP)
I am still trying to figuere this out.  Here are some problems I am encoutering:

1. If the values in a column do not have the same number of characters (for example if 2 and 14 are in the same column) then the values in the next column are not align.
2.  Also, when I have a value suche as 200.00 when I use the formulat that number gets converted to 200   Is there a way to keep the .00 keeping in mind that some numbers in the column do not contain decimal places such as -34670.

THANKS

RE: Transfering Data from Excel To File Editor

CODE

   |A     B     C  D    E
   |---------------------
 1 |1     ad    1  -57  a  
 2 |2.00  adadf 13 25   b  
 3 |2.01  adfa  12 37.8 cd
 4 |127.5 adadf 3  boo  efg

with the formula

=LEFT(TRIM(A1)&"      ",6)&LEFT(TRIM(B1)&"      ",6)&LEFT(TRIM(C1)&"   ",3)&LEFT(TRIM(D1)&"     ",5)&LEFT(TRIM(E1)&"   ",3)

in column F produces the following results:

CODE

1     ad    1  -57  a  
2.00  adadf 13 25   b  
2.01  adfa  12 37.8 cd
127.5 adadf 3  boo  efg

Note that there a as many spaces in each &"   " as the number after the comma in the LEFT function.

RE: Transfering Data from Excel To File Editor

You can use the TEXT or FIXED function in the formula to preserve the significant digits. They convert numbers to text. Instead of TRIM(A1) use FIXED(A1,2)or TEXT(A1,"0.00")for the numeric data.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources