Re: Import number data with leading zeros as text without losing l
- From: "Jeff Boyce" <nonsense@xxxxxxxxxxxx>
- Date: Thu, 15 Feb 2007 15:55:43 -0800
If you have multiple (70) tables "all identically set up", each table for a
different client, you have a spreadsheet, not a relational database. If you
want to get good use of Access' features and functions, you can't feed it
'sheet data.
Before you even get into how to import and the problem with leading zeros,
consider whether you actually need to be using a spreadsheet instead. Is
there something that you want Access to do with your data that isn't easily
done in Excel?
If you are convinced that Access is the tool you need, normalize your data
before going any further. For instance, you could have ONE table, something
like:
tblMonthlyClientData
MonthlyClientDataID (a Primary Key)
ClientID (a foreign key, identifying which client from a
tblClient)
DateOfImport
Amount
Notice that the SSN and the internal 401K identifier are NOT in this
table -- these are facts about the client, and belong in the client table,
not duplicated each month. Also note that there's no need to delete and
re-fill each month. You can simply append new records.
If you'll provide more detailed description of the kind of data the 70
tables hold, I'm sure that newsgroup readers will offer alternate
suggestions for a table structure that will make your work much easier.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"rnvhill" <rnvhill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:43AF677F-5BCB-45D5-8962-B66D552C68D3@xxxxxxxxxxxxxxxx
Sorry I was not more clear- basically - I have 70 tables- all identically
set
up but for different clients. Monthly I purge them of data, compact the
databse, download new data from the corporate tool, and import to Access
then
run reports.
If I import the below sample - a small portion only but in the exact
format
I can get the data - to an exisitng table that is empty, and the table
data
types are TEXT then the leading zeros are stripped off. The first is a SSN
and the second an internal identifier of the 401K plan number, both of of
course have no math done with them. Yet - if I import to a new table
defining
each column through the wizard, assign field as text then it does not
strip
the leading zeros.
The tables are all Access 97 converted to 2000, to XP to 2003 and now to
2007 over the last years, I tried creating a whole new database in 2007 -
importing a data set to a new table, clearing it via delete query - and
reimporting- zeros stripped. Access has no problems re writing the data
once
it is 'in' Access to other tables etc. I tried imput masks which Help
tells
me are ignored when importing text, I tried csv, setting minimim data
lengths
etc. Nothing worked. As I had Access 97 still installed I am setting up
all
data as a front end back end and importing it to 97, but as you can
imagine
this is a bit of a pain.
SAMPLE:
Row Participant_ID Plan_ID
1 001122330 061226
2 001144556 061002
"Jeff Boyce" wrote:
I'm not sure I fully understand your situation and what you are trying to
accomplish.
I will point out that "numbers with leading zeroes" (e.g., zip codes from
the US East Coast) are NOT numbers, they are characters/text. This is
because there is no need to perform math on them (you don't multiply zip
codes, right?!).
One approach might be to create an "import" table, with the fields
defined
as you know they should be. Then, when you wish to import data, you
import
it into the known import table. From there, you can use queries to parse
the data out to final permanent tables as needed.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"rnvhill" <rnvhill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E63BC765-7527-4747-BC5F-64B7FF71065A@xxxxxxxxxxxxxxxx
I can get the data in txt or csv format, but not with text identifiers.
There
are 70+ files with 200+ MB of data each month. I can not get the
formats
changed as it is a corporate wide reporting tool and I had to get
approval
just to use Access!
I can import to a new table in Access 2007 and retain the leading zeros
in
the data type TEXT, but not to an exisitng table.
I 'could' define the import each time to a new table, then F2 the old
table,
copy the name- delete the old table - then rename the new, but this is
not
an ideal solution.
Is there another way? and just because I am cranky... why can't Access
trust
me to tell it that the number should be considered as TEXT? I would
have
much
preferred a pop up error telling me that some data is possibly not in
the
right data type than an arbitray strip of the leding zeros. It worked
all
the
way back through 95
Thanks
Rowland
.
- References:
- Prev by Date: Re: Import number data with leading zeros as text without losing l
- Next by Date: Problem linking or importing Excel 2002 spreadsheet to Access 2002
- Previous by thread: Re: Import number data with leading zeros as text without losing l
- Next by thread: Re: Import number data with leading zeros as text without losing l
- Index(es):
Relevant Pages
|
