Data Conversion Excel/Access
Bruce
files, other use Access or Excel. We have automated our
system so the customers can upload their own data but we
always have problems with Excel. If a column contains
alpha-numeric and pure numeric data in the various cells
we end up with mixed data types even if the user formats
the column as text instead of general. The problem occurs
when we try to export (or import) into SQL Server 2000. We
get the numeric values but not the alpha-numeric. In
Access we also receive type conversion errors for all the
alpha-numeric cells. If I do manual import into Access I
can preformat the data type for that field as text and it
works fine. Does anyone have any suggestions for a work
around? We have to be able to script the solution to avoid
manual processing of the files.
Peter Russell
Regards
Peter Russell
Bruce
goes well. The problem is that if the data was originally
input under the general type of format in Excel, Access or
SQL tries to import the pure numeric values as a Double
Data Type. As soon as the import process hits a cell with
an alpha-numeric string it fails to convert to text and no
data gets pulled from that cell. The column we are
importing into is a text column. This may be best shown by
example.
309 imports as 309
309,310 is not imported, the cell is left blank
>.
>
Joe Fallon
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.
--
Joe Fallon
Access MVP
"Bruce" <bdr...@stampfinder.com> wrote in message
news:1048e01c1a9b5$ac0d44d0$9ae62ecf@tkmsftngxa02...
Jeff Boyce
From your description, it doesn't sound like you are importing into a
pre-existing table that has a field defined as text-type. Are you importing
into an empty, pre-defined table, or are you creating a new table with each
import?
Good luck
Jeff Boyce
<Access MVP>
Andrei Smolin
This is a really serious problem that can be solved via a set of hardly
keeping requirements to your users. Or, maybe, you can supply your users
with one or several template files having correct cell formatting? If not,
you can make a program that will correct it according to needs of your
import program. If you have multi-language users you should also be aware of
decimal separator problem: it can be set to "," or ".". The same also
applies to dates.
--
Andrei Smolin
Active XL Report - Easy way to Excel reporting
www.afalinasoft.com
"Bruce" <bdr...@stampfinder.com> wrote in message
news:f13401c1a9d4$63855d50$9ee62ecf@tkmsftngxa05...
