Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    first experience with delimited ';' records (2000)

    I want to import (via VBA code) in sheet_1 the records of this file from line:


    ;01005.8;00000000000;001;ENTE CREDITIZIO;1936-12-31;1936-12-31;0001-01-01; ;;0001-01-01;BANCA NAZIONALE DEL LAVORO;002;ISTIT. CRED. DIRITTO PUBBL.;000;;;00000;343000;VIA VITTORIO VENETO, 119;00187;343000;000000;1936-12-31;1960-12-30

    and when code find OPERAZIONI import the rest of records in sheet_2 (operazioni is thi init of new stok of records it to the bottom of txt file)

    In effect in the same file are 2 blobk of definition record:
    first block init with DATI FILE and second block init with OPERAZIONI

    note admit have the file in c:

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: first experience with delimited ';' records (2000)

    Does this do what you want? (Test on a copy of the workbook!)
    <code>
    Sub ImportDelimited()
    Const strFile = "C:Banche_1.txt"
    Dim f As Integer
    Dim strLine As String
    Dim wsh As Worksheet
    Dim r As Long
    Dim arr As Variant

    Application.ScreenUpdating = False

    f = FreeFile
    Open strFile For Input As #f

    ' Skip the first part
    Do
    Line Input #f, strLine
    Loop Until Left(strLine, 12) = "INTERMEDIARI"

    ' Fill the first sheet
    Set wsh = Worksheets(1)
    r = 0
    Do
    Line Input #f, strLine
    If Left(strLine, 10) = "OPERAZIONI" Then
    Exit Do
    End If
    r = r + 1
    arr = Split(Mid(strLine, 2), ";")
    wsh.Range("A" & r).Resize(1, UBound(arr) + 1) = arr
    Loop
    wsh.Range("A" & r).Resize(1, UBound(arr) + 1).EntireColumn.AutoFit

    ' Fill the second sheet
    Set wsh = Worksheets(2)
    r = 0
    Do While Not EOF(f)
    Line Input #f, strLine
    r = r + 1
    arr = Split(Mid(strLine, 2), ";")
    wsh.Range("A" & r).Resize(1, UBound(arr) + 1) = arr
    Loop
    wsh.Range("A" & r).Resize(1, UBound(arr) + 1).EntireColumn.AutoFit

    Application.ScreenUpdating = True
    End Sub</code>

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: first experience with delimited ';' records (2000)

    Hi Hans tks for code but when go on: Line Input #f, strLine, have error "62" see image...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: first experience with delimited ';' records (2000)

    Then your text file must have a different structure than the one you attached.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: first experience with delimited ';' records (2000)

    Repost extraction of this day...
    Is generated from Host at every day. Sorry me:-(

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: first experience with delimited ';' records (2000)

    This file has a different structure. You'd better find out how the export works, so that you can get a consistent format. It is impossible to write code if the format changes each day.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: first experience with delimited ';' records (2000)

    No Hans...
    In effect to redim dimension of file to upload in this forum i have deleted may line... and commit error...
    The last file attacged is the real file without modify.
    Sorry for to lost your time.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: first experience with delimited ';' records (2000)

    The file you attached today is not suitable for processing in Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •