Issue With Jet 4.0... Could Not Find Installable ISAM

[Irfan Khan]

Hi,

Whenever I run following code I am getting "Could Not Find Installable
ISAM".... Please help... I have update registry also according to microsoft
support suggestions...

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
strFilePath & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Regards
Irfan Khan

 

[Joel]

This much will work. Not sure of the options you picked and whered they came
from. I emliminate some of the options in the open statement to get it to
work.

Dim OConn As New ADODB.Connection
strFilePath = "c:\temp\abc.mdb"

OConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
strFilePath & ";"

 

[Irfan Khan]

Hi Joel,

Thanks for reply, but i m trying to open text file with that...

Regard

 

[Joel]

A text file I don't thnk can be opened as a database. there is no structure
to a text file. There are other methods to open text files which will work.
What are you really trying to do?

 

[Irfan Khan]

Hi,

I need to open a text file which is of 20 MB and put it into different
sheets and then run macro to extract useful info out of those, the solution i
m having as of now is which i posted earlier... could you please help me...

Thanks a Lot !!!

 

[Joel]

I need to know more about your text file. With such a large file, it is
better to read data one line at a time and put the data in the correct sheet
in the workbook rather than read the whole thing into the workbook at one
time and then move the data. Give sample of text file and how you want it
seperated.

Usually this type of task there are page/block seperators in the text file
and each page/block will go on its own sheet. Done this thing lots of times.

 

[Irfan Khan]

Hi Joel,

Ok, I will be comfortabsle with line by line reading but that is also not
working... following is the format of my text file.... I have to collect
value for each parameter out of these....

SEG-0001 DLM00461
BCF-0001 BTS-0001 DLM00461
-------------------------------------
BTS ADMINISTRATIVE STATE ... UNLOCKED
BTS OPERATIONAL STATE ...... BL-RST
BTS BACKGROUND DATA STATE .. NOT DEFINED

CELL IDENTITY............................(CI)..... 00461
FREQUENCY BAND IN USE....................(BAND)... 1800

BS IDENTITY CODE.........................(BSIC)

NETWORK COLOUR CODE...................(NCC).... 4
BACKGROUND NETWORK COLOUR CODE........(BNCC)... -

BTS COLOUR CODE.......................(BCC).... 1
BACKGROUND BTS COLOUR CODE............(BBCC)... -

LOCATION AREA ID.........................(LAI)
MOBILE COUNTRY CODE...................(MCC).... 405
MOBILE NETWORK CODE...................(MNC).... 800
LOCATION AREA CODE....................(LAC).... 00112

BTS HOPPING MODE.........................(HOP).... RF
BACKGROUND BTS HOPPING MODE..............(BHOP)... -
UNDERLAY BTS HOPPING MODE................(UHOP)... N
BACKGROUND UNDERLAY BTS HOPPING MODE.....(BUHOP).. -

MOBILE ALLOCATION FREQUENCY LIST......(MAL).... 1
BACKGROUND MOBILE ALLOC FREQ LIST.....(BMAL)... -
UNDERLAY MOBILE ALLOC FREQ LIST.......(UMAL)... -
BG UNDERLAY MOBILE ALLOC FREQ LIST....(BUMAL).. -

HOPPING SEQUENCE NUMBER 1.............(HSN1)... 1
BACKGROUND HOPPING SEQUENCE NUMBER 1..(BHSN1).. -
HOPPING SEQUENCE NUMBER 2.............(HSN2)... 0
BACKGROUND HOPPING SEQUENCE NUMBER 2..(BHSN2).. -
UNDERLAY HOPPING SEQUENCE NUMBER......(UHSN)... 0
BG UNDERLAY HOPPING SEQUENCE NUMBER...(BUHSN).. -

