Import Text file Data into Excel Sheet

[vicky]

I have a text file which has data(sample data below), i
need to import data directly into the excel sheet

I need to import from the text file phase by phase,i.e if the (text
file) data exceeds 65536 rows then it has to put it in next sheet.

the data is in this format wherin comma is a delimiter

7,.,20,de05c,,,52,1.2,52
7,.,21,de05c,,,2,1,2

 

[vicky]

hey guys i am done with the logic.....


Sub ReadStrings()
Dim sLine As String
Dim sFName As String 'Path and name of text file
Dim iFNumber As Integer 'File number
Dim lRow As Long 'Row number in worksheet
Dim lColumn As Long 'Column number in worksheet
Dim vValues As Variant 'Hold split values
Dim iCount As Integer 'Counter
sFName = "C:\Documents and Settings\vbarlotx\Desktop\Bharath
\AllExcel.csv"

'Get an unused file number
iFNumber = FreeFile
'Prepare file for reading
Open sFName For Input As #iFNumber
Sheet1.Cells.Clear
'First row for data
lRow = 1
Do
'Read data from file
Line Input #iFNumber, sLine
'Split values apart into array
vValues = Split(sLine, ",")
With Sheet2
'First column for data
lColumn = 1
'Process each value in array
For iCount = LBound(vValues) To UBound(vValues)
'Write value to worksheet
..Cells(lRow, lColumn) = vValues(iCount)
'Increase column count
lColumn = lColumn + 1
Next iCount
End With
'Address next row of worksheet
lRow = lRow + 1
'Loop until end of file
Loop Until EOF(iFNumber)
'Close the file
Close #iFNumber
End Sub

 

[vicky]

I just need a logic for Populating Data in a next sheet when the
Lrow>65536

 

[keiji kounoike]

Try this one.

Sub LargeDataImport()
Dim flname
Dim filename
Dim FileNum As Integer
Dim Counter As Long, maxrow As Long
Dim WorkResult As String
Dim ws As Worksheet
Dim i As Long

On Error GoTo ErrorCheck
maxrow = Cells.Rows.Count
MsgBox "Select Data File"
filename = Application.GetOpenFilename(FileFilter:= _
"Text file (*.prn;*.txt;*.csv;*.dat),*.prn;*.txt;*.csv;*.dat" _
, MultiSelect:=True)
If VarType(filename) = vbBoolean Then
Exit Sub
End If

Application.ScreenUpdating = False
Application.EnableEvents = False
Set ws = ActiveWorkbook.ActiveSheet

Counter = Cells(Cells.Rows.Count, "A").End(xlUp).Row

If Counter <> 1 Then
Counter = Counter + 1
End If

For Each flname In filename
FileNum = FreeFile()
Open flname For Input As #FileNum
Do While Not EOF(FileNum)
If Counter > maxrow Then
Set ws = Nothing
Set ws = ActiveWorkbook. _
Worksheets.Add(after:=ActiveSheet)
Counter = 1
End If
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & flname
Line Input #FileNum, WorkResult
Cells(Counter, "A") = WorkResult
Application.DisplayAlerts = False
Cells(Counter, "A").TextToColumns Destination:= _
Cells(Counter, "A"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, _
Other:=False
Counter = Counter + 1
Loop
Close #FileNum
Next

Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrorCheck:
Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "An error occured in the code."
End Sub

Keiji