Import Problem

[Bill Sturdevant]

I am trying to import from an Excel file into an existing
table.

The first row of the excel file has the column headers
exactly the same as the field names in the table. Most of
the data is text. One column is a number and 4 of the
columns are dates.

When I key values into the first data row of the
spreadsheet, the data imports fine. In fact, during the
import at the point where it asks if the first row has
field labels, it comes up with the option already checked.

BUT, when I copy and paste special values into the data
rows of the spreadsheet and import into an existing table,
I get an error saying 0 records were deleted and 0 records
were lost due to key violations. When I look at the
table, I have a large number of records with all null
values, and none of my data is there.

If I import the copied and pasted values into a NEW table,
the data imports completely along with a large number of
records all with null values. The problem is that many of
the fields in the new table have been assigned a type of
Date when no date is present!

What am I missing?

 

[GVaught]

All it takes is one cell to be classified incorrectly in Excel. Ensure that
the data in cell column is indeed text, date or number. Also ensure that any
values that could be construed as a number by Access is indeed classified as
text in Excel. For example if you enter a value of 10 in an Excel field,
Access could construe this as a number even though you have the cell set as
text. To ensure the field is text in Excel generally you have to precede the
entry with '.

 

[John Nurick]

Hi Bill,

There may be more than one thing going on here. One factor is this:

Excel has a concept of a UsedRange, basically a rectangular area in a
worksheet starting at A1 and including every cell that's ever had data
in it. When you import, Access by default imports the entire UsedRange.
When you delete the existing data from the worksheet and paste in an
equal or smaller amount of new data, the UsedRange does not change. So
on the second import Access will import - or try to import - the empty
rows.

To reset the UsedRange you need to delete the actual cells (or rows or
columns) that contain (or once contained) data. It's not enough to clear
them (delete the data they contain). Alternatively you can explicitly
adjust the UsedRange in VBA.

As for creating date/time fields even when you're importing blank data,
I don't know. I've seen other phenomena which may have a the same cause,
but haven't been able to pin it down. Again, resetting the UsedRange
should stop this happening.

 

[GVaught]

Also, one other thing. Ensure each column of data is the same. Don't mix
text values such as 'Michigan' and then several records down and in the same
column have a date 12/12/03. This will cause import problems.

 

[Bill Sturdevant]

I have been working with this all day and I have compiled
the following notes:

1.I already delete and clear all rows below the last row
of data. I do the same for columns to the right of the
rightmost column of data.

2. All of the data came from extracting an Access database
into Excel and then copying and pasting into my import
template.

3. Most of the dates had spaces in them, as in " 1/ 1/2003"

4. Many of the fields had text in them but the format
was "Date"

5. Every field that appeared to be blank, actually had
some sort of "invisible" value in them. I had to go to
every cell that appeared to be blank and hit the delete
key.

6. Once I did all of this (extremely time consuming) I was
able to do a successful import into my existing table.

I would love to find a way around this. The problem is I
will be getting spreadsheets from all around the world and
I will not know where they got the data (copied and pasted
from an Access export, or copied and pasted from another
excel spreadsheet, or copied and pasted from a text
file). The only way I know for sure that works is if they
key the data directly into my import template, and that is
the most unlikely mechnism they will use.

Any thoughts on importing and building a table on the fly
and then programmatically verifying/formatting the fields
so I can use them?

 

[John Nurick]

Hi Bill,

My first feeling is that if the data is being exported from Access to
Excel and is hard to re-import into Access, the best thing to do is to
cut out the middleman (or use a different intermediate file format).

Comments on individual items inline.

I have been working with this all day and I have compiled
the following notes:

1.I already delete and clear all rows below the last row
of data. I do the same for columns to the right of the
rightmost column of data.

You can automate this in Excel VBA. Something like this air code:

Dim xlSheet as Excel.Sheet
Dim arFieldNames As Variant
Dim raR as Excel.Range
Dim j as Long

arFieldNames = ("First", "Second", "Third")
Set xlSheet = ActiveWorkbook.ActiveSheet
xlSheet.UsedRange.Delete
Set raR = xlSheet.Rows(1)
For j = 0 to UBound(arFieldNames)
raR.Cells(j+1).Formula = arFieldNames(j)
Next
Set raR = Nothing
Set xlSheet = Nothing

2. All of the data came from extracting an Access database
into Excel and then copying and pasting into my import
template.

3. Most of the dates had spaces in them, as in " 1/ 1/2003"

If you copy a cell containing an Excel date/time value and Paste Special
into another cell, what is pasted is the value (e.g. 19 December 2003 is
37974) and what you see depends on the format already applied to the
destination cell. Unless this is a date format you'll normally see the
numeric value, not a date.

This suggests that the "dates" with spaces in them are not Excel
date/time values but text representations of dates. You can use the
DateValue() worksheet function to convert these into date/time values.
If you also format these with a standard date format, they usually
import cleanly into Access Date/Time fields.

4. Many of the fields had text in them but the format
was "Date"

Excel doesn't have a real concept of data types in cells. Applying a
"Date" format to a cell means that numeric values in the cell will be
treated as date/time values and displayed as dates or times; but text
values in a date-formatted cell will normally be displayed straight.

5. Every field that appeared to be blank, actually had
some sort of "invisible" value in them. I had to go to
every cell that appeared to be blank and hit the delete
key.

Select one of these cells, do Alt-F11 to get the VBE editor and Ctrl-G
for the Immediate pane. Then try this (all on one line)
? "F>" & Selection.Cells(1).Formula & "< V>" &
Selection.Cells(1).Value & "<"

If it prints
F>< V><
the cell is empty and the effects you're seeing are probably (IME) due
to formats or the "UsedRange effect".

6. Once I did all of this (extremely time consuming) I was
able to do a successful import into my existing table.

I would love to find a way around this. The problem is I
will be getting spreadsheets from all around the world and
I will not know where they got the data (copied and pasted
from an Access export, or copied and pasted from another
excel spreadsheet, or copied and pasted from a text
file). The only way I know for sure that works is if they
key the data directly into my import template, and that is
the most unlikely mechnism they will use.

Any thoughts on importing and building a table on the fly
and then programmatically verifying/formatting the fields
so I can use them?

One very common source of problems importing data from Access is
inconsistent data within a column. Access (as Im sure you;ve seen) gets
badly confused by this. A useful trick is to stick an apostrophe in
front of each Excel value. This forces Excel (and Access) to treat it as
text; which means it gets imported to a text field in Access.

Once it's there, you can use queries to convert data types and generally
clean up the data.

Here are a couple of little functions for handling the apostrophes:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub