Organize large space delimited text file into appropriate rows and columns in excel.

**rdlaner** · 08-28-2013, 05:07 PM

I have collected data into a large text file such that each value is space delimited. I would like to import this text file into Excel in order to analyze it better. I have pasted a portion of the text file into Excel in order to attach it to this ticket. Whether I copy and paste the data or import it, I don't care, just as long as I can organize it in Excel.

I need the data to be organized into 5 different columns with the first value going into A1, the second value into B1, and continuing until the fifth value is in E1. Once there, it needs to repeat so that the sixth value is placed in A2, the seventh in B2 until the the tenth value is in E2.

For example, if I had this series of data:
0053 02A3 0000 0000 0000 0054 02A3 0000 0000 0000 0055 02A3 0000 0000 0000

It would need to be organized like this, with each value in it's respective row and column:
0053 02A3 0000 0000 0000
0054 02A3 0000 0000 0000
0055 02A3 0000 0000 0000

I hope that makes sense, let me know if you need any clarification.
Thanks!

**JBeaucaire** · 08-28-2013, 11:30 PM

Your explanation of the need is fine. If you provide a sample of your TEXT file, that would be better.

**JBeaucaire** · 08-28-2013, 11:43 PM

This seems to be pretty fast with the file created from the text in your sample file.

Option Explicit

Sub ImportTextFileToFiveColumns()
Dim fNAME As String, MyStr As String, MyARR As Variant, i As Long

'Choose a text file
    fNAME = Application.GetOpenFilename("Text Files (.txt),.txt")
    If fNAME = "False" Then Exit Sub

'Read the file into memory and split into an array
    Open fNAME For Input As #1
    Input #1, MyStr
    MyARR = Split(MyStr, " ")
    Close #1
    
'on a new sheet, parse sets of 5 values out to individual rows
    Sheets.Add
    Application.ScreenUpdating = False
    For i = 0 To UBound(MyARR) Step 5
        Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 5).Value = Split(Join(Array(MyARR(i), MyARR(i + 1), MyARR(i + 2), MyARR(i + 3), MyARR(i + 4)), " "), " ")
    Next i
    Application.ScreenUpdating = True

End Sub

**rdlaner** · 08-29-2013, 01:15 PM

Thanks for the help!
I am running into a few errors while trying to implement the code you gave me. Forgive me since I do not have much experience using macros.
When I'm in the macro editor and I try to run the script you gave me, I get this error:
"Run-time error '1004'
Application-defined or object-defined error"

I also tried assigning this macro to a button to see if I would get the same error. The error I received when I tried to press the buttons was:
"Microsoft Visual Basic
400"

I've looked up these errors online and am trying to make sense of them, if you have any input I would certainly appreciate it. I've also attached a portion of the text file for testing.

Thanks!

**rdlaner** · 08-29-2013, 12:43 PM

I've attached the .txt file, although I had to reduce its size so that the uploader would accept it.

**JBeaucaire** · 08-29-2013, 01:20 PM

Macro seemed to work without error on your sample text file, too.

Here's the macro installed into a basic workbook. Compare this to what you did to see what is different.

**rdlaner** · 08-29-2013, 01:25 PM

Thanks.
The workbook you gave me fails (with the same error) at the same point where mine does, when this line is called:
fNAME = Application.GetOpenFilename("Text Files (.txt),.txt")

I am using Excel 2011, if that helps at all.

**JBeaucaire** · 08-29-2013, 01:30 PM

Yes, MAC OS is very different from Windows.

Originally Posted by rdlaner

Thanks.
The workbook you gave me fails (with the same error) at the same point where mine does, when this line is called:
fNAME = Application.GetOpenFilename("Text Files (.txt),.txt")

I am using Excel 2011, if that helps at all.

Some Googling suggests something like this might work:

fNAME = MacScript("return (path to documents folder) as String")

I have no way to test that or debug it for you. That "path to documents folder" may be an editable string.

**JBeaucaire** · 08-29-2013, 01:26 PM

Is that a MAC version? I'm not that well-versed in MAC requirements.

**rdlaner** · 08-29-2013, 01:27 PM

It is a MAC version, should I expect the functions to be that different...?

**rdlaner** · 08-29-2013, 01:44 PM

Thanks, I'll try out a few things and let you know what I find.

**rdlaner** · 08-29-2013, 01:56 PM

I ended up just removing the arguments for the GetOpenFilename function and everything appears to be working now.

Thanks for your patience and help!

**JBeaucaire** · 08-29-2013, 02:25 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Organize large space delimited text file into appropriate rows and columns in excel.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Re: Organize large space delimited text file into appropriate rows and columns in excel.

Thread Information

Users Browsing this Thread

Similar Threads

Space delimited text to multiple columns

Import space delimited text file into excel

Opening a space delimited text file in Excel

How to save excel data into text file with space delimited

[SOLVED] Excel 2003 Importing large delimited text file

Bookmarks

Bookmarks

Posting Permissions