Names change in spreadsheet import

[bobfin]

Every month I download an Excel spreadsheet from a vendor and import it into Access 2003. Even tho the first row has column names, a dozen fields have incorrect names and data types and I have to manually change them each time. The spreadsheet has columns representing the amount spent for specific months. The column names are all like "May-02", "Sep-03", etc. and are all currency. After the import, many of the fieldnames are like "1/5/02 0:00:00" instead of "May02" and the data type is text. The column formatting is "mmm-yy" on all the columns, even the ones where the field names match the column names. How do I get all the fields to import correctly?

 

[neileg]

If you input May-02 into Excel it will store this value as 01/05/02 (depending on your local date format) even if it displays May-02. So when you import this into Access it imports the stored value, not the displayed value. That's where your field names come from.

Excel doesn't support defined datatypes. It will accept any old rubbish in any cell. When you import the data into Access, it guesses the datatype from the first few rows of data. So if the first row contains text, it treates the whole file as text. You may also get tripped up by embedded spaces. Your Excel data may look like $100.00, but depending where the data comes from, it may actuall be stored as, say, space, space, space,$100.00, space. Now that still looks like currency to you, but to Access, that's text.