CELL BARRED..............................(BAR).... N
CALL RE-ESTABLISHMENT ALLOWED............(RE)..... Y
EMERGENCY CALL RESTRICTED................(EC)..... N
PLMN PERMITTED...........................(PLMN)... 04
NOT ALLOWED ACCESS CLASSES...............(ACC).... -
ADJACENCY ON OTHER BAND..................(DBC).... Y
DIRECTED RETRY USED......................(DR)..... Y
INTELLIGENT DIRECTED RETRY USED..........(IDR).... N
CELL TYPE................................(CTY).... GSM
MIN TIME LIMIT DIRECTED RETRY............(MIDR)... 1
MAX TIME LIMIT DIRECTED RETRY............(MADR)... 5
DIRECTED RETRY METHOD....................(DRM).... 0

CELL RESELECT HYSTERESIS.................(HYS).... 06 dB
MS TXPWR MAX CCH.........................(TXP1)... 33 dBm
MS TXPWR MAX CCH 1X00....................(TXP2)... 30 dBm
RXLEV ACCESS MIN.........................(RXP)....-105 dBm
RADIO LINK TIMEOUT.......................(RLT).... 20 SACCH BLOCKS
POWER OFFSET.............................(PO)..... 00 dB
GPRS RXLEV ACCESS MIN....................(GRXP)...-105 dBm
GPRS MS TXPWR MAX CCH....................(GTXP1).. 33 dBm
GPRS MS TXPWR MAX CCH 1X00...............(GTXP2).. 30 dBm
GPRS NON BCCH LAYER RXLEV UPPER LIMIT....(GPU)....-095 dBm
GPRS NON BCCH LAYER RXLEV LOWER LIMIT....(GPL)....-100 dBm
DIRECT GPRS ACCESS.......................(DIRE)... 0 dBm
RA RESELECT HYSTERESIS...................(RRH).... 4 dB
PRIORITY CLASS...........................(PRC).... 0
HCS THRESHOLD............................(HCS).... -

MAX QUEUE LENGTH.........................(MQL).... 025 %
TIME LIMIT CALL..........................(TLC).... 05 SECONDS
TIME LIMIT HANDOVER......................(TLH).... 02 SECONDS
QUEUE PRIORITY USED......................(QPU).... Y
QUEUEING PRIORITY CALL................(QPC).... 10
QUEUEING PRIORITY URGENT HANDOVER.....(QPH).... 09
QUEUEING PRIORITY NON-URGENT HANDOVER.(QPN).... 09
MS PRIORITY USED.........................(MPU).... Y

NUMBER OF BLOCKS FOR ACCESS GRANT MSG....(AG)..... 02
NUMBER OF MULTIFRAMES....................(MFR).... 06
TIMER FOR PERIODIC MS LOCATION UPDATING..(PER).... 04.0 HOURS
ALLOW IMSI ATTACH DETACH.................(ATT).... Y

C/N THRESHOLD............................(CNT).... 00 dB (NOT USED)
AVERAGING PERIOD.........................(AP)..... 06
BOUNDARY 0...............................(BO0)....-110 dBm
BOUNDARY 1...............................(BO1)....-105 dBm
BOUNDARY 2...............................(BO2)....-100 dBm
BOUNDARY 3...............................(BO3)....-095 dBm
BOUNDARY 4...............................(BO4)....-090 dBm
BOUNDARY 5...............................(BO5)....-047 dBm

