Solved

Acc97-IMPORT SSN from Excel or TXT

Posted on 1998-10-02
3
395 Views
Last Modified: 2008-02-07
I have an Excel-97 file and a TXT file I need to import into Access and merge on SSN. Cell is formatted as text in both files.  Access says it has a conversion error and does not load any records where SSN starts with a 0(zero)!!! There must be a way to do this. I can't believe a million other people don't need to do this! Yet the ZIP code formatted exactly the same way comes in great. The only difference I can see is 9-digits vs. 5-digits.????
0
Comment
Question by:ronallard
3 Comments
 

Expert Comment

by:JCreson
ID: 1964348
You may have to create an Import spec for your text file.  When you set up your import, look at the Advanced button in the lower left corner of the Import Wizard.  With that, you can set up a spec to get the data into the types you need.
Hope that helps...
0
 
LVL 3

Accepted Solution

by:
kaldrich earned 50 total points
ID: 1964349
Ah, yes, this brings back fond memories.  What you need to do is concatenate information in the Excel file, copy and paste it specially, and delete the original row.  To do this:

1. Copy your file.

2. In the copied file, create a row next to your SS# row.

3. Set the first value in it as = '"' & (Cell your SS# is in) & '"'.  This should create a field that looks like this: "999999999".  Copy the formula down to the bottom.

4. Highlight the column and copy it.

5. Go into Edit, Paste Special, and select "Values".

6. Delete your original column of SS#s.

7. Do your import.

Hope this helps.  Thanks.

Kevin
0
 

Author Comment

by:ronallard
ID: 1964350
Thanks - works great!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now