Invalid heading names

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I shouldn't be doing this on a Sunday morning BUT......
I'm really struggling with importing an Excel spreadsheet as you can tell
from the number of posts! I keep getting a message that says that the first
row contains data that Access can't use as valid field names. I've taken out
any punctuation characters, I've made sure that the heading doesn't contain
any reserved words like Date, Time etc and as far as I can see have taken
out all the spaces. Access says it will give the columns names but it
doesn't change any of them.What more can I look for? The spreadsheet doesn't
import as I get that extremely unhelpful message at the end of the process
could it be something to do with the headings?
HELP!!!!!!!
Thanks
Tony
PS I feel better now I've got that off my chest
 
Can you post some examples of the data in the EXCEL spreadsheet's rows?
Also, how are you trying to do the import -- File|Get External Data|Import ?
or TransferSpreadsheet?
 
What I do, all columns in excel add fld_ to the front of the column name.
Remove all spaces, replace with _ between field names like First_Name.
Remove all punctuation and formatting, first row. Also make sure no one
inserted a hard return in the column name as Access chokes on them, they are
not real returns in excel and you'll have problems with them later. One
other trick, seed the first row with the data type you want field to be
converted into. Another words if it is a date/time in the rows but you want
it to be text field for what ever reason make the first row after the
headings ABCDEFG. There are other ways to do it but I found these have
almost never failed (yet). They even beat access import specs...on almost
all occasions. Note I left myself a way out incase yours craps!
 
Hi Ken I'm using File|get External Data etc
The data in the cells is a mixture of text, numbers and dates. The heading
cells however are all text.
If I take the headings out the import will work OK but obviously I would
prefer to have the field headings in to make sure I have the correct data in
the right place. One thing about spreadsheets is that you can't tell if an
empty cell contains a space or nothing. Does this affect the import and is
it necessary to have something in every cell? How does Access cope with
empty cells?
Thanks
Tony
 
Thanks for the pointers Pete. I retyped the headings in the end and it
worked!
Thanks again
Tony
 
Retyped the headings and it worked!!!
Thanks
Tony
Ken Snell (MVP) said:
Can you post some examples of the data in the EXCEL spreadsheet's rows?
Also, how are you trying to do the import -- File|Get External Data|Import
? or TransferSpreadsheet?
Click to expand...
 
It's good if the row header text strings have no spaces or characters other
than _ and numbers and letters in them. Otherwise, ACCESS/Jet may change the
strings to "conform".

An empty cell in EXCEL will import as Null in ACCESS. I have not tried a
space, but I would think it should import as a space initially -- however,
if you edit that field's value later when viewing the data in a query or
form, Jet likely will truncate the blank to "nothing" (Null).
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top