Community
Showing results for 
Search instead for 
Do you mean 
Reply

Trying to import records in a .TXT file into ACT

New Member
Posts: 16
Country: United States

Trying to import records in a .TXT file into ACT

I am trying to import a ton of records into ACT v.10 The records are currently in this form spaced by a line or two between each one in plain ole .txt files. I have made many attempts and realize they will have to be either in a .csv format or delimited  and when I do get that far I have a major challenge in mapping the fields as it sees each line as one record. I found some software called Import Wizard and they want $149 for a single user lisence but I want to be sure before I do it.Perhaps there is far better or more user friendly software  available. I have about 3,000 records and this would save me copious hours of tedious data entry.

 

The reocrds look like this currently within the .txt file spaced by a line or two between each record currently:

 

PETER PIPER
4633 ELM DR
BIRCH RUN, MI  48415
989-777-9999
cff1000@charter.com

 

 

Any software and mapping recommendations or even specific instructions that would help me have success would be so greatly appreciated.

Silver Elite Contributor
Posts: 3,311
Country: United_Kingdom

Re: Trying to import records in a .TXT file into ACT

I think you need to do quite a bit of work tidying up your data before it will import. Personally I would start off by importing the whole lot into Excel. Then you need to arrange it so you have each contact and address on a separate row, with each address element in a different column, with column headers. You might be able to use the Text to Columns function to do this if you are lucky.

 

You are looking for something like this:

 

 

|Contact       |Address 1    |Address 2 |Zip Code  |Phone           |E-mail                       | 

|PETER PIPER |4633 ELM DR|BIRCH RUN|MI  48415|989-777-9999 | cff1000@charter.com |

|JOHN SMITH |46 OAK DR    |PINE RUN |MI  48413|989-777-8888 | cff9000@msn.com      |

 

 You need a continuous list with no spaces (i.e. no blank rows).

 

At this point you would be in a position to try and import the data after saving as a tab delimited txt file. (Always do an ACT! backup before an import!!!).

 

If the import still doesn't work you may have to clean up the data in the Excel sheet.

 

I don't believe that Import IT would help unless you get to this stage in tthe first place. 

 

Jeff

 

 

 

 

Jeff Granger
UK ACT! Specialist and Trainer

ACT Training Throughout The UK
www. bigbluemarketing.co.uk
New Member
Posts: 16
Country: United States

Re: Trying to import records in a .TXT file into ACT

TY Jeff...I had a feeling that would be the reply. I am wondering how I can go into all 40 odd files that I have and do the clean-up in one fell swoop. Do you know of the ideal software? A good freeon perhaps that would be the viable solution?

 

Thanks again for chiming in!

Copper Contributor
Posts: 30
Country: Sweden

Re: Trying to import records in a .TXT file into ACT

If you can copy the textfile into excel, then you can use VBA (Excel macro) to do the work. I't not that hard to do.

 

Data - Text to columns are sometime good if you have problem geting text into excel corectly.

 

 

Also you can work with any scripting language directly on the textfiles, but thats harder to do.

New Member
Posts: 16
Country: United States

Re: Trying to import records in a .TXT file into ACT

Asedt,

 

I really appreciate the reply.

 

I'm a techno dummy so could you please elaborate a bit on the VBA Excel macro. Is this a function within excel or a software interface that works with it? If the latter-do I need to buy it? Also I wasn't clear on this part:

 

" Data - Text to columns...."

 

O.k...I am with you now and I did try that and it said my columns were too wide.

 

Thank you once again.I'm really under a gun trying to find this solution or it's time to lock myself up for 200 hours and start tapping away.....

Copper Contributor
Posts: 30
Country: Sweden

Re: Trying to import records in a .TXT file into ACT

If all of your records are on five lines the you can do like this:

 

 

1. Copy text to excel if you havent done this yet. Open in Notpad, Ctrl+A to select all, Ctrl+C to copy. Switch to excel and press corner abow 1 left to A to select the hole sheet then Ctrl+V to past the text to the excel sheet.

 

You get a error/warning message, just klick ok.

 

Now you have Ewerything in column A and the rows the same way as in the textfiles.

 

 

2. VBA is Visual basic for Applications and is teh scripting/macro language in excel. You open the editor with Alt + F11 (Tools - macro -Visual basic editor)

 

You can record macros and run macros (VBA code) from the Tools - macro menue.

 

I find it best to write code in a module, make a new one with insert module when you are in the VBA editor.

 

 

If all of your records are on five lines like your example i can make a macro for you putting them on one line each.

New Member
Posts: 16
Country: United States

Re: Trying to import records in a .TXT file into ACT

wow!....I'll prolly have to read this several times but I really appreciate your help! dID i READ THAT WRITE ABOUT 'YOU" POSSIBLY WRITING ME A MACRO.
Copper Contributor
Posts: 30
Country: Sweden

Re: Trying to import records in a .TXT file into ACT

Yep, you hered me right. It's quite funny writing macros. I'm not that pro at writing macros so it's maybe not that fast but only for 3000 records it's no problems, i think it goes under a second or two.

 

Paste this into the module, then run the macro from Tools - Macro - Macros... The name of the macro is PutRecordsOnLines and shold be the only one to chose in the list.

 

 

Sub PutRecordsOnLines()
   
    'The row in sheet1 that we read from.
    Dim readingROW As Double
       
    'The row and column in sheet2 that we add rows to
    Dim inputROW As Double
    Dim inputCOL As String
   
    inputROW = 2
    readingROW = 1
    inputCOL = "A"
   
    'Reads lines in sheet1 until "END" is writen in the A column
    'Write END manualy at end of sheet
    While Not Sheet1.Range("A" & CStr(readingROW)).Value = "END"
        'Reads lines until emty line is find and write lines in sheet2
        While Len(Sheet1.Range("A" & CStr(readingROW)).Value) > 0
           
            'The write
            Sheet2.Range(inputCOL & CStr(inputROW)).Value = Sheet1.Range("A" & CStr(readingROW)).Value
       
            'Set next column to write to
            Select Case inputCOL
                Case "A"
                    inputCOL = "B"
                Case "B"
                    inputCOL = "C"
                Case "C"
                    inputCOL = "D"
                Case "D"
                    inputCOL = "E"
            End Select
           
            'Set next row to read from
            readingROW = readingROW + 1
        Wend
       
        'Reset column to write to
        inputCOL = "A"
   
        'Set next row to read from (we do noting on emty rows of any number)
        readingROW = readingROW + 1
   
        'Set next row to write to if we are on a row alredy writen to
        If Len(Sheet2.Range("A" & CStr(inputROW)).Value) > 0 Then
                inputROW = inputROW + 1
        End If
    Wend
End Sub

 

I hope code looks good in forum Smiley Happy

Copper Contributor
Posts: 30
Country: Sweden

Re: Trying to import records in a .TXT file into ACT

[ Edited ]

O forgott to say, as you can se in teh macro you have to write END at the last row on the sheet with the records.

 

The result will be on sheet2, and you maybe have to copy to it's own file it before you can convert it to any more useful formats.

 

 

I now also see that i missed the splitup of the third row Smiley Sad can maybe fix that later to.

Message Edited by asedt on 12-02-2008 07:45 AM
New Member
Posts: 16
Country: United States

Re: Trying to import records in a .TXT file into ACT

asedt,

 

Please bear with me.I'm computer savvy..marketing savvy but way out of my league with languages and programming. The only macro I ever executed in my life was with Lotus 123 back in 1984.  Do I still follow your instructions in the frame with the time stamp 6:46 and if I have the data in approx. 30-40 files-will I have to re-insert this marcro each time or just ONCE  and then cut 'n paste the data each time.

 

I have no clue what I am doing or where exactly do I start and end the cut 'n paste let alone where to install it.I need exactitudes before I start or I will screw this up. I really appreciate the time you have taken to assist me. I don't want ask for too much but this stuff is 2nd nature to you but foreign to me.