Last Column(=spaces) is Dropped When Importing from .Txt file

I am importing a text file from the mainframe in which the last
field of the record is spaces.  After transfer of the file down to my
PC as a text file, I then wish to import it into Excel.
Excel is truncating the last column and wiping out my record length
which needs to be a fixed record length.  After user corrections,
the Excel file will then be formatted back to text and processed
by another PC application.  Any suggestions on how to prevent
the truncation of the last column of my records?
Thanks! 

0
Ttown (2)
6/23/2005 3:24:05 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
350 Views

Similar Articles

[PageSpeed] 46

If the last column is always the same number of spaces you could set it as
=rept(" ",9) or however many spaces you need
if it is variable but you can calcualte however many you need by the other 
cells
try something like
=rept(" ",50-len(A1)-Len(B1)-len(c1)) etc.

"Ttown" wrote:

> I am importing a text file from the mainframe in which the last
> field of the record is spaces.  After transfer of the file down to my
> PC as a text file, I then wish to import it into Excel.
> Excel is truncating the last column and wiping out my record length
> which needs to be a fixed record length.  After user corrections,
> the Excel file will then be formatted back to text and processed
> by another PC application.  Any suggestions on how to prevent
> the truncation of the last column of my records?
> Thanks! 
> 
0
BJ (832)
6/23/2005 5:39:04 PM
Thanks for your reply,
but I forgot to say that this last column MAY contain spaces.
It does this time, but may not always.  

"bj" wrote:

> If the last column is always the same number of spaces you could set it as
> =rept(" ",9) or however many spaces you need
> if it is variable but you can calcualte however many you need by the other 
> cells
> try something like
> =rept(" ",50-len(A1)-Len(B1)-len(c1)) etc.
> 
> "Ttown" wrote:
> 
> > I am importing a text file from the mainframe in which the last
> > field of the record is spaces.  After transfer of the file down to my
> > PC as a text file, I then wish to import it into Excel.
> > Excel is truncating the last column and wiping out my record length
> > which needs to be a fixed record length.  After user corrections,
> > the Excel file will then be formatted back to text and processed
> > by another PC application.  Any suggestions on how to prevent
> > the truncation of the last column of my records?
> > Thanks! 
> > 
0
Ttown (2)
6/24/2005 4:30:02 PM
when you generate the data to go back,
do a len() function ad use the rpt function to make sure you have enough 
spaces.

"Ttown" wrote:

> Thanks for your reply,
> but I forgot to say that this last column MAY contain spaces.
> It does this time, but may not always.  
> 
> "bj" wrote:
> 
> > If the last column is always the same number of spaces you could set it as
> > =rept(" ",9) or however many spaces you need
> > if it is variable but you can calcualte however many you need by the other 
> > cells
> > try something like
> > =rept(" ",50-len(A1)-Len(B1)-len(c1)) etc.
> > 
> > "Ttown" wrote:
> > 
> > > I am importing a text file from the mainframe in which the last
> > > field of the record is spaces.  After transfer of the file down to my
> > > PC as a text file, I then wish to import it into Excel.
> > > Excel is truncating the last column and wiping out my record length
> > > which needs to be a fixed record length.  After user corrections,
> > > the Excel file will then be formatted back to text and processed
> > > by another PC application.  Any suggestions on how to prevent
> > > the truncation of the last column of my records?
> > > Thanks! 
> > > 
0
BJ (832)
6/24/2005 5:25:02 PM
Reply:

Similar Artilces:

quick way to combine 2 files?
If you have two excel files, both of which have 3 or 4 worksheets that are all used in lookup routines etc etc, is there an easy way to combine the two into a single file, as I think it won't work just by copying and pasting individual worksheets over in their entirety tks in advance LeeH Lee, With both workbooks open, group the sheets of one workbook (using Ctrl clicks on the tabs) then right click and select Move or Copy and then move them into the other workbook. HTH, Bernie MS Excel MVP "Lee Harris web" <lee.harris4@virgin.net> wrote in message news:32c0bd8d.04012...

