8 replies to this topic

[u2cannv]

Here's my problem, I have a .txt file that contains data that needs to be seperated by tabs and cant figure out how to write a code that will seperate the data for me. So that when I pull it into excel it's not read as one big cell of information, instead its seperated by tabs. Any help or direction to where i could go would be helpful. I'm programming in BASIC and the compiler is BASIC IDE. thanks in advance

[Lop]

I'm a little confused so let me see if I understand.

You have a txt document but do or don't want it to be tab delimited?

You want to open it and it be in one big cell or not?

If you want it to be in separate cells you need to delimited by commas and name the file .csv.

[WingedPanther]

Do you know something more precise for where to insert the tabs?

[u2cannv]

Thanks for your help so far, I have txt file that needs to be tab delimited. I would like to do a find a replace with tabs. the character that is being replaced is (|) this in not an "L" by the way. so i'm not sure if how to write the code for that.
Thanks

[kkelly]

I'm not sure if this is the type of solution you are looking for, but when importing external data into excel you can specify the delimiting character in the Import Text Wizard. Just uncheck the "Tab" box and check "Other", then specify the | character in the following text box.

[u2cannv]

thank you for your reply, but I know how to do that. I'm trying to automate the process so I wont have to do that (LOL). I know that it's probably something simple, but can't put my hands around it. When i pull the file into excel its not recognizing the tabs, it just reads it as spaces and continues putting all the information into that one block of cell. It's really frustrating, so I'm not sure what i need to do?????? any suggestions or website are helpful.

Thanks

[u2cannv]

yes the tabs needs to be replaced inbetween "|" this is the bar character. Thanks

[WingedPanther]

I would just read the source file one character at a time, replacing the | with [tab] when encountered, then write the character to a new file. You just need to do a little experimenting to figure out the ASCII code for | and [tab].

[ghostdog74]

assumed input data like this:
123|456|789
one|two|three

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

objSheet.Cells(1, 1).Value = "First Column header"

objSheet.Cells(1, 2).Value = "Second column header"

objSheet.Cells(1, 2).Value = "third column header"

r = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

myFile = "c:\temp\a.txt"

Set objFile = objFSO.OpenTextFile(myFile,1)

Do Until objFile.AtEndOfStream

	line = Split(Replace(objFile.ReadLine,"|",vbTab),vbTab )

	WScript.Echo line(1)

	objSheet.Cells(r, 1).Value = line(0)

	objSheet.Cells(r, 2).Value = line(1)

	objSheet.Cells(r, 3).Value = line(2)

	r=r +1

Loop

objExcel.ActiveWorkbook.SaveAs "c:\test.xls"

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

when done, an excel file will be created