MS Excel question regarding importing a report in text format

Factual Questions
1

I’ve got a quick Excel question I’m hoping someone may have the answer to.

I have reports which are generated by one program, saved in text format, and then opened with Excel. I have no problems getting the data into my spreadsheet, but some of the columns are formatted in ways which Excel isn’t recoginizing correctly for me. My problems are:

  1. Dates. The report lists dates in the format MMDDYY or YYYYMMDD. For example today would be either 072507 or 20070705. Excel doesn’t seen this as the correct date for me.

  2. Negative numbers. The report puts the minus sign after the number for some reason. Excel hasn’t been recognizing 100- as -100.

So far these reports haven’t been all that large, so I’ve gone back and made the corrections manually as needed. But, it is still kind of time consuming, and if the reports ever get longer it may become impossible.

Any quick way to make Excel recognize the information the way I want it to?

Thanks for the help.

2

The Date function is what you need for this. If cell B5 contains the YYYYMMDD format, you can convert that to a date using the formula =Date(left(B5,4), mid(B5,5,2), right(B5,2)) and make sure the new cell’s formatted as a date. You can do something similar for MMDDYY format. If you have trouble with the year part, try


"20" & Right(B5,2)

for the year part.

One way to handle this, assuming the number’s in C3 would be

=IF(RIGHT(C3,1)="-", -1*LEFT(C3, LEN(C3) - 1), C3)

which detects whether the rightmost character is a minus sign, remove it and multiplies by -1 if yes. I’m sure someone will come up with a more elegant solution though.

3

A quick look at the “Text To Column” wizard (under the Data menu) shows that it claims to convert all possible date formats, and it also handles a trailing minus for negative numbers (this is selected in step 3 with the “Advanced” button).

If it works, you can record a macro and run it every time you want to convert one of these reports.

4

Appreciate the help, thanks for the quick replies. I think I’ve got it, and I look forward to trying this out on my next report.

5

I have the opposite situation. I need a flat text file, but the data come in as Excel files. Excel has a maximum record length of 240 bytes when writing a space delimited file, so longer files have to be imported to Access (which as my thread earlier today shows, I don’t know) and exported as text. (I can write longer records as tab-delimited, only the columns are not lined up when some fields are shorter than others – which is the normal case.)

Excel question: I’m using Excel 2003. Does Excel 2007 have the 240-byte record length limit like 2003? Or can it write longer space-delimited records?

6

Johnny, I’m not 100% what you need as output but you might be able to bypass Access and use Word. It sounds like you could select everything from A1 to the last active cell in the Excel file, copy it to the clipboard, paste it into Word, find-and-replace the tabs (coded as ^t) with single space characters, and save as a .txt file.

7

If I put it into Word the text wraps around to fit the page. My records need to be ‘flat’. That is, no wrapping/CRLF/etc. For example, let’s say that Word will fit ten Courier New characters on a page. (Of course it will fit more, but I want to keep sizes resonable here.) And let’s say my records are 37 bytes long. What I want is this:


1234567890123456789012345678901234567

What I get in Word would be like this:


1234567890
1234567890
1234567890
1234567
8

Got it. I was merely suggesting Word as a means to an end, not as the best way of viewing the resulting .txt file… which I envisioned as being used as an input file of some sort. Anyway, bottom line is this wouldn’t work for you… I’m happy to bow out and return everyone to their regularly scheduled thread.

9

I’m not entirely sure I’m understanding the question correctly, but I just entered 450 characters into cell A1 in Excel 2007. I then did a “Save As…” and chose “Text (MS-DOS) (*.txt)”. I got the usual warning that this file format doesn’t support all of the features of the worksheet (or whatever it says, blah blah blah), but my resulting text file is 452 bytes (CR/LF at the end I guess).

Does that help? If this experiment doesn’t answer your question let me know if I should have done something differently.

10

