Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    29

    Red face Unanswered: Importing Excel Spreadsheets

    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.

    Thanks in advance!

    Bailee220

  2. #2
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    Have you tried importing the spreadsheet without selecting "first row contains column headings"?

  3. #3
    Join Date
    Oct 2003
    Posts
    29
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    Try using an import spec or...

    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.

    Code:
    
    Sub AlterTable_import()
    Dim dbs As Database
        
    Set dbs = CurrentDb
    
        With dbx
        
            dbs.Execute "ALTER TABLE TABLE_NAME" & "ALTER COLUMN [COLUMN_NAME_1] number;"
            dbs.Execute "ALTER TABLE TABLE_NAME" & "ALTER COLUMN [COLUMN_NAME_2] datetime;"
    
            
        End With
    
    
        dbs.Close
        
        Set dbs = Nothing
        
    End Sub
    Last edited by michelin man; 01-19-04 at 15:17.

  5. #5
    Join Date
    Oct 2003
    Posts
    29
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    when you do the append query tell the query not to append the column name

    where <> column_name

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •