Importing File greater than 65536

[Dan]

I need to import a file greater than 65536 lines. I have read and tried
Microsofts macro on this (http://support.microsoft.com/kb/120596/en-us) but I
need the file to be in a Fixed Width format and not the Delimited format the
above macro provided.

How do I switch the import over to Fixed Width?

Thanks

 

[Dave Peterson]

I'd cheat.

I'd use another program to split the text file into smaller pieces--each with
less than 64k lines. Then import each of those sections.

 

[Dan]

The problem is not the file size but the format. The macro already takes care
of the lenght. The reason for this is I need to proces the data with
additional macros based on the informtion.

The macro from Microsoft is:

Do While Seek(FileNum) <= LOF(FileNum)
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr <----- EDIT HERE?
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr <---- EDIT HERE?
End If

Is there a way to modify the macro at either the above two places to change
the data into a Fixed Width format?

Thanks

 

[Dave Peterson]

You can read the string intor resultStr, then parse the data with a mid's and
trims (and maybe values)?

Without knowing anything at all about your data, maybe this will help you.



dim myName as string
dim myAddress as string
dim myPhone as string
dim mySSN as long
dim DestCell as range
dim lCtr as long
dim FileNum as long
dim myFileName as variant

....

set destcell = nothing

Close FileNum
Open myFileName For Input As FileNum
lCtr = 99999
Do While Not EOF(FileNum)
line input #filenum, resultstr
lctr = lctr + 1
if lctr > 40000 then
set destcell = worksheets.add.range("a1")
lctr = 1
else
set destcell=destcell.offset(1,0)
end if
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
mySSN = value(trim(mid(resultstr, ...

with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

==========
But I would think that this would be tons slower than splitting the file into
pieces and then just importing those pieces.

 

[Dan]

I will try what you suggested. Here is an example of a data file:

The first 4 lines I want in a delimited format so you can read it.

date Wed Aug 9 01:04:31 pm 2006
internal events logged
Begin Triggerblock Wed Aug 9 01:04:31 pm 2006
0.0000 Start of measurement
0.0044 2 2C0 Rx d 8 FF FF FF FF 7B 00 FF 40
0.0262 2 402 Rx d 8 FD 07 3F FF FF FF FF FF
0.0292 2 0 Rx d 6 85 00 00 00 00 00
0.0408 2 14 Rx d 7 00 3C E7 FF 19 64 5E
0.0463 2 2 Rx d 8 03 C2 FF FF FF FF FF FF
0.0476 2 1A8 Rx d 1 01
0.0951 2 394 Rx d 6 06 80 00 00 00 00
0.0964 2 3F8 Rx d 6 04 00 00 FF 7B 00
0.0979 2 2 Rx d 8 03 C2 FF FF FF FF FF FF

The remaining lines (which may be longer than 65536 lines) I want to pull it
in a Fixed format so each value is in its own cell for additional processing.
The values are in HEX and I will processes them with additional macros I
already created.

Thanks

 

[Dave Peterson]

I would still break up the file outside of excel, then just use the import
wizard to layout the fields.

I would think that the data would have the same layout. If that's the case, I'd
record a macro when I did one section manually. Then rerun that macro against
the other sections.

I will try what you suggested. Here is an example of a data file:

The first 4 lines I want in a delimited format so you can read it.

date Wed Aug 9 01:04:31 pm 2006
internal events logged
Begin Triggerblock Wed Aug 9 01:04:31 pm 2006
0.0000 Start of measurement
0.0044 2 2C0 Rx d 8 FF FF FF FF 7B 00 FF 40
0.0262 2 402 Rx d 8 FD 07 3F FF FF FF FF FF
0.0292 2 0 Rx d 6 85 00 00 00 00 00
0.0408 2 14 Rx d 7 00 3C E7 FF 19 64 5E
0.0463 2 2 Rx d 8 03 C2 FF FF FF FF FF FF
0.0476 2 1A8 Rx d 1 01
0.0951 2 394 Rx d 6 06 80 00 00 00 00
0.0964 2 3F8 Rx d 6 04 00 00 FF 7B 00
0.0979 2 2 Rx d 8 03 C2 FF FF FF FF FF FF

The remaining lines (which may be longer than 65536 lines) I want to pull it
in a Fixed format so each value is in its own cell for additional processing.
The values are in HEX and I will processes them with additional macros I
already created.

Thanks

 

[Dan]

I'll try both formats and see which works quicker.

Question on the code you provided:
1. How do I change it so it starts in the existing Sheet that is opened?
2. How do I modify so that the new sheet is goes into Sheet2 (that is
already opened).
3. Want to create the macro as an "exe" so it can be placed on the desktop
and run automatically without the need to launch Excel.

Thanks

 

[Dave Peterson]

#1. Set DestCell = Worksheets("Sheet1").range("a1")

#2. Keep track of what sheet number you're on and then you can use:
SheetCtr = SheetCtr + 1
set DestCell = worksheets("Sheet" & sheetctr).range("a1")

#3. You can't compile your VBA code into a .EXE.
But you could name your macro Autpen and then when the workbook
that holds that macro opens, the macro will start.

I'll try both formats and see which works quicker.

Question on the code you provided:
1. How do I change it so it starts in the existing Sheet that is opened?
2. How do I modify so that the new sheet is goes into Sheet2 (that is
already opened).
3. Want to create the macro as an "exe" so it can be placed on the desktop
and run automatically without the need to launch Excel.

Thanks

 

[Dan]

Thanks for all the assistance. With a a few additions it is working how I
wanted. I wil try dividing the intial file up and importing using the Wizard
format to see which works more efficiently.