Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
Hi all,
I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
select all you cells and set the font as courier new (a mono spaced font).
the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
Tom,
With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
=REPT(" ",4) & TEXT(A1,"000000.00")
will put 4 spaces, then the number in A1 (formatted with 2 decimals). jeffP wrote: Tom, With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
=rept(" ",20-len(Sheet1!A1)) & Sheet1!A1
-- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
Tom,
Great help from you and also from Dave. I still can't exactly get what I want , though. This gives me the perfect fixed length field that I need for all my fields except the $ amount field. For that field I adjusted Dave's suggestion =REPT(" ",4) & TEXT(A1,"000000.00") to =REPT(" ",20-len(sheet1!a1)) & TEXT(sheet1!a1,"000000.00") and it formats the way I need but does not provide the fixed length. I need the two decimal format but also the field filled left to 20 characters. If you don't mind continuing to help, I always appreciate it. -- jeffP "Tom Ogilvy" wrote in message ... =rept(" ",20-len(Sheet1!A1)) & Sheet1!A1 -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
=REPT(" ",20-len(TEXT(sheet1!a1,"000000.00"))) & TEXT(sheet1!a1,"000000.00")
or, since we know the format will be 9 characters wide =REPT(" ",11) & TEXT(sheet1!a1,"000000.00") if you don't want leading zeros with $ =REPT(" ",20-len(TEXT(sheet1!a1,"$#,###.00"))) & TEXT(sheet1!a1,"$#,###.00") Without $ =REPT(" ",20-len(TEXT(sheet1!a1,"#,###.00"))) & TEXT(sheet1!a1,"#,###.00") or without $ and commas =REPT(" ",20-len(TEXT(sheet1!a1,"#.00"))) & TEXT(sheet1!a1,"#.00") -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, Great help from you and also from Dave. I still can't exactly get what I want , though. This gives me the perfect fixed length field that I need for all my fields except the $ amount field. For that field I adjusted Dave's suggestion =REPT(" ",4) & TEXT(A1,"000000.00") to =REPT(" ",20-len(sheet1!a1)) & TEXT(sheet1!a1,"000000.00") and it formats the way I need but does not provide the fixed length. I need the two decimal format but also the field filled left to 20 characters. If you don't mind continuing to help, I always appreciate it. -- jeffP "Tom Ogilvy" wrote in message ... =rept(" ",20-len(Sheet1!A1)) & Sheet1!A1 -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
And one more option--just to muddy the waters!
=RIGHT(REPT(" ",20)&TEXT(Sheet1!A1,"$#,###.00"),20) Format the contents, add a bunch of spaces on the left and take the 20 rightmost characters. Tom Ogilvy wrote: =REPT(" ",20-len(TEXT(sheet1!a1,"000000.00"))) & TEXT(sheet1!a1,"000000.00") or, since we know the format will be 9 characters wide =REPT(" ",11) & TEXT(sheet1!a1,"000000.00") if you don't want leading zeros with $ =REPT(" ",20-len(TEXT(sheet1!a1,"$#,###.00"))) & TEXT(sheet1!a1,"$#,###.00") Without $ =REPT(" ",20-len(TEXT(sheet1!a1,"#,###.00"))) & TEXT(sheet1!a1,"#,###.00") or without $ and commas =REPT(" ",20-len(TEXT(sheet1!a1,"#.00"))) & TEXT(sheet1!a1,"#.00") -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, Great help from you and also from Dave. I still can't exactly get what I want , though. This gives me the perfect fixed length field that I need for all my fields except the $ amount field. For that field I adjusted Dave's suggestion =REPT(" ",4) & TEXT(A1,"000000.00") to =REPT(" ",20-len(sheet1!a1)) & TEXT(sheet1!a1,"000000.00") and it formats the way I need but does not provide the fixed length. I need the two decimal format but also the field filled left to 20 characters. If you don't mind continuing to help, I always appreciate it. -- jeffP "Tom Ogilvy" wrote in message ... =rept(" ",20-len(Sheet1!A1)) & Sheet1!A1 -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
Tom and Dave,
You are unbelievable in talent and help. I can't thank you enough. I had one thing more. I need to take a cell that's 2 place numeric w/ decimal (1234.56) and move it to a cell formatted text, 9 characters (left fill) without the decimal point (it would be assumed 2 decimal places). I couldn't get it formatted without loosing the last two digits so this is what I did. It works fine but was there a simpler way? =text(sheet1!A1*100,""000000000"") Thanks again for your time and help -- jeffP "Tom Ogilvy" wrote in message ... =REPT(" ",20-len(TEXT(sheet1!a1,"000000.00"))) & TEXT(sheet1!a1,"000000.00") or, since we know the format will be 9 characters wide =REPT(" ",11) & TEXT(sheet1!a1,"000000.00") if you don't want leading zeros with $ =REPT(" ",20-len(TEXT(sheet1!a1,"$#,###.00"))) & TEXT(sheet1!a1,"$#,###.00") Without $ =REPT(" ",20-len(TEXT(sheet1!a1,"#,###.00"))) & TEXT(sheet1!a1,"#,###.00") or without $ and commas =REPT(" ",20-len(TEXT(sheet1!a1,"#.00"))) & TEXT(sheet1!a1,"#.00") -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, Great help from you and also from Dave. I still can't exactly get what I want , though. This gives me the perfect fixed length field that I need for all my fields except the $ amount field. For that field I adjusted Dave's suggestion =REPT(" ",4) & TEXT(A1,"000000.00") to =REPT(" ",20-len(sheet1!a1)) & TEXT(sheet1!a1,"000000.00") and it formats the way I need but does not provide the fixed length. I need the two decimal format but also the field filled left to 20 characters. If you don't mind continuing to help, I always appreciate it. -- jeffP "Tom Ogilvy" wrote in message ... =rept(" ",20-len(Sheet1!A1)) & Sheet1!A1 -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
saveas fixed text for unix
That's the way I'd do it. (And I can't think of anything simpler.)
jeffP wrote: Tom and Dave, You are unbelievable in talent and help. I can't thank you enough. I had one thing more. I need to take a cell that's 2 place numeric w/ decimal (1234.56) and move it to a cell formatted text, 9 characters (left fill) without the decimal point (it would be assumed 2 decimal places). I couldn't get it formatted without loosing the last two digits so this is what I did. It works fine but was there a simpler way? =text(sheet1!A1*100,""000000000"") Thanks again for your time and help -- jeffP "Tom Ogilvy" wrote in message ... =REPT(" ",20-len(TEXT(sheet1!a1,"000000.00"))) & TEXT(sheet1!a1,"000000.00") or, since we know the format will be 9 characters wide =REPT(" ",11) & TEXT(sheet1!a1,"000000.00") if you don't want leading zeros with $ =REPT(" ",20-len(TEXT(sheet1!a1,"$#,###.00"))) & TEXT(sheet1!a1,"$#,###.00") Without $ =REPT(" ",20-len(TEXT(sheet1!a1,"#,###.00"))) & TEXT(sheet1!a1,"#,###.00") or without $ and commas =REPT(" ",20-len(TEXT(sheet1!a1,"#.00"))) & TEXT(sheet1!a1,"#.00") -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, Great help from you and also from Dave. I still can't exactly get what I want , though. This gives me the perfect fixed length field that I need for all my fields except the $ amount field. For that field I adjusted Dave's suggestion =REPT(" ",4) & TEXT(A1,"000000.00") to =REPT(" ",20-len(sheet1!a1)) & TEXT(sheet1!a1,"000000.00") and it formats the way I need but does not provide the fixed length. I need the two decimal format but also the field filled left to 20 characters. If you don't mind continuing to help, I always appreciate it. -- jeffP "Tom Ogilvy" wrote in message ... =rept(" ",20-len(Sheet1!A1)) & Sheet1!A1 -- Regards, Tom Ogilvy "jeffP" wrote in message ... Tom, With your help I'm getting this project to work. Thanks. Adjust the formula if you want to left pad the value.<<<<< I need to adjust the formula, but don't know how. As is it adds spaces to the right of the existing number and I need it to fill on the left of the number. All your help is greatly appreciated. -- jeffP "Tom Ogilvy" wrote in message ... select all you cells and set the font as courier new (a mono spaced font). the autofit your columns or manually adjust the width. Then do file=Save as and select Formatted Text (space delimited) (.prn) to be more exact, you might go to a second sheet and put in formula in A1 like =Sheet1!A1 & rept(" ",20-len(Sheet1!A1)) then dragfill down and across to capture your data. Adjust the formula if you want to left pad the value. then select all the cells and do Edit=copy, then Edit=Paste Special and select values. This replaces the formulas. then you can copy the sheet to a new workbook and save as above after autofitting the columns. An alternative is to use low level file io to write out the file a cell at a time/a line at a time using space padding as above. Unix uses vblf as a line terminator I believe, while windows uses vbcrlf. I assume Unix will be able to handle the file, but if not, you will have to use code to terminate with vblf. -- Regards, Tom Ogilvy "jeffP" wrote in message ... Hi all, I've got a project that takes a *dbf file into Excel, does some clean up etc, then I need to save it as a fixed length text file that's able to be imported into a Unix system. I've got most of it done but now I can't get it exported or saved into a fixed field length text file.I've worked w/ comma delimited files but this has me lost. I have 8 columns that need to be set w/ fixed character lengths even though they hold varying length data. Then I need to export/save the sheet as a text file for the Unix people. As I mentioned , I'm really lost / stuck and any help is , as always, greatly appreciated. -- jeff -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fixed TextBox with Shrink to fit text | Excel Discussion (Misc queries) | |||
convert fixed number to text. | Excel Discussion (Misc queries) | |||
Macro - Fixed text code needs replacing with variable text | Excel Discussion (Misc queries) | |||
FIXED TEXT AFTER DELETION | Excel Discussion (Misc queries) |