DTX MODE.................................(DTX).... 1 (MS SHALL USE DTX)
BTS MEASURE AVERAGE......................(BMA).... 01
MS TX PWR MAX GSM........................(PMAX1).. 33 dBm
MS TX PWR MAX GSM1X00....................(PMAX2).. 30 dBm
MS TX PWR MIN............................(PMIN)... 00 dBm
MAX NUMBER OF RETRANSMISSION.............(RET).... 04
MAX NUMBER OF REPETITION.................(NY1).... 05
NUMBER OF SLOTS SPREAD TRANS.............(SLO).... 10
SMS CB USED..............................(CB)..... Y
BTS LOAD THRESHOLD.......................(BLT).... 070 %
MS MAX DISTANCE IN CALL SETUP............(DMAX)... 255 (NO REJECTION)
TRX PRIORITY IN TCH ALLOCATION...........(TRP).... 0
CELL LOAD FOR CHANNEL SEARCH.............(CLC).... 0 %
RX DIVERSITY.............................(RDIV)... N
TCH RATE INTRA-CELL HANDOVER.............(TRIH)... 0
(NO CONSTRAINTS)
LOWER LIMIT FOR FR TCH RESOURCES.........(FRL).... 100 %
UPPER LIMIT FOR FR TCH RESOURCES.........(FRU).... 0 %
NEW ESTABLISHMENT CAUSES SUPPORT.........(NECI)... N
RADIUS EXTENSION.........................(EXT).... 00 km (NORMAL CELL)
MULTIBAND CELL REPORTING.................(MBR).... 1
EARLY SENDING INDICATION.................(ESI).... Y
NON BCCH LAYER OFFSET....................(NBL).... 0 dBm
BTS LOAD IN SEG..........................(LSEG)... 70 %
SCALE ORD................................(SCO).... 0
AMH UPPER LOAD THRESHOLD ................(AUT).... NOT USED
AMH LOWER LOAD THRESHOLD ................(ALT).... NOT USED
AMH MAX LOAD OF TARGET CELL .............(AML).... NOT USED
TRHO GUARD TIME .........................(TGT).... NOT USED
DL NOISE LEVEL ..........................(DLN)... -117 dB
UL NOISE LEVEL ..........................(ULN)... -117 dB
C2 RESELECTION PARAMETERS:
CELL RESELECTION PARAMETER INDEX......(PI)..... N
CELL BAR QUALIFY......................(QUA).... N
CELL RESELECT OFFSET..................(REO).... 0 dB
TEMPORARY OFFSET......................(TEO).... 0 dB
PENALTY TIME..........................(PET).... 20 s

TRUNK RESERVATION USED...................(TR)..... N
LIMIT FOR FREE TCHS......................(LIMIT).. 16
TCHS RESERVED FOR PRIORITY SUBSCRIBERS...(TCRP)... 0
RESTRICTED USE OF PRIORITY CHANNELS......(RUP).... Y
RESERVATION METHOD.......................(REM).... DYN


Thanks a Lot !!!

 

[Joel]

This looks like a class project I had in college where the teacher gavbe us
bad input data to make sure we did all the proper error checking. The data
you provided has lot of different formats which makes reading very hard. You
have to do a lot of testing of the data to import the data correctly.

You need to change the file name in the code below to match your input file.
I can modify the code as required. If you tell me how to determine where
the breaks occur I can modify the code to put the data in more than one
worksheet.





Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


'default folder
Folder = "C:\temp\"
FName = "abc.txt"


Cells.NumberFormat = "General"

Set fsread = CreateObject("Scripting.FileSystemObject")