The data I work with is in more than just one column. There might be 20 columns. In order to work with the data, I need a .txt file in which all of the columns are lined up. If there are fewer than 240 characters in each row I can save as a .prn (space delimited) and everything is fine. If there are more than 240 characters, the part of the record that is longer than that limit will be moved to the bottom of the file. It wouldn’t be so bad if Excel just wrapped the text; that’s easy to code for. But by putting it at the bottom of the file there’s no way to correlate which ‘end bit’ goes with which ‘front bit’.

So the bottom line is: I need to create .txt files that have record lengths greater than 240 characters and in which all of the columns are aligned in the .txt file.

I can do this by importing the Excel file into Access, but it would be easier if Excel didn’t have that bizarre limit on it in the first place. I’m wondering if they fixed it in 2007.

Edit: The reason I’m interested is that if 2007 doesn’t have that limitation, the company might buy it for me. If it does have the limitation, I’ll just have to use Access.

11

It looks like 2007 is doing the same thing. I’ve never used .prn before, that’s weird. I wonder if it has something to do with the fact that it’s “Formatted Text” though.

I guess my MS-DOS .txt file is tab-delimited, but couldn’t you just save it that way and then use a text editor (or Word) to replace all tab characters with spaces? Not the function that some advanced editors have that replaces tabs with an equal number of space characters, but just something like “replace ’ ’ with ’ '”? I know you can do that easily in Word, and just because it appears to be word-wrapped in Word, doesn’t mean it is (if you keep the file in .txt format). Whatever you do, don’t use the clipboard.

Or maybe even easier, save as .csv and replace all commas with spaces? That won’t work if you have commas in your data though.

12

The problem with saving as Tab-delimited text is that some fields in a column are short. Thus the columns don’t line up within the text file.

Comma-delimited files aren’t lined up in columns at all. Again: the data has to be aligned in columns in the text file.

I got this in an email:

I’m not sure if that answers my question or not!

13

Aha, sorry. I guess that’s what I was missing. I didn’t realize the .prn format would line everything up. Does it pad each value out with spaces to the same width of the maximum-length value? Anyway, doesn’t matter, I understand now why none of my proposed options would help you.

I think the email is just saying that there are more cells in the spreadsheet now (the old Excel had a limit of 256 columns and 65536 rows. It took me a while to figure out the parenthetical values are supposed to be superscript, e.g. 2^8 not 28. Was the email formatted this way or is that a VBulletin thing? Anyway, I don’t think that helps you. The thing about long Unicode strings vs. limited-length byte strings I don’t think is any help either. And the thread-safety certainly has nothing to do with your problem.

Anyway, it just occurred to me that this is all a bit of a hijack to the OP. My apologies. I’ll let you know if I think of anything else that might help. At any rate, when I tried saving a long value to .prn in Excel 2007, it exhibited the same unusual behaviour you described with 2003.

14

Well, there’s always Access. I was hoping Excel 2007 would be fixed, but…

15

Perhaps I’m missing something, Johnny, but are you willing to try a little VBA programming? Seems to me that that might be an end run around the problems you’ve been facing.

(No, I don’t think the info in Post #12 applies to your problem.)

Well, yes, these little programming projects do usually turn out to be more complicated than one first hopes…

16

Well, I don’t know any VBA or how to implement it in Excel. Right now, with the answers I got in the Access thread, I can create the flat ASCII files I need. It would be good to have an easier/quicker way of doing it though.

I was wondering if I could save an Excel file as a comma-delimited text file. (I think I can.) Then I might be able to write a really ugly Easytrieve (the program I’m very good at) to parse each record and move what’s between the double-quotes into working storage and thence to the output record. It would take a lot of processing time, but our files are only several thousand records long so it won’t be that bad. Certainly quicker than manually moving stuff (cutting and pasting) in Excel, which is the way they’ve been doing it for years and years.

My boss gave me a ‘really ugly’ file today. Over 10,000 lines and flat-ASCII text. She’d been going into Notepad and deleting records by highlighting them and hitting the Delete key. I whipped out an Easytrieve that looked for attributes indicating a line was to be deleted. Once the program was written I cleaned up her file in four seconds. She was impressed. Heh. Since it was already a flat file, it was a piece of piss!

17

Here’s a vbscript to dump an Excel worksheet to CSV.