Import number data with leading zeros as text without losing leadi

[Guest]

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

 

[Jeff Boyce]

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

 

[Guest]

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]

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

 

[Guest]

Trust me- I need Access for this and SSN is the primary key throughout all
the other Access databases that are provided to me. I understand the
inefficiencies and bad data base set ups, but I am not in a position to get
these changed. I wish I was, and I have tried, but I was not able to make it
so.

I appreciate your suggestions, but if we could just stay within the universe
defined as "how can I import a SSN to an existing table without Access
treating the SSN as a number and removing the leading zeros. The field is
defined as text in Access, the tool that I get the data from cannot place the
double quotes around the data I want defined as text."

This worked in the above manner until Access 2007, if the answer is to not
use Access 2007 then so be it - I will have IT remove it and complete their
questionnaire about its capabilities appropriately.

Thank you

 

[Jeff Boyce]

I wasn't suggesting changing the source data, just looking at how you store
it in YOUR database.

Please return to an earlier response.

Create a table defined as you required, then import into (i.e., append) that
table.

Regards

Jeff Boyce
Microsoft Office/Access MVP