Varying Column Widths for Text Import

[Rytrilius]

Hi all,

I have limited experience with VBA, so I decided to seek some help here.

I need to import a text file into Excel, it does not contain commas, nor
special characters. I have 3 different layouts given to me by the user, for
example

Format 1:
Field 1. 3 chars
Field 2. 20 chars
Field 3. 13 chars

So the 3 different layouts have different column widths. The text file is
such that it loops from Format 1 (1 line) to Format 2 (20++ lines) to Format
3 (10+ lines) and then back to Format 1.
The only way to identify which format each line belongs to, is to search for
certain positions where certain text are present within the line. for example,
format 1 contains non-empty char in position 3 to 10, format 2 contains NULL
char in positions 14 to 30, format 3 contains 9s from postion 25 to 29...

I've recorded the macro to do the importing, but cannot seem to find the code
to tweak the varying column widths. any help is greatly appreciated!!

 

[Guest]

Hello,

Well, you don't necessarily need any VBA here, though it depends how easily
these different layouts can be identified. Excel has quite useful text
functions for these kinds of purposes.

I did not understand what you mentioned about NULL characters (layout 2).
Did you mean that each of the layouts has fixed length? Then you could test
for length of the column using LEN.

If column width is not fixed with each layout, you can use FIND for
identification:
1) NOT(ISERROR(FIND("9";A1;25)))
- if TRUE = belongs to layout 3
- "9" is the text to be searched, can be longer like "ABC"

Then you have to check whether the line belongs to layout 1 or 2 similarly.
You only need to search either for layout 1 or layout 2, as if you know that
"not layout 3" AND "not layout 2" => "layout 1"

After you have tested which line is which, you could use LEFT or MID command
to take the characters you need. This can be done by using IF -function.

Hope this helps.

Best Regards,

 

[Dave Peterson]

But you didn't describe each field for Format 2 and format 3.

Maybe you could base it on the sum of the lengths of the fields (format 1 has a
combined length of 36 characters).

If this sounds interesting,
can any of the fields be empty?
can any of the fields be shorter than the given length (can field 3 of format 1
be 1-12 characters)?

Maybe you could just add another column of cells that determine the format
type. Then use that in other formulas to parse each field:

Column B:
=IF(LEN(A1)=36,1,IF(LEN(A1)=59,2,3))
(whatever the lengths may be...)

Then in column C, grab the first field:
=IF(B1=1,TRIM(MID(A1,1,3)),"somethingelse")

in column D, grab the second field:
=IF(B1=1,TRIM(MID(A1,4,20)),"somethingelse")

and so forth.

If you can figure out a formula that goes in column B, it should work!

 

[Rytrilius via OfficeKB.com]

Thanks Joe and Dave,

The user's file is actually nicely padded with spaces (NULL) and zeroes so
all the rows are of the same length. I will try both your suggestions and see
if i can achieve anything.

Thank you very much!!

Regards,

Rytrilius