Importing into Excel

Question

Hello.

I use this old database called AskSam. The company is no longer in business, so I can not get help from them. Basically, I want to import data from AskSam into Excel.

I am able to export AskSam data to a text deliminated file. However, Excel is not placing the information into tables correctly. For example, this is one of my "tab deliinated documents"

Any ideas?

Thanks

Answer 1

Try this macro - I think it requires a reference to MS Scripting Runtime... change the .txt to the actual file extension that you have. It will put the results in column C.

Option Explicit

Sub PullFromFile()

    Dim FSO As Object    'Scripting.FileSystemObject

    Dim ts As Object    'Scripting.TextStream

    Dim Contents As String

    Dim strFN As String

    Dim AllLines As Variant

    Dim i As Long, r As Long

    'Read in the text file

    Set FSO = CreateObject("Scripting.FileSystemObject")

    strFN = Application.GetOpenFilename("Text Delimited Files (*.txt),*.txt")

    Set ts = FSO.OpenTextFile(strFN)

    Contents = ts.ReadAll

    ts.Close

    'Split the lines into an array

    AllLines = Split(Contents, "!@#$%^*")

    'Visit each line

    For i = 0 To UBound(AllLines)

        'Write out the data to column C

        Cells(i + 2, "C").Value = Trim(AllLines(i))

    Next

End Sub

Answer 2

Im using every method I can thing of. But primarily copy and paste. When I try to use the Excel Import Wizard, it does not work.

At this point, I do not care if I get them into the correct fields. You see, with AskSam, I have about 1,500 documents. These documents are separate by a string in notepad, specifically, "!@#$%^*". Is there a way I can use this string and Excel to each force each document into a single cell? That way I will have 1,500 cells with data? (ie: One column and 1500 rows?)

Thanks!

Answer 3

Are the articles split into separate rows by carriage returns?  Which technique are you attempting to use?

Answer 4

Thank you. I am still running into some problems. I believe the carrier returns in my information is messing things up.

Answer 5

1. Write a macro that opens the file as a text stream, and read the file line by line. Then choose which column to place the data into based on the characters at the start of each line.

If you are not good with macros:

2. Open the file - with all the information in column A - and use formulas in other columns to extract the data into those columns.
3. Open the file - again, with all the information in column A - and sort column A and copy and move blocks of data into your columns manually.