PDA

View Full Version : Import and split large tab deliminated text files



knifelong
04-02-2008, 06:31 AM
Hi

I saw this code which works well to split text files with over 65000 records into new worksheets when you import them into Excel 2003. However it will only import them into the first column. I have been trying to figure out a way to adapt the code to import into as many separate fields as you need if the text is tab deliminated. Ideally for a variety of text formats, ie. tab, space or comma deliminated, but mainly tab deliminated. However, as I am quite new to VBA code I have not succeeded. Thanks!

The code is below:


Sub LargeFileImport()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = Application.GetOpenFilename
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet
'Set The Counter to 1
Counter = 1
' Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & Counter & " of text file " _
& FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub



Credit for posting code goes to "ROYUK" who has been banned from OZgrid. (they have more rules than posters)

mdmackillop
04-02-2008, 10:11 AM
Hi Knifelong,
Welcome to VBAX
Record a macro using Data/Get External Data/Import Text File. Let us know if you need more assistance.
BTW to format your code (good attempt!) Select it and click the VBA button.
You can post files using Manage Attachments in the Go Advanced section

Charlize
04-02-2008, 01:46 PM
A little modified coding. Delimiter is a ; . Change it to suit your needs. Maybe create a function for it.
Sub LargeFileImport()
'added a loop variable
Dim vloop As Long
'**********************
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = Application.GetOpenFilename
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet
'Set The Counter to 1
Counter = 1
' Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & Counter & " of text file " _
& FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell

'*** added some coding ***************************
'store everything in an array using split function
'i've used ; as the delimiter
'*************************************************
For vloop = LBound(Split(ResultStr, ";")) To UBound(Split(ResultStr, ";"))
If Left(Split(ResultStr, ";")(vloop), 1) = "=" Then
ActiveCell.Offset(, vloop).Value = "'" & _
Split(ResultStr, ";")(vloop)
Else
ActiveCell.Offset(, vloop).Value = _
Split(ResultStr, ";")(vloop)
End If
Next vloop

'*** end of added coding **************************

If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub
Charlize

knifelong
04-04-2008, 06:36 AM
Thanks Charlize and mdmackillop for your time. After a bit of fiddling I found out that vbTab is the delimiter I needed to change to.

I have just one other question which is about how to leave the data in txt format when it is imported into excel 2003 rather than it changing automatically to general or number format as this is messing up numbers which have .00 decimal places and they need to remain exactly the same.
I think its something to do with changing a 1 for 2 somewhere in the code but I can't figure out where but I may find it by doing some more searching.

Cheers