Relavent data from text file

[Npsays]

Hi
I need help on importing data into excel from a text file. But, before the import i need to ignore the blank lines. is it possible ? It is a huge file which has lot o f blank spaces in between which I don't need before importing
into excel .

Thanks..Np

 

[venkat1926]

better to sort after import the blank rows will go down.

 

[Npsays]

better to sort after import the blank rows will go down.

Thanks .. but I need it before the upload as the file size is huge and I need only the required data. Is it possible to sort the text file in ascending order before it uploads the data on excel ?

 

[erik.van.geit]

Hi,

try this

Option Explicit

Sub import_textfile_without_blanks()
'Erik Van Geit
'060912
'requirement: exclude blanks before import

    Dim FilePath As String
    Dim FileNumber As Integer
    Dim I As Long
    Dim Data As String
    Dim CalcSetting As Integer

FilePath = "C:\windows\desktop\temp.txt"
FileNumber = FreeFile

Open FilePath For Input As #FileNumber
I = 0

    With Application
    .ScreenUpdating = False
    CalcSetting = .Calculation
    .Calculation = xlCalculationManual
    End With

With Sheets(1)
    If FileLen(FilePath) > 0 Then
        While Not EOF(FileNumber)
            Line Input #FileNumber, Data
            If Data <> "" Then
            I = I + 1
            Cells(I, 1) = Data
            End If
        Wend
        Close FileNumber
    Else
      MsgBox "No Data found in the textfile", 48, "TITLE"
      Exit Sub
    End If
    
End With
    
    With Application
    .ScreenUpdating = False
    .Calculation = CalcSetting
    End With

End Sub

storing everything into an array before import, will be faster, but you would be limited to about 1800 characters per line
kind regards,
Erik

 

[Npsays]

Thanks Eric ! Just Perfect!
last , one small addition ...... there are some special characters which I need to ignore . They appear in the begining of the string.. any ways around that too ? .. great help Thanks !

 

[erik.van.geit]

always same amount of characters ?
do you know which characters ?

check it out: report the numbers you get
after I = I + 1 insert this

Dim ii

Stop
'continue with function key F8 till you know enough
'then press stop-button
For ii = 1 To Len(Data)
MsgBox Asc(Mid(Data, i, 1))
Next ii

best regards,
Erik