Pls help! Txt to Xls

[nick]

Hi,

I have a txt file with a lot of data which im trying to export to excel. The
data is not seperated by any comma, space or anything. All the data is
together but i know how many characters goes into each cell of every column.
I dont want to go thru fixed length wizard as it takes a lot of time. Can
anyone help me with this plz?

EX: The format of the 1st line in txt file is
04MKK 9900000001/ 2INSUINSU23234563 / /
/ / / // /
/ //CRS Insured Name 1
CRS2 Insured Name 1
/ / / / /// / // 1100000001
M// /
New

I cannot consider spaces to delimt the file as there is no data for some
records in those spaces but some do. lets say the first cell of the 1st
column is 3 charachters, 1st cell of second column should have 2 char etc.
The ultimate format shld be like this

04M KK 9900000001 / /

 

[Doug Kanter]

Hi,

I have a txt file with a lot of data which im trying to export to excel.
The
data is not seperated by any comma, space or anything. All the data is
together but i know how many characters goes into each cell of every
column.
I dont want to go thru fixed length wizard as it takes a lot of time. Can
anyone help me with this plz?

EX: The format of the 1st line in txt file is
04MKK 9900000001/ 2INSUINSU23234563 / /
/ / / // /
/ //CRS Insured Name 1
CRS2 Insured Name 1
/ / / / /// / // 1100000001
M// /
New

I cannot consider spaces to delimt the file as there is no data for some
records in those spaces but some do. lets say the first cell of the 1st
column is 3 charachters, 1st cell of second column should have 2 char etc.
The ultimate format shld be like this

04M KK 9900000001 / /

Nick, you posted this question yesterday. It would be best for you if you'd
stay with one conversation so we can narrow things down. Now, onward:

1) Question: Why do you think the slashes cannot be gotten rid of
completely? Whoever sent you this file - do they say you have to keep the
slashes?

2) Question: You said you don't want to use the wizard because it takes too
much time. How often do you need to import this file?

 

[nick]

Hi,

Yes the slashes have to be there. wherever u see spaces and slashes, they r
considered as characters as well which means, they could go in to any cell.
bottom line is i cant get rid of anything from there...no letters, spaces,
slashes...nothing. every cell has a fixed character irreespective of letters,
numbers, spaces, / etc.

I dont wanna use the wizard as i have 5 files like these for now and they
keep getting updated very often.

Thank you

 

[Doug Kanter]

Hi,

Yes the slashes have to be there. wherever u see spaces and slashes, they
r
considered as characters as well which means, they could go in to any
cell.
bottom line is i cant get rid of anything from there...no letters, spaces,
slashes...nothing. every cell has a fixed character irreespective of
letters,
numbers, spaces, / etc.

I dont wanna use the wizard as i have 5 files like these for now and they
keep getting updated very often.

Thank you

Assuming that the field widths are consistent, you can record a macro while
going through the fixed length import steps manually. The macro will not
only record the steps you take, but also the name of the text file you
opened. So, assuming the 5 file names remain the same (or that you can
rename them with a consistent pattern), this will work fine. Once you're
sure the macros work, you could attach them to custom toolbar buttons so
this becomes a one-click operation for each file.

Within Excel, this is your only choice.

There may be other possibilities if you can tell me what software produced
the original text files.

 

[nick]

Hi doug,

Thanx a lot for your help. it definitely cuts down my work later when these
files are updated. one thing is that every file and even in every file, every
cell has different widths. Its not consistant. but one thing i cld definitely
do with your advise is when these files get updated, the width is still gonna
be the same for that particular file. about the software, the company has its
own database which produces these files.

Thank you very much. Please do let me know if you could come up with any
other ideas. thanks

 

[Doug Kanter]

Hi doug,

Thanx a lot for your help. it definitely cuts down my work later when
these
files are updated. one thing is that every file and even in every file,
every
cell has different widths. Its not consistant. but one thing i cld
definitely
do with your advise is when these files get updated, the width is still
gonna
be the same for that particular file.

Then, record a different macro for each file. We're talking what....2
minutes each for this step?

about the software, the company has its
own database which produces these files.

I'll bet you a year's pay that if you spoke to the right person, they could
provide you with much better text files. Mainframe geeks will get away with
anything they can until someone calls and they realize their sloppiness has
been noticed. Ask for something better. They may be able to give you a very
generic and dependable format like .DBF (ask about that). Or, a real fixed
length file, or somehow delimited.

 

[Doug Kanter]

Hi,

I have a txt file with a lot of data which im trying to export to excel.
The
data is not seperated by any comma, space or anything. All the data is
together but i know how many characters goes into each cell of every
column.
I dont want to go thru fixed length wizard as it takes a lot of time. Can
anyone help me with this plz?

EX: The format of the 1st line in txt file is
04MKK 9900000001/ 2INSUINSU23234563 / /
/ / / // /
/ //CRS Insured Name 1
CRS2 Insured Name 1
/ / / / /// / // 1100000001
M// /
New

I cannot consider spaces to delimt the file as there is no data for some
records in those spaces but some do. lets say the first cell of the 1st
column is 3 charachters, 1st cell of second column should have 2 char etc.
The ultimate format shld be like this

04M KK 9900000001 / /

TIME OUT! HANG ON A MINUTE! The presence of the slashes makes absolutely no
sense. Is it possible that THOSE are your delimiters? Right now, just for
grins, try using Excel's delimited import method. On the 2nd screen, unclick
TAB, click Other, and put a slash in the box to the right of other.

You said those slashes existed as data, but why? It makes no sense, if the
sample above is any indication. What possible meaning could all those
slashes have?

 

[nick]

Hi,

Ok, the process here is...they r transfering all the data from one
application to the other. the slashes are nothing but an indication to input
all the / fields manually in the new system. Hope whateva i said makes sense.
its part of SAP implementation. The new system is SAP

 

[nick]

can i use left function? can u help me out how i use this function? like ex:
=LEFT(o_CRSF_BP_20060213.txt!$A$1,Sheet1!B1)
=left(txt file,i inserted all the char len in row b( lets say in this case
its 2))
When i did the above function, it gives me the first 2 chars of that txt
file. in the same way can i use left func in cell 2 starting with 3 char of
that txt file till what ever the char length for that cell is?

 

[Doug Kanter]

Hi,

Ok, the process here is...they r transfering all the data from one
application to the other. the slashes are nothing but an indication to
input
all the / fields manually in the new system. Hope whateva i said makes
sense.
its part of SAP implementation. The new system is SAP

They can definitely give you a better file to work with. Just ask.