I'm trying to import an Excel spreadsheet, using the Access Import Wizard. The Excel spreadshee has a field in it that is set as TEXT however, it has the first, let's say 100 lines of this one field as numbers (still set as text), and then the remaing data in that column is Alpha and numerica data in it. When I try and import this field, Access sees it as a number field and I'm unable to change it. When stepping through the wizard, it doesn't allow me to change columns so I could change that column as importing at text. The only way I can get it to import correctly is to resort the Excel spreadsheet, so the alpha characters and numbers are at the top of the file. So therefore it sees that specific column as text field. Is there something else I can do to import this correctly? Is there something I need to set up so I can change the wizard so I can change the field column names and such on the other columns? Oh, the columns have headings so when I do the wizard, I select the sheet, and then I select that it has headings.
Yes, I have. and it worked just fine. But I need for the column to have the headings in them. This import is really being done from a Macro. And the Macro imports the file that people use. And I didn't want to have to do any field manipulating of the Spreadsheet.
Import the spreadsheet without the column heading then append the data to a table with column headings.
1. Import spreadsheet
2. Clear out destination table
3. Append to the destination table from import table
or from the macro call this block of code and change the data_type of the table.
Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute "ALTER TABLE TABLE_NAME" & "ALTER COLUMN [COLUMN_NAME_1] number;"
dbs.Execute "ALTER TABLE TABLE_NAME" & "ALTER COLUMN [COLUMN_NAME_2] datetime;"
Set dbs = Nothing
That is some good thoughts. However, I would have to delete the first row of the import because it will import the row with the column headings. Although I might be able to get the people to save the spreadsheet, that is to be imported, without the headings. I could go that way. But if there was any other way.
I know how to do specs, but when you import an Excel file, I don't see where you can use a specification. There is no way to go into advance for the spec.