Importing Excel Table into Access

[Bob Barnes]

I usually save the Excel file as a DBF file before
Importing it. Just be sure the structure of your
Excel File has the FieldNames in the first row with
no Blank rows thru the end of your data.

HTH - Bob

 

[Anita]

Thanks for the advice...I converted the file in DOS but I
can't import it now. Using the wizard, I try to import but
when I get the option to select file type as I find the
file to import, it does not list DBF. Just excel or text
or Dbase etc...Advice???

 

[Anita]

I get the error message: External Table is not in expected
format...I tried using Dbase 3,4 and 5...no luck...

 

[Bob Barnes]

My correction - the file you have now is...??
XLS??

If so, trying to Import a "DBase" would not work.

When you say "I converted the file in DOS" - It was a
XLS originally? I used 1-2-3 for years, but wasn't Excel
"always" Windows-based?

Bob

 

[John Masui]

Excel to Access transfer can be buugy, ususally becuase
Access treats one column as either text or number. If the
field is mixed with numbers and text then it will either
show error message for the text or visa versa. One
solution might be to covert the number data into text...
i.e. say it's column "C" which is having problem then
wirte a formula =c1&"" next to the column and drag in to
the end then copy and paste. this should covert the
number value to text. (no space between "")

Hope this helps.

 

[Tim Ferguson]

Excel to Access transfer can be buugy, ususally becuase
Access treats one column as either text or number. If the
field is mixed with numbers and text then it will either
show error message for the text or visa versa. One
solution might be to covert the number data into text...

An alternative is to create the table in Access first with correct field
types, and then get the Excel data with an append query rather than a
maketable.


B Wishes



Tim F

 

[Anita]

Actually I used Dos in Windows to convert my excel file to
txt initially. Then after receiving your advice, I
converted it to DBF. So the file was xls but now it has
the dbf extension. All fields have been converted to text
too. But I still have this problem. Have you encountered
this situation? Got more advice???Please help...

 

[Bob Barnes]

I always have better luck with Excel to DBF than to text.
I have some Notes from about 6 weeks ago about the pros-&-
cons. Will find & post here.

If you have an "Excel DBF" now, but sure all columns are
"contiguous" (no empty columns). Make sure each Column
Name has a Unique Name - remove any "spaces" in the Names.
Then, no "Open rows"...Just a "Block" of data where the
1st row has the Names (these become the Access Table's
FieldNames). John's suggestion on the text is Correct.

Excel is "onery" about saving the DBF files
(several "Questions") but it will save.

Then, in Access, use the Menu "File - Get External Data -
Import" - select the Path to the File & use the correct
"DB" (BBF III, IV). Then run it.

Let me know. Bob

 

[Bob Barnes]

Anita - Here's the LONG discussion I had with Joe Fallon,
one of the many knowledgeable people in these Newsgroups.
Hope it helps...

Joe - Thank you for answering. I always enjoy reading
your insight.

I made an Excel csv (just separates the column values by
columns). None of the "traditional delimited double-quote
around text, & no double-quotes around numbers".

Ran the code "Open a txt" file. The Dates read the first
character - IE - "4/16/03" was Input as "4" & converted
to "1/3/1900" in the Access table.

So...forced to format the Dates in Excel as numbers, then
Input txt & convert to Dates.

Years ago, Lotus 1-2-3 would produce a nice delimited csv
file with "proper double-quotes".
I did a Project in Syracuse almost 4 years ago where I
asked the Boston Corporate Ofc to give me a "True csv
double-quotes - delimited file" (which they did). Such
files could even be made from the DOS prompt.

Today...what is the best way to produce a Delimited csv
double-quote file from Excel data?
The fact is there are probably millions of small companies
that use Excel as their their "mainframe" database. I
have seen small companies resist using Access over Excel
for databases.

Bob

-----Original Message-----
I prefer any database format over Excel.
I also prefer ASCII delimited over Excel.
(I like semi-colon, | or ~ delimiters.)

Excel is NOT a database and so you have to live with all

of its eccentricities when using it with Access. The first
8-16 rows of data are critical. (It varies depending on
versions, registry setting and programming languages.) I
would inspect the Excel file and ensure that the first
data row had a representative pice of data in it that
would force the Guess to be correct.

This is especially true for Access Text fields and Memo fields.
Be sure that a piece of Text data and Memo (over 255

char) data appear in row 1 of Excel.Excel is a mix of Number & text. IE, "456" will be
interpreted as a Double in the DBF, but many rows further
down, there is a "B123" - & that is not brought into the
DBF, so I lose that value.(either csv or fixed-length) is solid working with code.
But, I have had difficulty getting reliable ASCII files
from Excel.