Need Help Importing Text File Using Two or More Spaces as the Delimiter

[jessiright77]

Hello. I would really appreciate someone's help with my problem.

I have been asked to figure out a way to automate the import of text
files into Excel. The import process MUST be automated by a macro
because the users are not able to do this on their own.

I have tried manually importing the file into Excel two different ways:
1. When I import using the "Delimited" data type with "Space" as the
delimiter, it doesn't work because each word of the 3 rows of headings
are separated into separate columns because of the single space between
them.

2. When I import using the "Fixed Width" data type, it doesn't work
because all the text on one row is placed into the first cell of each
row and, therefore, the columns of numbers are not aligned.

I have included a simplified version of the text file below. Does
anyone know how I can make this file automatically import using
**MORE** than one space as the delimiter (so it doesn't mess up all the
headings)? I would really appreciate your help.

Thanks,
Jessi


BENTLEY AREA AUTHORITY


Service Work Log Report 05/04/2006 2:45pm Page 1


Staff ID Name SAL No Date Payroll ----Reported
Hours---

728739 BROWN 099999 05/04/2006 8:00 5:00 3:00
478920 SMITH 097584 05/03/2006 8:00 4:00 4:00
347822 CUNNINGHAM 038477 05/03/2006 8:00 4:30 3:30
482730 DENNY 088887 05/03/2006 10:00 4:00 6:00

********** Total ********** 34.0 17.5 16.5

 

[Paul Mathews]

Hi Jessi, I copied your data into a text file and then imported it into a
spreadsheet by selecting the "Delimited" choice but starting the import at
row 10 (the row where the data actually starts); you can change the default
start row of 1 by changing the "Start import at row" property on the same
dialogue page where you indicate the "Delimited" choice. Click Next and
select the "Space" delimiter. Your data should parse okay now. The only
hitch is that you don't get the headers imported along with the data. What
I'd suggest, if you need those headers, is to set up a template spreadsheet
with the headers and then in the first available row below the headers
specify the leftmost cell as the one where the data import should start (this
is indicated in the last page of the import dialogue).

 

[JimMay]

Have you tried manually importing the file into Excel using the
"Delimited" data type with "Space" as the delimiter, and indicating on
the 1st screen of
The Wizard, Start with Row 4 (after the header)?

 

[jessiright77]

Thanks for the suggestions!

I could use a template for my headers and import the data beginning at
row 10... EXCEPT that I need the date and page number that appears on
the second header. Any ideas on how I could still pull that
information into the import?

Thanks again,
Jessi

 

[Paul Mathews]

Jessi, I think you'll still be okay if you start the data at row 4 (where the
date and page number resides) instead of row 10. The vital data itself still
parses okay and you now get the date and page number too (and they're parsed
nicely also

 

[jessiright77]

Another thought...

I created a template for my headers (without the date and page
numbers). But when I opened a worksheet based on the template and
tried to import the text file, it imported it into a NEW worksheet (not
the EXISTING worksheet I just began). How do I make it import into
the worksheet based on the template?

Right now I'm just playing around with this manually... I'll try to
figure out how to put it in a macro later.

Thanks!
Jessi

 

[Paul Mathews]

Right at the end of the data import process, you'll have a choice as to which
cell you want the imported data to start in and whether you'd like the import
in the current worksheet or a new one.

 

[jessiright77]

The sample that I posted was a "simplified" version so that everyone
could get a general idea of what I'm working with. The actual file
has more columns, and the column headers with two words in them (ie.,
SAL No) are split into separate columns when I import the data using
"space" as a delimiter. I thought it would be too confusing to paste
the sample "as is," but I have done so below so you can see the actual
file.

So... I think your template idea with the headers was a good idea... I
just need to figure out 1) how to capture the date and page number
information from the original file; and 2) how to import the data into
same worksheet as my template headers. What do you think?

Thanks!
Jessi

P.S. Here is the actual file:

BENTLEY AREA
AUTHORITY

Service Work Log
Report 05/04/2006 2:45pm Page 1


Staff ID Name SAL No Date Payroll
-------Reported Hours-------- % Lines Comp Comp

Hrs Total
Direct Non Dir Prep Earned Taken

432498 BROWN 748334 05/04/2006 8:00 5:00 5:00
0:00 0:0 62.5 8 0:00 0:00

399003 JACKSON 444321 05/03/2006 8:00 4:45 4:45
0:00 0:0 59.3 5 0:00 0:00

887790 SMITH 023445 05/02/2006 8:00 2:00 0:00
2:00 0:0 25.0 5 0:00 0:00

223456 CUNNINGHAM 665433 05/03/2006 8:00 4:50 3:50
1:00 0:0 60.4 9 0:00 0:00

333456 DENNY 055432 05/03/2006 10:00 4:30 2:45
1:45 0:0 45.0 9 0:00 0:00

********** Total ********** 42.0 21.0
16.3 4.7 0.0 50.1 36

 

[jessiright77]

Thanks for your assistance, Paul.

I do not see these options in my version of Excel (2002). The last
screen on the Import Wizard just lets you select the data type. Which
version are you using, or where specifically do you see this?

Thanks,
Jessi

 

[Paul Mathews]

Hi Jessi, I'm using Excel 2003. I don't have a copy of Excel XP so I can't
see what the import dialogues look like. If you'd like I can mail you a
screen shot to your email address.

 

[mark]

Jessi,

It looks like your data could be wrapping due to page constraints.
Does the program exporting from allow you change / use landscape
orientation rather than letter?

Mark
Systems Administrator

 

[Tom Ogilvy]

Paul,
you using something from the data menu. I doubt Jessi is.