I'm looking for a way to pull data from notepad and quickly insert it into excel. For example, each notepad document has 500 columns of 400 parameters. 200k pieces of information in all. But there are words that separate each column as they are vertical. Suggestions? I really do not want to copy and paste.

Here is what the text file looks like.

words words words

number
number
number
number
number

words words words

number
number
number
number

etc.

Thanks

6/6/2008 10:42:46 AM

search and replace \n -> \t then \t\t -> \n
or something similar
send it to me, i'll do it if you aren't seeing what i'm saying

6/6/2008 10:46:41 AM

are the columns in notepad fixed width or separated by tabs?
If so, in excel, go to Open and select "All File Types" and open the text file, then it will take you to a text-file import wizard. you can select "Delimited" if all the columns are searated by tabs, or "Fixed Width" if the columns are the same size in Notepad.

6/6/2008 11:39:46 AM

^ ah nice. yeah I got it in. ha unfortunately it is in one long 20000 row column. Next step is to get this 1 by 20000 column into a 400 by 500 array.

6/6/2008 12:45:49 PM

This looks like a job for PERL!

6/6/2008 12:49:03 PM

yeah, it is a good job for perl if you are doing this often and need to do it in an automated fashion. but if it's a once or twice type of thing, a regex search/replace is a lot quicker than making a script (unless you're very comfortable with perl and could do it really quick)

6/6/2008 12:52:17 PM

^ I love nedit for just that purpose.

6/6/2008 2:25:51 PM

For god's sake just use vba.

Sub IJustFuckedYourMomWithAWroughtIronRod()

' YOUR FILE HERE
file = "c:\myfile.txt"


ff=FreeFile
col=0
row = 0
open file for input as #ff
While not Eof(FF)
  Line Input #FF, l
  If Isnumeric(l) Then
    row=row+1
    ActiveSheet.Cells(col,row).Text = l
  ElseIf Trim(l)=vbNullString
  Else
    col=col+1
    row=1
    ActiveSheet.Cells(col,row).Text = l
  End If
Wend
Close #FF
End Sub

6/7/2008 12:53:40 AM

i can name a few things wrong with that post

6/7/2008 1:32:01 AM

you could do it in a couple lines of code with a SAS data step.

6/7/2008 2:09:38 PM

yeah, we've already established that it can be done in code. darkone likes perl, limpy likes vba, you like SAS. i did it in php. anybody want to offer up cobol or python?

6/7/2008 2:30:49 PM

^^^ what's wrong with that post? unless i put the indices in Cell() backwards or .Text is not a member of the Excel.Range class (in which case you'd use .formula -- I've been using Word a lot lately which has an entirely different range class). I don't have excel here to test with, but that assumes "words words words" is a column header and "number" is an element of the column. the original post wasn't very clear on that. multiple lines of text consecutively would result in an empty column which you can get rid of by copying the data range and paste special -> skip blanks.

i could do it in perl too, but VBA is built right into Excel. fuck a SAS.

6/7/2008 3:22:04 PM

qntmfred did it perfectly. I'd be curious to see something more simple than it.

6/7/2008 5:49:49 PM

PISSING MATCHES!!


I DID IT WITH PUNCHCARDS

6/7/2008 6:37:01 PM

shit i did it on my cell phone!!1!

6/7/2008 6:37:46 PM

simpsons did it

6/7/2008 8:02:18 PM

I hired an Indian to do it.

6/8/2008 10:07:45 AM