Importing an Excel data file

[infact]

While trying to import an Excel file with 20,000+ records into Access, I am getting an "unable to translate field" error message which references some of the values in the first field. This field supposedly was formatted as text, and can be either a one or two digit number or a combination of letters and numbers. These alphamnumeric names are being flagged in the error. I attempted to reformat some of these fields in Excel making them text again, then hitting f2 and then enter. This seems to have fixed the problem, but this is too tedious to do for 20,000 records. Is there a way to reformat this field for all 20,000 records at once ? Appreciate the help.

 

[HandsOnAccess]

What is the value of the said field in the first row of the excel sheet?

In case it is only numeric, try placing a row with text data.

Import and Let me know how it goes...

Regards,

 

[psemianonymous]

The only reason I can think that your import would fail is if you're trying to import into an Access table with a date/time or number data type field. Import into Access with all "Text" fields, and once it's imported in Access, then move it to a table with the proper data types.

 

[infact]

Thanks for your reply, pseale. The first field in record is a machine name. First we used a number for the name (1,35, etc.) Then we set up other names such as "p76b", "t33001", etc. (alphanumeric). In Excel this caused problems, so all these fields were reformatted text. Then when I attempted to import the file into Access, the transfer error referenced the lines with the alphanumeric names. Excel must be mixing up the formats, treating the numbers as number values even though they were converted to text. As previously indicated, I reformatted some number fields as text, hit f2, enter, and then imported them with some records with alpah names into Access with no problem. But I can't manually do this for 20,000+ records.

 

[SkipVought]

Hi,

In Excel...

Select the column...

Sub MakeNumericTextInColumn()
   dim r as range
   With Selection
      for each r in range(Cells(2, .Column), Cells(2, .Column).End(xlDown))
        with r
          if isnumeric(.value) then
            .value = "'" & .value
          end if
        end with
      next
   End With
End Sub

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[infact]

Hi Skipvought - Thanks for the code. I ran this but it made the machine names that were alphanumeric (DP1234)blank, and left the names that were numbers as is.

 

[SkipVought]

That's odd! It works perfectly for me as copied.

What did you change?

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[infact]

Skip, I tried it again and it worked! Maybe I used a bad copy of the excel file. Thanks very much, again. This will be a big help.