Importing Excel File Into All Text Fields

[Guest]

I'm currently using Access 2007 with Vista. I'm trying to import an Excel
file. The Excel file may have numbers in the fields, however, I want to
import them as Text no matter what they are assigned in Excel. And I don't
want Access to make it's own interpretation of what the field should be. I
always want it to import into a Text field. I've done this, but my problem
is that even though it's importing into a text field, it's still doing a type
conversion error on some of the data. In one column, the data is mainly
numbers, but there is so text in it. And where it runs into the Text data is
where the type conversion happens and I get an error file. Is there any way
to keep this from happening?

Thanks,
Nancy

 

[Guest]

Create a table definition that has the fields in the Excel table, but make
them text fields. Then do the TransferSpreadsheet into the exiting table
with the definition you want.

 

[Guest]

I'm not 100% sure what you're saying. But if you're basically saying to
create a table with the same fields as the Excel file and have them be text
and then import into this table. I've done this and it still errors out. I
can't have the Excel file sorted or anything, I just have to take it as it
is. Unless there's some sort of sorting I can do while importing? But if
this isn't what you're talking about, then can you please explain further?
Thanks!

 

[Guest]

You are correct about what I was saying. But, the problem you are
experiencing can still occur. I have found there is not "one best way" to
accomplish importing from Excel. It can take some experimenting to determine
which technique works in any situation.

Here would be my next suggestion.

Rather than import the Excel file directly, link to it as if it were a
table. Look at design view of the linked table to see what data types Access
thinks they are. Then create a query to append from the Excel table to the
Access table, and use whatever data type conversion functions are necessary
to coerse it to text.

 

[Guest]

Okay, I tried the linking part. And even with just linking the file, it has
an issue with the columns that have both text and a number in it. It gives
me a #NUM in the place of the text that should be there. So I can't even see
the text data in the column to even do an append query. Unless this is just
some hidden way of telling you that the file is both text and numeric. Which
I'm not thinking so. So it's not looking like this way will work either.
Any other suggestions? I do appreciate the help, I'm currently lost so any
help is great. Thanks, Nancy

 

[Guest]

A field is either text or numeric, so if it has numbers in some records, it
still should be defined as text. The problem is Access looks at some, but
not all, of the rows in the spreadsheet to determine its data type.
What you are experiencing is that the those rows with text are probably
further down in the data that Access uses to make a type determination. This
causes Access to define it as numeric, but then you hit a record with text,
and it chokes and stumbles into the wall.

The next thing I would try would be to convert the excel .xls file into a
..csv file before you import it. The, use the TransferText instead of
TransferSpreadsheet to import it. Now, you will want to do one import
"manually" (using the File, Get External Data, Import). When you get to the
TransferText wizard, click on Advanced. From here you can specify field
names, data types, etc. The click Save As and give the specification a name.
Now you can use that specification name in the TransferText. I think that
may take care of it.

 

[Guest]

I know and understand about Access only looking at some of the data. What I
don't understand is that row 1 of this Excel spreadsheet that I'm importing,
the field that I"m having troubles with, is actually text. But then about
the next 30 rows are numbers. So you would think it would assume to make
that a text. But it's like it ignores that first record. Frustrating. As
for the import specification, that won't work because this program that I'm
writing is using the same set of code to pull in all different files. Some
with 5 columns, some with 10 columns, some with just 2 columns. So I can't
use the same specification for all of them. But what I've done in another
section of this program that imports CSV files, is to create my own
Schema.ini file based off the CSV file. And that seems to be working just
fine. So I guess if they did do the change to CSV then I could go through
the same steps for this. I'll just go with this or see if I can't make the
people using and creating these files to make all the columns Text in the
Excel spreadsheet. That would solve all my problems. Thanks again for the
help. Nancy