this is my text file 30 MB and I'

[yesilkalem]

this is my text file 30 MB and I'm importing this to access.. and I have to
write fill the blank cells with the filled above.
could you please help me about filling the empty cells in number and date
section

thanks..

number date acct XX amount unit

20001871 02/01/2001 291 B 1,000 USD
39501 A 1,000 USD
20001873 02/01/2001 291 B 7,100 ATS
39501 A 7,000 ATS
39502 A 100 ATS
20001876 02/01/2001 291 B 23,000 DEM
39501 A 22,000 DEM
39502 A 1,000 DEM
20001880 02/01/2001 291 B 5,000 ATS
39501 A 5,000 ATS

 

[MichaelRed]

Well, it is certainly NOT difficult. Long, boring, tedious -yes.

But, FIRST, some additional info needs to be furnished (or at least confirmed).

It is a 60 chracter 'Record' of 6 fields (and seperator columns), which could be described as:

Type FieldRec
Numb String * 11
SepString1 * 1
DateFld * 10
SepString2 * 1
Acct String * 9
SepString3 * 1
XX String * 2
SepString4 * 1
Amount String * 19
SepString5 * 1
Units String * 3
End Type

You intend/want/need to maintain the "information" in your 30MByte TEXT file.

So your process would be to read a record, if the first two fields are "empty", then replace them with the previous (non-empty fields') information from the previous records's information and re-write the record to the SAME file?

If you can confirm (or correct) this info, the actual process can be accoomolished.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[yesilkalem]

this is my sample file .. I have tons of 30 mb file like this. you understood right. addition to this I have an explanation field. with excel I was concatenating these explanations in a one cell bu data is very big now. I want to write unified (concatenated) data in every explanation cell .. thanks




below.. please copy it to a text file ..
--------------------------------------------------------------------------------
20067041 17/07/2001 118112 B 15,000,000 TL ALI DEMIRESIK TAKIP HES.
010 A 15,000,000 TL YATAN...
--------------------------------------------------------------------------------
20067043 17/07/2001 010 B 15,000,000 TL ALI DEMIRESIK TAKIP HES.Y
118112 A 15,000,000 TL ATAN..
--------------------------------------------------------------------------------
20067064 17/07/2001 010 B 15,000,000 DEM ALI DEMIRESIK TAKIP HESAB
118112 A 15,000,000 DEM INA TAHSIL EDILMEK UZERE
SEHVEN TEDIYE KESILDIGIND
EN 20067041 NOLU FIS TASH
IHI...
--------------------------------------------------------------------------------
20067271 17/07/2001 010 B 50,000,000 ATS ISMAIL GIRGIN NIN IDARI T
118112 A 50,000,000 ATS AKIP HESABINA YAPILAN TAH
--------------------------------------------------------------------------------
20067619 18/07/2001 010 B 28,968,343 TL NURI SARI NIN IDARI TAKIP
118112 A 28,968,343 TL TEKI HESABINA YAPILAN TAH
--------------------------------------------------------------------------------
20067948 18/07/2001 010 B 96,471,513 TL MEHMET MERT AIT TAKIP HES
514200 A 4,933,398 TL ABINDA IZLENEN YAPILAN TA
118112 A 78,934,365 TL HSILAT ILE KAPATILAN...
380005 A 246,670 TL
27802 A 11,863,740 TL
390220 A 493,340 TL
--------------------------------------------------------------------------------
20068402 19/07/2001 010 B 30,000,000 TL IBRAHIM ALAK IN TAKIP HES
118112 A 30,000,000 TL ABINA TAHSIL EDILEN,,,,,,
--------------------------------------------------------------------------------
20068491 19/07/2001 010 B 42,572,462 TL MUSTAFA YALCIN NIN TAKIPT
514200 A 1,622,416 TL EKI HESABINA YT
118112 A 28,842,943 TL
380005 A 81,121 TL
27802 A 11,863,740 TL
390220 A 162,242 TL
--------------------------------------------------------------------------------
20068898 20/07/2001 010 B 500,000 USD SUKRIYE AKYORUK TEN AKBAN
118112 A 500,000 USD K CEKI BEDELI OLARAK TAKI
--------------------------------------------------------------------------------
20069327 20/07/2001 392031 B 500,000,000 TL 067 KUTAHYA SUBESINDE
118112 A 500,000,000 TL N GELEN 20069308 GIRIS FI
S NOLU VE 20/07/2001 TARI
HLI ELEKTRONIK POSTANIN
KAPATILMASI SULEYMAN SAR
NIN TAKIPTEKI KREDILI BAN
KOMAT HESABINA AKTARILMAK
UZERE ILGILI HESABA INTI
KALI..
--------------------------------------------------------------------------------
20070297 24/07/2001 010 B 31,480,220 TL HASAN DUYMUS TAKIP HESABI
514201 A 6,325,409 TL NA YAPILAN TAHSILAT
380005 A 316,270 TL
390220 A 632,540 TL
118112 A 12,342,261 TL
27802 A 11,863,740 TL
--------------------------------------------------------------------------------
20071099 25/07/2001 392996 B 25,000,000 TL MUMTAZ OKUMUS UN TAKIPDEK
118112 A 25,000,000 TL I HESABINA DOMANIZ DEN GO
NDERMIS OLDUGU EFTOLUP TA
HSILI
--------------------------------------------------------------------------------
20071304 26/07/2001 010 B 80,967,269 TL SITKI EKMEKCININ IDARI TA
118112 A 69,103,529 TL KIP HES
27802 A 11,863,740 TL
--------------------------------------------------------------------------------
20071305 26/07/2001 118112 B 18,400,000 TL M SITKI EKMEKCI NIN BAKIY
380005 A 800,000 TL ESININ FAIZ HS LARINA ALI
390220 A 1,600,000 TL NMASI.
514200 A 16,000,000 TL
--------------------------------------------------------------------------------
20071417 26/07/2001 010 B 130,000,000 TL UCEYLUL LDT STI TAKIPTEKI
118112 A 130,000,000 TL HESABI OLUP YT
--------------------------------------------------------------------------------
20072346 30/07/2001 010 B 75,000,000 TL RISKI TAKIP HS DA IZELENE
118112 A 75,000,000 TL N YAKUP GUNSEL INTAKIPTEK
--------------------------------------------------------------------------------
20072814 30/07/2001 010 B 53,132,224 TL RECEP OZYILDIZ IN IDARI T
118112 A 50,602,119 TL AKIP HESABINA YAPILAN TAH
380005 A 2,530,105 TL SILAT
--------------------------------------------------------------------------------
20073410 31/07/2001 118112 B 53,132,224 TL RISKIMTAKIP HS DA IZLENEN
390220 A 4,620,194 TL RECEP OZYILDIZ IN BAKIYE
380005 A 2,310,097 TL SININ FAIZ HS LARINA ALIN
514200 A 46,201,933 TL MASI.
--------------------------------------------------------------------------------
20073896 02/08/2001 010 B 111,863,740 TL ALI RIZA OZDURGUT TAHSILA
27802 A 11,863,740 TL TI
118112 A 100,000,000 TL
--------------------------------------------------------------------------------
20074553 06/08/2001 010 B 17,127,809 TL TAKIP BORCLUSU MUMTAZ OKU
118112 A 17,127,809 TL MUS A ODENEN.
--------------------------------------------------------------------------------
20074809 07/08/2001 010 B 1,042,040,000 TL SB MIZIN TAKIPTEKI DOSYAL
172034 A 150,097,000 TL ARININ KESILEN KESINTILER
118235 A 363,200,000 TL I OLARAKICRDAN YAPILAN TA
176034 A 373,200,000 TL HSILAT./20074805 NOLU FIS
118112 A 155,543,000 TL LE KARSILIKLI
--------------------------------------------------------------------------------
20075145 08/08/2001 010 B 100,000,000 TL SB MIZ TAKIP BORCLUSU MEH
11821 A 50,000,000 TL MET KURTUL UN TAKIPTEKI H
118112 A 50,000,000 TL S LARINA YT.
--------------------------------------------------------------------------------
20075641 13/08/2001 010 B 22,294,191 TL AHMET KOSE NIN TAKIPTEKI
118112 A 22,294,191 TL BANKOMAT HS NA YATIRILAN.
--------------------------------------------------------------------------------
20075646 13/08/2001 118112 B 1,617,959,625 TL MEHMET SITKI AYKAN E AIT
290 A 1,617,959,625 TL 00158003845203576 NOLU B
ANKOMAT724/KREDI KART HES
ABINA YATIRILAN TAKIP HES
ABINA ATILAMAK UZERE SUBE
MIZCE YATIRILAN
--------------------------------------------------------------------------------
20075814 14/08/2001 118112 B 1,389,092,316 TL AHMET YILMAZ E AIT 00158
290 A 1,389,092,316 TL 003848315793 NOLU BANKOMA
T724/KREDI KART HESABINA
YATIRILAN TAKIP HESABINA
ATILMAK ZERE SUBEMIZCE Y
ATIRILAN
--------------------------------------------------------------------------------
20076349 16/08/2001 118112 B 138,679,209 TL 20056288/20056292/2005847
548 B 9,844,000 TL 5 NL FISLERLE SEHVEN MEHM
390220 B 984,591 TL ET OZKAYA DAN MUKERER TAH
380005 B 492,200 TL SILAT YAPILDIGINDAN IADES
010 A 150,000,000 TL I
--------------------------------------------------------------------------------
20077231 17/08/2001 010 B 1 TL HIKMET KARADUMAN TASFIYE
118112 A 1 TL OLUNAN ALACAGININ IZ BEDE
--------------------------------------------------------------------------------
20077256 17/08/2001 010 B 228,338,573 TL ICRA BORCLUMUZ ISMAIL GIR
118112 A 122,811,753 TL GIN TAHSILI
27802 A 26,776,820 TL
514200 A 75,000,000 TL
380005 A 3,750,000 TL
--------------------------------------------------------------------------------
20077634 20/08/2001 010 B 650,000,000 TL TAKIPTEKI MUSTERIMIZ SITK
118112 A 650,000,000 TL I AYKAN IN TAKIPTEKI BAN
--------------------------------------------------------------------------------
20077991 20/08/2001 010 B 400,000,000 TL MUSTAFA TASKIN IN TAKIPTE
118112 A 400,000,000 TL KI HS NA YT.
--------------------------------------------------------------------------------
20079069 22/08/2001 010 B 500,000,000 TL S-223 RESAT CETIN 2001/29
118112 A 57,959,579 TL 35 ICRA DSY YAPILAN TAHSI
11821 A 329,995,007 TL LAT
27802 A 76,363,740 TL
380005 A 1,699,127 TL
514201 A 33,982,547 TL
--------------------------------------------------------------------------------
20079668 23/08/2001 010 B 600,000,000 TL SUKRIYE AKYORUK UN TAKIPT
118112 A 600,000,000 TL EKI HS NA YATIRILAN.
--------------------------------------------------------------------------------
20081072 27/08/2001 118112 B 1,279,372,222 TL MUHAMMET EMIN OZCAN E AI
290 A 1,279,372,222 TL T 00158003845028956 NOLU
BANKOMAT724/KREDI KART HE
SABINA YATIRILAN IDARI TA
KIP HS NA ALINAN
--------------------------------------------------------------------------------
20081446 28/08/2001 290 B 15,939,999 TL 724 BANKOMAT724 SUBESI MU
118112 A 15,939,999 TL HAMMET EMIN OZCAN E AIT
00158003845028956 NOLU BA
NKOMAT724 HESABININ KAPAT
ILMASI BAKIYE=142153211 F
AIZ= 0 VERGI=0 SSDF=0 KR
BAK=0 KR FAIZ=109750619 K
KDF=10975062 BSMV=5487531
--------------------------------------------------------------------------------
20081447 28/08/2001 118112 B 10,000,000 TL MUHAMMET EMIN OZCAN IN B
010 A 10,000,000 TL ANKOMAT HS NA 20081207 N
OLU FISLE BANKOMAT HS NA
YT RILAN MIKTARIN 200814
46 NOLU FISTEN KAPAMASI.
--------------------------------------------------------------------------------
20081717 28/08/2001 010 B 228,925,887 TL CAHIT ERDOGAN IN TAKIPTEK
118112 A 228,925,887 TL I HS BINA ICRADAN TAHSILA
--------------------------------------------------------------------------------
20082257 29/08/2001 010 B 240,000,000 TL HAKAN KARAKOC UN IDARI TA
118112 A 240,000,000 TL KIP HESABINA YAPILAN TAHS
--------------------------------------------------------------------------------
20082327 29/08/2001 118112 B 73,623,139 TL HAKAN KARAKOC UN BAKIYESI
380005 A 3,505,864 TL NIN FAIZ HS LARINA ALINMA
548 A 70,117,275 TL SI.
--------------------------------------------------------------------------------
20082333 31/08/2001 118112 B 69,470,126 TL SB MIZ TAKIP BORCLUSU CAH
548 A 66,162,023 TL IT ERDOGAN IN BAKIYESINI
380005 A 3,308,103 TL N FAIZ HS LARINA ALINMASI
--------------------------------------------------------------------------------
20082523 31/08/2001 010 B 89,375,042 TL SABRI GULENC IN TAKIPTEKI
118112 A 1 TL BANKOMAT KREDISININ FAIZ
514200 A 77,717,427 TL I OLARAKTAHSIL EDILEN.
390220 A 7,771,743 TL
380005 A 3,885,871 TL

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,

Deja vou all over again.

I have several un-nice things to say. Most should (will?) remain un-said.

Much of the problem here is simply not really knowing (understanding?) the issues -before jumping in. The "records" are not fixed length. Actually, all EXCEPT the comments part are fixed length, so someone should loose their license (to program) over this alone. The file also includes these -weird?- sets of dashes which form another delimiter (Account?) these need to treated with some (distain?) exception processing. Some fields (Ammount?) are right justified, and in some instances it is not clear what the field width is and wheather some spaces are intended as leading pad spacing for one field or trailing pad spaces for another.

Why the process is using a plain text file in today's world is even more of a mystry.

My inclination is to just go away and hope the issue does not ever involve me, as I feel that I am to old to bear the pain. Still, as has been said, there is some good in everthing - even if it is only as a bad example. On that note, I offer the following rather ugly bit of code:

Option Compare Database
    Type FieldRecType
         Numb As String
         DateFld As String
         Acct As String
         XX As String
         Amount As String
         Units As String
         Cmnts As String
    End Type
    Dim FieldRec() As FieldRecType
Public Function basReadFixed(FileIn As String)

    Dim MyFil As Integer
    Dim NewFilName As Variant
    Dim MyNewFil As String
    Dim FilStr As String * 80
    Dim MyStr As String
    Dim MyFld As String
    Dim MyNumb As String
    Dim MyDate As String
    Dim FileStr As String
    Dim FilLen As Long
    Dim Idx As Long
    Dim Jdx As Long
    Dim FilRecs As Variant
    Dim FldStrt(6) As Integer
    Dim FldLen(6) As Integer
    'C:\MsAccess\Yesilkalem\Sample.txt

    'Start of Each field
    FldStrt(0) = 1
    FldStrt(1) = 9
    FldStrt(2) = 20
    FldStrt(3) = 27
    FldStrt(4) = 30
    FldStrt(5) = 49
    FldStrt(6) = 54

    'Length of Each field
    FldLen(0) = FldStrt(1) - FldStrt(0)
    FldLen(1) = FldStrt(2) - FldStrt(1)
    FldLen(2) = FldStrt(3) - FldStrt(2)
    FldLen(3) = FldStrt(4) - FldStrt(3)
    FldLen(4) = FldStrt(5) - FldStrt(4)
    FldLen(5) = FldStrt(6) - FldStrt(5)
    FldLen(6) = 79 - FldStrt(6)

    'Get the whole file into "Memory"
    MyFil = FreeFile
    Open FileIn For Binary As #MyFil
    'FilLen = LOF(MyFil)
    FileStr = String$(LOF(MyFil), 32)
    Get #MyFil, 1, FileStr
    MyStr = FileStr
    Close #MyFil

    'Parse into Individual Records / Lines
    FilRecs = basSplit(MyStr, vbCrLf)

    'Set Field UDT Dimension to # Records
    ReDim FieldRec(UBound(FilRecs))

    'Parse Records into Fields
    Idx = 0
    Jdx = 0
    Do While Idx <= UBound(FilRecs)
        If (Left(FilRecs(Idx), 10) <> &quot;----------&quot;) Then
            'It is a &quot;Real&quot; Record, Process it
            
            FieldRec(Jdx).Numb = Trim(Mid(FilRecs(Idx), FldStrt(0), FldLen(0)))
            FieldRec(Jdx).DateFld = Trim(Mid(FilRecs(Idx), FldStrt(1), FldLen(1)))
            FieldRec(Jdx).Acct = Trim(Mid(FilRecs(Idx), FldStrt(2), FldLen(2)))
            FieldRec(Jdx).XX = Trim(Mid(FilRecs(Idx), FldStrt(3), FldLen(3)))
            FieldRec(Jdx).Amount = Trim(Mid(FilRecs(Idx), FldStrt(4), FldLen(4)))
            FieldRec(Jdx).Units = Trim(Mid(FilRecs(Idx), FldStrt(5), FldLen(5)))
            If (Len(FilRecs(Idx)) > 55) Then
                FieldRec(Jdx).Cmnts = Trim(Mid(FilRecs(Idx), FldStrt(6), Len(FilRecs(Idx)) - 55))
             Else
                FieldRec(Jdx).Cmnts = &quot;&quot;
            End If

            Jdx = Jdx + 1
        End If

        Idx = Idx + 1
    Loop

    'Actually &quot;Fix&quot; the partial Records
    Idx = 0
    Do While Idx <= UBound(FieldRec)

        If (FieldRec(Idx).Numb <> &quot;&quot;) Then
            MyNumb = FieldRec(Idx).Numb
         Else
            FieldRec(Idx).Numb = MyNumb
        End If

        If (FieldRec(Idx).DateFld <> &quot;&quot;) Then
            MyDate = FieldRec(Idx).DateFld
         Else
            FieldRec(Idx).DateFld = MyDate
        End If

        Idx = Idx + 1

    Loop

    'Create the NEW File Name
    NewFilName = basSplit(FileIn, &quot;\&quot;)
    NewFilName(UBound(NewFilName)) = &quot;New&quot; & NewFilName(UBound(NewFilName))
    MyNewFil = basJoin(NewFilName, &quot;\&quot;)
    MyFil = FreeFile

    'Do the File Thing
    MyFil = FreeFile
    Open MyNewFil For Output As #MyFil
    'Convert the Whole thinggy to a BIG String
    'MyStr = basJoin(FieldRec, vbCrLf)

    Idx = 0
    MyStr = &quot;&quot;
    MyNumb = &quot;&quot;
    Do While Idx <= UBound(FieldRec)
        If (FieldRec(Idx).Numb <> MyNumb) Then
            'Change Record Number.  Demark Change
            Print #MyFil, String(80, &quot;-&quot;)       '& vbCrLf
        End If

        'Replace MyNumb w/ Current
        'If ((MyNumb = FieldRec(Idx).Numb And FieldRec(Idx).Numb <> &quot;&quot;)) Then
            MyNumb = FieldRec(Idx).Numb
        'End If
        'It is a &quot;Real&quot; Record, Process it

        MyStr = &quot;&quot;

        MySpace = String(FldLen(0), 32)
        MyFld = Left(FieldRec(Idx).Numb & MySpace, FldLen(0))
        MyStr = MyStr & MyFld

        MySpace = String(FldLen(1), 32)
        MyFld = Left(FieldRec(Idx).DateFld & MySpace, FldLen(1))
        MyStr = MyStr & MyFld

        MySpace = String(FldLen(2), 32)
        MyFld = Left(FieldRec(Idx).Acct & MySpace, FldLen(2))
        MyStr = MyStr & MyFld

        MySpace = String(FldLen(3), 32)
        MyFld = Left(FieldRec(Idx).XX & MySpace, FldLen(3))
        MyStr = MyStr & MyFld

        MySpace = String(FldLen(4) - 2, 32)
        MyFld = right(&quot;  &quot; & MySpace & FieldRec(Idx).Amount, FldLen(4))
        MyStr = MyStr & MyFld

        MySpace = String(FldLen(5) - 2, 32)
        MyFld = Left(&quot; &quot; & FieldRec(Idx).Units & MySpace, FldLen(5) - 2) & &quot; &quot;
        MyStr = MyStr & MyFld

        MySpace = String(FldLen(6), 32)
        MyFld = Left(FieldRec(Idx).Cmnts & MySpace, FldLen(6))
        Do While (right(MyFld, 1) = vbCr) Or (right(MyFld, 1) = vbLf)
            MyFld = Trim(Left(MyFld, Len(MyFld) - 1))
        Loop
        MyFld = Left(MyFld & MySpace, FldLen(6))
        'MyFld = FieldRec(Idx).Cmnts
        MyStr = MyStr & MyFld

        FilStr = MyStr

        Print #MyFil, FilStr

        Idx = Idx + 1
    Loop


'    Put #MyFil, 1, MyStr
    Close #MyFil

End Function
[code]

The following 'helper' functions are used within the above were developed independently, and MAY be of use to others as something OTHER than the &quot;Bad Example&quot;:

[code]
Public Function basSplit(strIn As String, _
                         Optional DelimChar As Variant = &quot; &quot;) _
                         As Variant

    'to return an array of the tokens (Words) in a delimited list of values
    'the delimiter may be set by the user.  The default value for the delimiter
    'is a single space.  The Delimiter may be set to any string (including null {as Chr(0)},

    'Michael Red, 9/25/00 for the Duvall Group, Columbia, MD
    'Usage & Example

    'Note [MyArray] is defined as a variant in the calling procedure.
    'MyArray = basSplit(&quot;Me, Myself, I, Thee, Thou, Though, Go, _
                         This is a also a test&quot;, &quot;,&quot;)

    'For xx = 0 To UBound(MyArray): Print xx, MyArray(xx): Next xx
    '0       Me
    '1       Myself
    '2       I
    '3       Thee
    '4       Thou
    '5       Though
    '6       Go
    '7       This is a also a test

    Dim Idx As Integer
    Dim LenDelim As Integer
    Dim Delim As Integer
    Dim PrevDelim As Integer
    Dim WdLen As Integer
    Dim WdsDone As Boolean
    
    Dim WdAray() As String

    'DelimChar = Left(DelimChar, 1)

    Idx = 0                 'Init WdAray Index
    LenDelim = Len(DelimChar)
    PrevDelim = 0           'Start w/ Previous position of Delimiter Before String
    ReDim WdAray(Idx)       'Initalize array of Words to single element

    While Not WdsDone
        Delim = InStr(PrevDelim + 1, strIn, DelimChar)
        If (Delim = 0) Then             'Can't find any more delimiters.
            'Must be done.
            'Just add the remainder of the Input to this element of WdAray
            WdAray(Idx) = right(strIn, Len(strIn) - (PrevDelim))
            WdsDone = True              'Tell'em were done here
         Else
            'Somewhere in the midst of all this, we have found a &quot;Real&quot; word
            WdLen = ((Delim - 1) - (PrevDelim - 1)) - 1
            WdAray(Idx) = Mid(strIn, PrevDelim + LenDelim, WdLen)
            Idx = Idx + 1                       'Add another element to the word array
            ReDim Preserve WdAray(Idx)
            PrevDelim = Delim + LenDelim - 1    'Push Previous Delimiter to Current Loc
        End If
    Wend

    basSplit = WdAray

End Function

Public Function basJoin(AryIn As Variant, _
                         Optional DelimChar As Variant = &quot; &quot;) _
                         As String

    'to return a String of the Set of elements in the input Array wit a Delimiter.
    'The delimiter may be set by the user.  The default value for the delimiter
    'is a single space.
    'The Delimiter may be set to any string (including null {as Chr(0)},

    'Michael Red, 9/11/01

    Dim Idx As Integer
    Dim tmpStr As String

    For Idx = 0 To UBound(AryIn) - 1
        tmpStr = tmpStr & CStr(AryIn(Idx) & DelimChar)
    Next Idx

    basJoin = tmpStr & CStr(AryIn(UBound(AryIn)))

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over