Export Errors/Type Conversion Errors

Shanan212
Shanan212 used Ask the Experts™
on
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "TEMP", "C:\Users\" & username & "\Desktop\" & iName, True

Hi,

I am exporting/appending a table via the above code.

The problem is, whenever I do the appending via manual way (by going through the Access Wizard) it appends fine.

However, whenever I try to append it via the above code, it gives 'Type Conversion' Error on a certain column (column H)

This column H is definted as 'Text' on the database

Also in the excel file, defined as text (and I tried to re-define it via the following vba code)

    Columns("H:H").Select
    Selection.NumberFormat = "@"
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True

^ Regardless of what I do, when I do the appending manually, it imports fine. When I do it through code, it gives a list of import errors on column H.

Any help is much appreciated!

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I don't get it?

http://support.microsoft.com/kb/109376

^ That did not help. The problem is, my rows are already defined as text!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
A sample of the the Excel file would help greatly...

What's all the TextToColumns stuff for:
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True

This may be the culprit....

The format may be: "@", ...but Access relies on the "Datatype".
Your code above is setting it as: xlFixedWidth
Who know how Access will try to resolve this?
Also unknown is what Access thinks of:  FieldInfo:=Array(0, 2)
?

In other words, if you just manually inserted the "required" text in the cells (the text that really gets inserted after all the "massaging"), ...does this work?

JeffCoachman

Author

Commented:
I am lost in  your last sentance.

Please see the attached sample file.

How I am doing it that, the text to column, etc is done on the excel macro.

Then the importing is done on the Access Macro.

A a unique thing is, this (import error) happens on only certain data (or files of data) which I try to import into Access.

Thanks for the help!
National-Matrics---ProBill-Data-.xlsx
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
1. My point was that in order to test this, I would have to have your same setup and try to see if I could replicate the issue.

2. My other question was, ...If you inserted the correct data "manually", would it work?

3. There is No data in column H in the sample you posted...?
Is this the way it is normally?..
What type of data is supposed to be in this field?
(perhaps that is the issue, ...That Access does not know if this is Null, or an empty String...)

Author

Commented:
1. To do this I will have to show you the database. Which I can but I want to see if it can be done without it. THanks!

2. If I manually import it via the Access wizard, it works fine.

3. There is no data...however there is data here and there which is like

S212
R221
S232

The data type should be text. Same field is defined as text in Access.

How would I let access know it an empty string?

Even when there is nothing inside cells (some of course) the error shows. Do you require the database or got any advice for me?
Thanks!

Author

Commented:
Please see attached sample database.
SAMPLE.accdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Still confused...

The error table states an error on row: 32, 141, 156, ...etc
...yet there are no rows (records) in the table "probills"?

What I need is a sample of this *entire* system.
So that I can do exactly as you are doing and replicate the error.

The basic error is that Access does not understand what the data is in those rows, so it fails and throws the error.

But again, the PROBILLS table has no records, so I cant even see what the data is in the rows it should have imported (row 1-31, ...for example)

Author

Commented:
Please see the attached. This is the file that I am try to append to the table Probill
National-Matrics---ProBill-Data-.xlsx
MIS Liason
Most Valuable Expert 2012
Commented:
Here is what may be happening...

In your file, there is no data in Col H until the last line.
Access scans the first few line of data in a field to determine the datatype.
Since there is no data in the first few lines, Access can't decide what the datatype is and throws the error.

One way around this would be to use a (blank) "Staging" table, with all the fileds already defined.  Then import into the staging table.

And just to be sure, ...are you explicitly defining the datatype of this field as text in your import specification?

Author

Commented:
Nope. The table Probill in the database already got a column called "Sub" which corresponds to column H in Excel.

The "Sub" is already defined as text right? So I am appending it to that table

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "Probill", "C:\Users\" & username & "\Desktop\" & iName, True

Isnt this already a 'staging' table you as you mentioned?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Again, I really need a way to simulate your entire process... to investigate further.

As I stated, if you loaded column H with dummy text in all the records, does it append?

Author

Commented:
All coding is inside the Access.

The Sub "ImportExcel (iName as String)" get triggered by a sub in Excel.

What I am realllllly doing here is this

Take the Excel file. Append to Access - TEMP table (hidden). (Lets say the Excel contains the latest data)

Then taken Unique values from Probill table (say there is data), and append them to the temp table.

Delete all contents in Probill table

Then copy Temp table to Probill Table

Clear Temp table


^ all above coding is in Access Database I posted

You may temporarly run the code by creating a sub and calling it with the excel file name (I know you already know this - just suggesting)

Thanks!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<Nope.>
Nope to what?
Pleas be specific.

Again, if you posted dummy text in the excel file does it Append?
Yes or No?

Your Access code may need some tweaking in the error handling, and the way you are running the SQL.
Try not to use "On Error Resume Next", ...try to prevent or trap the specific error.
(Only use  "On Error Resume Next" if the sub is simple, which yours is not)
Also use code like this to run SQL:
Currentdb.execute "Your SQL", dbfailonerror
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I'll play around with your code tonight...

Author

Commented:
After reading through your comment, I thought of doing this (since Access looks at top few line) I am assigning ' to those cells.

Never gave me an error!

    For count = 2 To 15
        If (Cells(count, 8).Value = "") Then
            Cells(count, 8).Value = "'"
        End If
    Next count

Thanks for all the help!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
LOL!

Great.

But you could have accepted your own post as the the actual "solution"

;-)

Jeff

Author

Commented:
:)

Yea but still your comment initiated the idea. Thanks still!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial