12-01-2008 05:13 PM
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:
4633 ELM DR
BIRCH RUN, MI 48415
Any software and mapping recommendations or even specific instructions that would help me have success would be so greatly appreciated.
12-02-2008 02:08 AM
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 | email@example.com |
|JOHN SMITH |46 OAK DR |PINE RUN |MI 48413|989-777-8888 | firstname.lastname@example.org |
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.
12-02-2008 05:04 AM
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!
12-02-2008 06:27 AM
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.
12-02-2008 06:32 AM
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.....
12-02-2008 06:46 AM
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.
12-02-2008 07:39 AM
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.
'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
Sheet2.Range(inputCOL & CStr(inputROW)).Value = Sheet1.Range("A" & CStr(readingROW)).Value
'Set next column to write to
Select Case inputCOL
inputCOL = "B"
inputCOL = "C"
inputCOL = "D"
inputCOL = "E"
'Set next row to read from
readingROW = readingROW + 1
'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
I hope code looks good in forum
12-02-2008 07:42 AM - edited 12-02-2008 07:45 AM
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 can maybe fix that later to.
12-02-2008 07:48 AM
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.