Problems importing Excel sheets

[conneticat]

I'm trying to help someone else, and I'm not sure what he's trying to do, (and I don't "do" Excel) but...

He has 169 separate Excel folders, and they all have the same headings in the same order (good start, at least).

He wants to import them all into a single Access table...

Question 1: When he imports an Excel sheet with a column that is alpha numeric, ie "30A", "344", etc. (and is formatted in Excel as text), Access immediately decides that column is numeric/double, and kicks out the records that are alpha numeric. It doesn't matter if you import the sheet into an existing table or let it create a new one.
Microsoft has a funky work-around for this problem that includes conversion to HTML which I won't even go into.

Question 2: Quickest way to get all 169 Excel files into one Access table?

 

[lameid]

Import one of the files...

Fix the data type in the table and rename it if you want...

Import to the new table itereatively... The File System Object and the Dir functtion come to mind.

There is also a registry hack that controls the number of lines Access looks at when importing files... As long as there is a value with non-numeric characters in the first so many rows it will work.

The Registry Hack... I don't have the one for Excel handy but for text on Access 2003 on an XP machine this will increase it...

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text]
"win32"="C:\\WINDOWS\\system32\\mstext40.dll"
"MaxScanRows"=dword:00000032

The Excel one should be similar referencing msExcel40.dll (assuming I remember correctly).


A cheap way is to open a file and to add the word text (or [red]I Hate Excel[/red] for that matter) in a row in all the text fields... then delete that record after import.

Some people will go as far as to automate Excel and import the data cell by cell. You could also automate Excel and save everything as text.

Remember Excel is an awesome Spreadsheet product but when it comes to being a database or a data transfer format, it is a lemon.

 

[SkipVought]

I'd suggest looping thru each Excel workbook, prepending each numeric value with an apostrophy...

dim r as range, iCol as integer
iCol = 3  'or whatever column of interest
for each r in Range(Cells(1, iCol), Cells(Cells(Cells.Rows.Count, iCol).End(xlUp).Row, iCol))
  with r
    if isnumeric(.value) then
       .value = "'" & .value
    end if
  end with
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[conneticat]

Both really good suggestions! Thanks so much!

 

[MazeWorX]

alternativly create a spec file for the import telling access what the field types are

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[SkipVought]

MWA,

The problem is a TEXT column containing NUMERIC values. And believe me, it IS a problem, I've faced many time!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lameid]

Skip is right the key here is that the import is from Excel and Access does not support file specifications for Excel. Why would MS not allow you to specify the datatype from a format that does not have strong datatyping? I know, it is ridiculous.