Text file import


Board Regular
Aug 29, 2002
I work in television and one of the pieces of equipment I'm responsibile for is called a 'stillstore', which I'll refer to as SS.

The SS has a database capable of storing 10,000 images, each with a unique number (from .0000 to .9999) and a description for each. The only method the SS has for exporting database info is for a Generic/Text printer to be setup on the PC, then print to this, specifying the filename, such as "stills.txt".

So far, so good......

When I import this into Excel, the result is all in Column A:
A1   .0000
A2   Description of .0000
A3   .0001
A4   Description of .0001
A5   .0002
A6   Description of .0002
....and so on for thousands of rows.

Here's what I want to do:
I want to massage everything in this single column, so the info ends up in two columns:
          COLUMN A        COLUMN B
A1        .0000             Description of .0000
A2        .0001             Description of .0001
A3        .0002             Description of .0002
.....and so on.

What I'm trying to do is break the data into two columns:
- in the original import, cells A1, A3, A5...etc are all Column A and renumbered to A1, A2, A3...etc.
- then cells A2, A4, A6...etc become Column B and are renumbed B1, B2, B3...etc.


I've tried massaging this manually but I go blind after a very short time ;)

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.


Well-known Member
Apr 8, 2005
Sounds the delimiter for the file is the carriage return? Have you tried using the Data -> Get External Data -> Import Text File? I don't think that will do it, but it's worth a try if you haven't.

I cut my teeth on BASIC and GW BASIC back in the 80's and 90's, so I usially end up approaching this stuff with a hammer and chisel, so to speak, rather than pre-packaged toolboxes... so I'll give you an approach, but I wouldn't be suprised if someone recommends something more efficient:

Sub Parse_File()

    Dim FileToOpen
    Dim tmp As String
    ChDrive (Left(ActiveWorkbook.Path, 1))

    ChDir ActiveWorkbook.Path

    FileToOpen = Application.GetOpenFilename(Title:="Please choose a file to import", FileFilter:="Text Files *.txt (*.txt),")

    If FileToOpen = False Then

        MsgBox "No file specified.", vbExclamation, "Duh!!!"

        Exit Sub

    End If
    Open FileToOpen For Input As 1
    cnt_row = 1
    'Application.Calculation = xlCalculationManual
    'Application.ScreenUpdating = False
    Do Until EOF(1)
        Line Input #1, tmp
        Range("a" & cnt_row).Value = tmp
        Line Input #1, tmp
        Range("b" & cnt_row).Value = tmp
        cnt_row = cnt_row + 1
    Close #1

    'Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True
End Sub

I would step through this one line ata time and watch the worksheet in the background and make sure that things are going where you expect... once it's debugged, un-comment the screenupdating and calculation lines to speed the process up.

THIS CODE IS UNTESTED... in it's urrent fomr... I snagged it from a working macro and lobotomized it for your needs... so it should be pretty clean.
Upvote 0

Andrew Poulsom

MrExcel MVP
Jul 21, 2002
Try this:

Sub Test()
    Dim FileName As String
    Dim FileNum As Integer
    Dim r As Long
    Dim Counter As Long
    Dim Data As String
'   *** Change path and file name to suit ***
    FileName = "C:\Temp\stills.txt"
    FileNum = FreeFile
    r = 1
    Counter = 1
    Open FileName For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, Data
        If Counter Mod 2 = 1 Then
            ActiveSheet.Cells(r, 1) = Data
            ActiveSheet.Cells(r, 2) = Data
            r = r + 1
        End If
        Counter = Counter + 1
    Close #FileNum
End Sub

Similar to Hatman's but tested.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back