How do I convert .txt files into .xls in order to be able to sum.
I receive via e-mail a statement that I need to resort by date , purchase order and total by department, etc. The process I have been using is to copy and paste into Excel. However, when I try to sum the $ is will not sum. Is there some other way of converting this statement that I receive via e-mail into Excel, so that I can resort and sum? I am using Excel 2002 and my e-mail browser is Mozilla. You might be able to use File-->Open, and then let the Text Import Wizard help. Otherwise, you could use the "data isn't recognized" fix here: http://www.officearticles.co...

How to read .pst file?
Supposed I have a .pst file that contains mails in Inbox folder. How can I write a program to read the those mails? The program must read from the .pst file without Outlook start-up. Any clue? See my answer in the Exchange newsgroup thread. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginners Guide to Microsoft Office Outlook 2003 Reminder Manager, Extended Reminders, Attachment Options http://www.slovaktech.com/products.htm "Roger" <anonymous@discussions.microsoft.com> wrote in message news:315C7206-100C-416E-88BF-976949AEDE90@microsoft.com....

Calendar in Publishing
I am creating a calendar in Publisher 2003. I am manually inputting my personal important dates, but would like to import general holidays - I couldn't find a template that has them. Are there any? "Snickerdodle" <Snickerdodle@discussions.microsoft.com> wrote in message news:36B8900A-F6E1-4495-967E-85998F45CCE9@microsoft.com... >I am creating a calendar in Publisher 2003. I am manually inputting my > personal important dates, but would like to import general holidays - I > couldn't find a template that has them. Are there any? I don't know the ...

"a document with the name" error on opening file
I'm getting an error message that says "A documents with the name " & filename & " is already open" (where filename is the actual name of the file) when I try to open certain files in Excel 97, when I don't really have a copy of the file in question opens. This error comes up pretty consistantly on one file that lives on a server, but I also get it when I try to open some local files. Anyone have any idea what would cause this & how to fix it? Thanks in advance. Best Jim Hi Jim have a look at: http://www.contextures.com/xlfaqApp.html#AlreadyOpen ...

move rows from column to column
I have Column b with Name in one row and number in 2nd row want to move all numbers to Column a and leave name in Column b without having to move each one seperate. 1500 records. Use a help column, in the first adjacent cell type =ISNUMBER(A2) where A2 is the first cell with name copy down 1500 rows by either dragging the formula or double click the lower right corner of the help cell (as long as it is in an adjacent column) Then apply data>filter>autofilter and filter on FALSE in the help columns, select all visible cells and copy and paste somewhere else, then select TRUE from ...

drag and drop problem with cell references
i have a cell with a formula in one worksheet that references another cell. the formula looks like this: =IF((ISBLANK('Draft'!$D25)),"",'Draft'!$D25) where 'draft' is another worksheet in my workbook. the formula works fine, it looks empty if there is nothing in the referenced cell('Draft'!$D25), otherwise it mirrors the content of that cell. If i type something in the referenced cell('Draft'!$D25), it still works great. But if i drag something to the referenced cell than the formula gets screwed up and replaces the $D25, with #ref. I am gue...