Set fread = fsread.GetFile(Folder & FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

RowCount = 1
Do While tsread.atendofstream = False

InputLine = Trim(tsread.ReadLine)
ColCount = 1
If Len(InputLine) > 0 Then
'check for dots
If InStr(InputLine, ".") > 0 Then
Do While InStr(InputLine, ".") > 0
'check if there is a space as the 1st character
If Left(InputLine, 1) = " " Then
InputLine = Trim(InputLine)
Data = Left(InputLine, InStr(InputLine, " ") - 1)
Cells(RowCount, ColCount) = Data
InputLine = Trim(Mid(InputLine, InStr(InputLine, " ")))
Cells(RowCount, ColCount + 1) = InputLine
Exit Do
Else
Data = Left(InputLine, InStr(InputLine, ".") - 1)
Cells(RowCount, ColCount) = Data
InputLine = Mid(InputLine, InStr(InputLine, "."))
'remove leading dots
Do While Left(InputLine, 1) = "."
InputLine = Mid(InputLine, 2)
Loop
End If
ColCount = ColCount + 1
Loop
InputLine = Trim(InputLine)
If Len(InputLine) > 0 Then
If InputLine = "NOT USED" Then
Cells(RowCount, ColCount) = InputLine
Else
If InStr(InputLine, " ") > 0 Then
Data = Left(InputLine, InStr(InputLine, " ") - 1)
Cells(RowCount, ColCount) = Data
InputLine = Trim(Mid(InputLine, InStr(InputLine, " ") + 1))
ColCount = ColCount + 1
End If
End If
Cells(RowCount, ColCount) = Trim(InputLine)
End If
Else
If InStr(InputLine, " ") > 0 Then
Do While InStr(InputLine, " ") > 0
Data = Left(InputLine, InStr(InputLine, " ") - 1)
Cells(RowCount, ColCount) = Data
InputLine = Trim(Mid(InputLine, InStr(InputLine, " ")))
ColCount = ColCount + 1
Loop
End If
If Len(InputLine) > 0 Then
Cells(RowCount, ColCount) = InputLine
End If
End If
RowCount = RowCount + 1
End If
Loop
tsread.Close
End Sub

 

[Joel]

I made some minor changes to the code to make it better. I can't believe
this isn't a class project since the data seems to be a hodgepodge of
different data the wouldn't be coming from the same source. I looks exactly
like a project a teacher would give as an assignment.

I'm not sure how you want the number to appear. The code will remove
leading zeroes unless you want me to change the output numbers all to text.
Then you can't use these numbers for mathematical calculations.

The code is also combining some notes and the data type together in the same
cell like below.

dB (NOT USED)

I can easily seperate these two items into different columns.

Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


'default folder
Folder = "C:\temp\"
FName = "abc.txt"

Cells.ClearContents
Cells.NumberFormat = "General"

Set fsread = CreateObject("Scripting.FileSystemObject")

Set fread = fsread.GetFile(Folder & FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

RowCount = 1
Do While tsread.atendofstream = False

InputLine = Trim(tsread.ReadLine)
ColCount = 1
If Len(InputLine) > 0 Then
'check for dots
If InStr(InputLine, ".") > 0 Then
Do While InStr(InputLine, ".") > 0
'check if there is a space as the 1st character
If Left(InputLine, 1) = " " Then
InputLine = Trim(InputLine)
Data = Left(InputLine, InStr(InputLine, " ") - 1)
Cells(RowCount, ColCount) = Data
InputLine = Trim(Mid(InputLine, InStr(InputLine, " ")))
Cells(RowCount, ColCount + 1) = InputLine
InputLine = ""
Exit Do
Else
Data = Trim(Left(InputLine, InStr(InputLine, ".") - 1))
Cells(RowCount, ColCount) = Data
InputLine = Mid(InputLine, InStr(InputLine, "."))
'remove leading dots
Do While Left(InputLine, 1) = "."
InputLine = Mid(InputLine, 2)
Loop
End If
ColCount = ColCount + 1
Loop
InputLine = Trim(InputLine)
If Len(InputLine) > 0 Then
If InputLine = "NOT USED" Then
Cells(RowCount, ColCount) = InputLine
Else
If InStr(InputLine, " ") > 0 Then
Data = Left(InputLine, InStr(InputLine, " ") - 1)
Cells(RowCount, ColCount) = Data
InputLine = Trim(Mid(InputLine, InStr(InputLine, " ") + 1))
ColCount = ColCount + 1
End If
Cells(RowCount, ColCount) = InputLine
End If
End If
Else
If InStr(InputLine, " ") > 0 Then
Do While InStr(InputLine, " ") > 0
Data = Left(InputLine, InStr(InputLine, " ") - 1)
Cells(RowCount, ColCount) = Data
InputLine = Trim(Mid(InputLine, InStr(InputLine, " ")))
ColCount = ColCount + 1
Loop
End If
If Len(InputLine) > 0 Then
Cells(RowCount, ColCount) = InputLine
End If
End If
RowCount = RowCount + 1
End If
Loop
tsread.Close
End Sub