Transfering Data from Excel To File Editor

[CWEngineer]

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

 

[MintJulep]

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.

 

[TomBarsh]

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.

 

[IRstuff]

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

 

[Feric]

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

 

[CWEngineer]

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

 

[davidbeach]

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

 

[IRstuff]

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

 

[handleman]

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

 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.

 

[CWEngineer]

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

 

[davidbeach]

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.

 

[CWEngineer]

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.

 

[davidbeach]

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.

 

[CWEngineer]

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

 

[davidbeach]

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

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.

 

[wannabeSE]

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.