If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with Saving Excel File as PRN
I need to be able to save an excel file in a fixed width format. I saved as a
PRN format, but some of the data is not saving with the file. Out of 30 columns of data only the first 5 saved with the file. When the warning message that says some data may not be compatible, I answered YES to proceed. I also tested it answering NO, but still had the same results - missing data. Any suggestions would be greatly appreciated !!! |
#2
|
|||
|
|||
Problem with Saving Excel File as PRN
There is a limit on how wide the output file will be in .prn files (I think it's
240 characters). Maybe the row was split over multiple pieces. You may want to scroll down a bit to see if that's where your data went. If you have to create a .prn file that's wider than 240 characters... Saved from a previous post: There's a limit of 240 characters per line when you do File|saveas and use .prn files. So if your data wouldn't create a record that was longer than 240 characters, you can save the file as .prn. I like to use a fixed width font (courier new) and adjust the column widths manually. But this can take a while to get it perfect. (Save it, check the output in a text editor, back to excel, adjust, save, and recheck in that text editor. Lather, rinse, and repeat!) Alternatively, you could concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00") (You'll have to modify it to match what you want.) Drag it down the column to get all that fixed width stuff. Then I'd copy and paste to notepad and save from there. Once I figured out that ugly formula, I kept it and just unhide that column when I wanted to export the data. If that doesn't work for you, maybe you could do it with a macro. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl DH wrote: I need to be able to save an excel file in a fixed width format. I saved as a PRN format, but some of the data is not saving with the file. Out of 30 columns of data only the first 5 saved with the file. When the warning message that says some data may not be compatible, I answered YES to proceed. I also tested it answering NO, but still had the same results - missing data. Any suggestions would be greatly appreciated !!! -- Dave Peterson |
#3
|
|||
|
|||
Problem with Saving Excel File as PRN
Hi Dave,
Thank you for getting back to me. I was talking with someone here in my office and they suggested I save it as a TXT format. Do you know if that would work if my vendor is asking for the file to be fixed width ASCII? Dawn "Dave Peterson" wrote: There is a limit on how wide the output file will be in .prn files (I think it's 240 characters). Maybe the row was split over multiple pieces. You may want to scroll down a bit to see if that's where your data went. If you have to create a .prn file that's wider than 240 characters... Saved from a previous post: There's a limit of 240 characters per line when you do File|saveas and use .prn files. So if your data wouldn't create a record that was longer than 240 characters, you can save the file as .prn. I like to use a fixed width font (courier new) and adjust the column widths manually. But this can take a while to get it perfect. (Save it, check the output in a text editor, back to excel, adjust, save, and recheck in that text editor. Lather, rinse, and repeat!) Alternatively, you could concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00") (You'll have to modify it to match what you want.) Drag it down the column to get all that fixed width stuff. Then I'd copy and paste to notepad and save from there. Once I figured out that ugly formula, I kept it and just unhide that column when I wanted to export the data. If that doesn't work for you, maybe you could do it with a macro. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl DH wrote: I need to be able to save an excel file in a fixed width format. I saved as a PRN format, but some of the data is not saving with the file. Out of 30 columns of data only the first 5 saved with the file. When the warning message that says some data may not be compatible, I answered YES to proceed. I also tested it answering NO, but still had the same results - missing data. Any suggestions would be greatly appreciated !!! -- Dave Peterson . |
#4
|
|||
|
|||
Problem with Saving Excel File as PRN
If you mean a tab delimited text file, then this won't be the same as a fixed
width file. Fixed width fields mean the text file doesn't have its fields separted by anything. The fields are defined by the columns that they occupy. Field one could be columns 1-10, field 2 could be columns 11-12, field 3 could be 13-20. With a tab delimited file, the data is usually trimmed and there is an extra character (the tab delimiter) between each field. So if I had this in columns A:C: aaa b ccccc The data in the PRN/fixed width file would look like: aaa_______b_ccccc_____ (where _ represents a space character) In that tab delimited file, the data would look like: aaa|b|ccccc where | represents the tab character But... Check with the recipient and ask if they would accept a tab delimited file. They may surprise you and say yes. I'd bet that they have some conversion utility (or even import procedure) that can handle that tab delimited file. (Sometimes requirements are just suggestions vbg.) DH wrote: Hi Dave, Thank you for getting back to me. I was talking with someone here in my office and they suggested I save it as a TXT format. Do you know if that would work if my vendor is asking for the file to be fixed width ASCII? Dawn "Dave Peterson" wrote: There is a limit on how wide the output file will be in .prn files (I think it's 240 characters). Maybe the row was split over multiple pieces. You may want to scroll down a bit to see if that's where your data went. If you have to create a .prn file that's wider than 240 characters... Saved from a previous post: There's a limit of 240 characters per line when you do File|saveas and use .prn files. So if your data wouldn't create a record that was longer than 240 characters, you can save the file as .prn. I like to use a fixed width font (courier new) and adjust the column widths manually. But this can take a while to get it perfect. (Save it, check the output in a text editor, back to excel, adjust, save, and recheck in that text editor. Lather, rinse, and repeat!) Alternatively, you could concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00") (You'll have to modify it to match what you want.) Drag it down the column to get all that fixed width stuff. Then I'd copy and paste to notepad and save from there. Once I figured out that ugly formula, I kept it and just unhide that column when I wanted to export the data. If that doesn't work for you, maybe you could do it with a macro. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl DH wrote: I need to be able to save an excel file in a fixed width format. I saved as a PRN format, but some of the data is not saving with the file. Out of 30 columns of data only the first 5 saved with the file. When the warning message that says some data may not be compatible, I answered YES to proceed. I also tested it answering NO, but still had the same results - missing data. Any suggestions would be greatly appreciated !!! -- Dave Peterson . -- Dave Peterson |
#5
|
|||
|
|||
Problem with Saving Excel File as PRN
Earl Kiosterud's free TEXTWRITE.xls workbook should do what you want.
http://www.smokeylake.com/excel/text_write_program.htm You can save as fixed width *.txt file. Note the caveat about not padding fields. Gord Dibben MS Excel MVP On Fri, 13 Nov 2009 07:01:01 -0800, DH wrote: Hi Dave, Thank you for getting back to me. I was talking with someone here in my office and they suggested I save it as a TXT format. Do you know if that would work if my vendor is asking for the file to be fixed width ASCII? Dawn "Dave Peterson" wrote: There is a limit on how wide the output file will be in .prn files (I think it's 240 characters). Maybe the row was split over multiple pieces. You may want to scroll down a bit to see if that's where your data went. If you have to create a .prn file that's wider than 240 characters... Saved from a previous post: There's a limit of 240 characters per line when you do File|saveas and use .prn files. So if your data wouldn't create a record that was longer than 240 characters, you can save the file as .prn. I like to use a fixed width font (courier new) and adjust the column widths manually. But this can take a while to get it perfect. (Save it, check the output in a text editor, back to excel, adjust, save, and recheck in that text editor. Lather, rinse, and repeat!) Alternatively, you could concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00") (You'll have to modify it to match what you want.) Drag it down the column to get all that fixed width stuff. Then I'd copy and paste to notepad and save from there. Once I figured out that ugly formula, I kept it and just unhide that column when I wanted to export the data. If that doesn't work for you, maybe you could do it with a macro. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl DH wrote: I need to be able to save an excel file in a fixed width format. I saved as a PRN format, but some of the data is not saving with the file. Out of 30 columns of data only the first 5 saved with the file. When the warning message that says some data may not be compatible, I answered YES to proceed. I also tested it answering NO, but still had the same results - missing data. Any suggestions would be greatly appreciated !!! -- Dave Peterson . |
Thread Tools | |
Display Modes | |
|
|