File in 2007 form
I was trying the Beta Office 2007 and made the mistake of saving one of you Excel file in the new 2007 form. I really need it back in the 2003 form. Problem: due to a variety of issues, I de-installed the Beta version and went back to 2003 but for got about the one file.. So; are there any utilities that anyone knows of the will convert this file back two 2003 form?? It is a simple Excel file, meaning that there are no clever or fancy formula or macros, just a collection of parts and part numbers gathered over MUCH time surfing various web sites. Any help or advise (beside the obvious....

word attachments on e-mail, docx files, recipient unable to open
I have been sending e-mails as I always have and attaching them in the way I always have, all of a sudden word documents are coming up as docx files and the recipients cannot open them, I have only recently gone onto Vista and microsoft offoce 2007. This has only happened recently with new docs that I have created on the new system. It is frustrating my customers and my hair is now falling out. Please help! That's because Word 2007 saves documents in a new format, .docx, unless told to do otherwise. There are two solutions: 1. Do a "Save As" and save documents in Word 97-...

My Account Settings, Icons, Files are "Missing"
Recently, I booted up and discovered that my personalized icons (under my account) had disappeared from the desktop. The Account Name is "Gilxxx" but my data is under "Curxxx" When I looked closer, I was able to see the directory structure in my "Documents and Settings" folders, but none of the files. The files are there, however, as I can play MP3s in Windows Media Player (and they are in my My Music directory). And, when I tried to add new desktop icons to "re-create" my old desktop, Windows tells me that a shortcut with that name alread...

Make Publisher default font and line spacing preferences availabl.
When bringing a mixture of text and graphics (in an ad for example) into one publisher file from another publisher file, publisher automatically changes the font of the imported box to Perpetua and changes the line spacing to 5.96 or something. I am assuming there is a way for this NOT to happen but I can't find it and it's not obvious in any of the help menus. ...

Problem cleaning up temporary files.
This isn't MFC specific so I apologize. I just like the information provided by the regular members of this group. I have an application that has a Rich Edit control. Next to the Rich Edit control is a Print button. To avoid all the details of printing, I decided to save the contents of the control to a RTF file, then do ShellExecuteEx("blahblah.rtf", "print", ...). This works fine. But, I sure would like to remove these temporary files. But I am finding that to be a tricky problem to solve. At first, I thought I would wait for the process started to end. Bu...

Virus in .mmd file
Hello, I speek english not well....I know. My anti-virus (norton) find a virus in a .MMD file stored in following folder : C:\Program Files\Microsoft BackOffice\Connectivity\POP3 Connector\InForward\ But the antivirus cannot access to this file, how i can remove this file ??? Thanks Configure NAV to exclude any data directories of Exchange (and other server products), otherwise file-level antivirus might damage databases. Then, in order to protect yourself from viruses, get Exchange-aware antivirus. J�r�my taverne wrote: > Hello, > > I speek english not well....I k...

Publisher File formats
I need to send file in a tif or jpeg format but when I save the file I still get the pub extension after the .tif' Can I not save in anything but pub? Thanks Ray, It would help us to answer your question if you told us which version of Publisher you are using. -- JoAnn Two things are aesthetically perfect in the world - the clock and the cat. --Emile-August Chartier "Ray" <anonymous@discussions.microsoft.com> wrote in message news:006101c3dc62$7649ce40$a501280a@phx.gbl... > I need to send file in a tif or jpeg format but when I > save the f...

dc as a file server
is it bad practice to have your dc as a file server as well. 100 people or less. One dc is Exchange and the other is the file server. Just wondering if I should put a member server in and move the file server over to that one? any thoughts appreciated. Not necessarily. This is common for branch offices to use the DC as the office file server also hth DDS "joe schmo" <joeschmo@discussions.microsoft.com> wrote in message news:3332409F-9184-4A3F-9DB3-1E8B57591C08@microsoft.com... > is it bad practice to have your dc as a file server as well. 100 peo...

overlaping dates in two files
MS Money 2001 deluxe on Windows XP Pro I archived my 2004 accounts and never balanced them. I decided i needed to and now have two files, both .mny files with 2003Archive.mny is from 1/2002 to 12/2004 Active2005.mny is from 1/2004 to present. I'd like to combine them into one file so I can balance the accounts (three main accounts) because my statements are not ending nicely on the end of the month. Please recommend the best method so I can balance the accounts. I think I need to conbine the files or somehow get the months that are now balanced in the 2003Archive.mny file to give...

tail or other realtime monitors for SMTPSVC exyymmdd.log files?
I'm trying to find an inexpensive way to spot-check / monitor the various log files in realtime for my Exchange environment of 2000 and 2003. I have tried a handful of "tail" implementations and all of them seem to work to a certain degree but none of them are able to successfully keep monitoring the text files that Exchange updates as part of the SMTPSVC. I'm using the W3C format and when I "tail" these files, it won't keep updating properly as new data is appended to the end of this file. Is anyone else monitoring their logs in realtime and if so, what util...

Change column color in chart when column value is over/under goal
No use of VBA or macros expected. It is believed to be Excel chart feature. Any ideas on how to change column colors (Red/Green) if value exceeds or meets the preset goal. Eg. if goal is 4% - anything at or above 4 should show green and under 4 should be red. Assuming your data is in A2:A20 B2: =IF(A2>=4,A2,NA()) C2: =IF(A2<4,A2,NA()) copy B2:C2 down, add some labels to B1:C1, then chart B1:C20. This will give you two series, one for the aboves, one for the rest. Select each data series, right click, choose format, and set the colour as required. -- --- HTH Bob (there's ...

File Attachment Problem
I have got the file attachment problem since we implemented the dynamics CRM 4.0. So i will be glad if you advise me a way to solve this problem. sincerally ...

Reference last entry in column
In Excel 2002, I need a way to reference the last entry in a column of data that has data added to it on a regular basis. Is there a way to do this that is fairly simple? I am trying to make a checkbook register in excel and I want to have the current balance shown in one place at the top so that when the list of entries gets long the user doesn't have to scroll to the bottom to see the current balance. I also want to have it show on a different sheet where the current balances for different accounts can all be seen one one page. Any help would be appreciated. Hi see: http://www.xl...

Import from Excel #2
Hi, Is there any way I can import from MS Excel to Money? -- Chanikya See http://xl2qif.chez.tiscali.fr/xl2qif_en.php "cndesu" wrote: > Hi, > Is there any way I can import from MS Excel to Money? > -- > Chanikya See also http://umpmfaq.info/faqdb.php?q=8. "cndesu" <cndesu@discussions.microsoft.com> wrote in message news:05433109-7FE5-4E53-8DE2-E3E2F5A8B34B@microsoft.com... > Is there any way I can import from MS Excel to Money? Hi, I have a pdf file consists of transactions which bank gave me online. Now Can I import this file to money? -- ...

Shrinking a Money File
I just upgraded to Money 2006 and I archived all my records prior to 01/01/2004. Is there a way to now shrink my money file in size? It's still at 20 Megs, same as it was before the archive. I assume since a majority of the data has been archive that I can compress all the open space in the file? I tried repairing the file (Standard and quick) and that didn't shrink the file. Does anyone know of a way? Thanks.... This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C58BE6.00FFF300 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-En...

[External Copy or SYLK] file format is not valid
I am moving to a new computer. I have WIN98SE on my old computer and WIN XP on the new one. I have Excel 97 and I am trying to transfer my data to the new computer. I get the above message when I try to open a file in XP. What is the problem? Thanks in advance, Bill. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Importing specific content into an Excel 2002 worksheet
I have some word documents that contain data I need throughout the document. As it stand, Ctrl+C and Ctrl+V are the easiest ways because the data is never in the same location on each document. Is there a way of capturing this data, and adding it to a selected field in Excel? The Word documents have a lot of 'noise' before, and after the data which is mostly contained in a table. This table also contains 'noise' that is not needed for the end result. Any suggestions are welcomed. Wayne. wrote: > I have some word documents that contain data I need throughout the &...

limits on hiding columns
are there any conditions that would limit the number of columns that can be hidden in Excel? Alternatively, why would excel not hide any more columns? Spreadsheet is not protected by the way Hi AFAIK you can hide all columns except of one remaining one -- Regards Frank Kabel Frankfurt, Germany m99panama wrote: > are there any conditions that would limit the number of columns that > can be hidden in Excel? Alternatively, why would excel not hide any > more columns? Spreadsheet is not protected by the way You might be thinking of hiding sheets, Frank? I think you can hide all ...