?
Solved

Type Conversion Failure using TransferSpreadsheet during Excel to Access Import

Posted on 2005-02-24
14
Medium Priority
?
2,114 Views
Last Modified: 2007-12-19
Hi there,

I am importing an Excel spreadsheet into a table in Access called [Import]. Each field on my Access Table has been declared as either a Text or Memo field.

When I run the TransferSpreadsheet command I am getting an Sheet1$_ImportErrors Table created. This tells me that in the Field "Date Added" on lines 41-48 have a Type Conversion Failure. I dont understand why this is as I'm importing the data to a Text field and I thought that Date Formats can get Parsed to Text Format no problemo.

The data on the Excel sheet in this field in those rows is as follows,
Row Date Added
41    Oct-04
42    Oct-04
43    Oct-04
44    Nov-04
45    Oct-04
46    June 2004
47    Dec 2004 ORC
48    Dec 2004 ORC

in some other rows the date format is "Sep-04".

My problem is that I dont want this error_table created. I want to load the excel sheet into an Import Table and then do the Type Checking myself using VBA code and then copy the data either onto my Project Tables or onto an Errors Table.

The weird thing is when I delete all the surrounding rows in the sheet, leaving just rows 41-48, then it loads fine and no error_table is created !

Can anyone advise me if there is a way of forcing the TransferSpreadsheet to do a type conversion to Text ignoring the Excel formats ?

Thanks
Robert.
0
Comment
Question by:robert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 13392376
try to create an import specification if you haven't done so.
File>GetExternalData>Import
follow the wizard and set fields as they should be
before finishing click Advanced and create the import spec.

use the import spec in your command line.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 800 total points
ID: 13392407
You have multiple options:
1) copy/paste the header in the excelsheet giving two the same headerrows, thus all fields will be seen as text allowing you to manipulate it afterwards.
2) Create a linked table to the spreadsheet and manipulate the column using one or more queries
3) Create an import specification by doing it once manual and when the fields have been defined by pressing the [Advanced] button you can save this under a name that can be used in the transferspreadsheet command.

What's the direction you want to go ?

Nic;o)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 13392421
sorry, there is no import spec on transfer spreadsheet
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:robert
ID: 13392501
Hi Guys,

Thanks for the comments. I like the idea of creating an Import Specification but Capricon posts that it cannot be specified in the TransferSpreadsheet command. I could try Nico's (i) suggestion of copying the First Row twice to "fool" the import routine to treat each row as a Header. I will give that a try. If you find out that the Import Spec can work then please let me know, that would be a more robust solution.

Regards
Robert.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13392671
Oops, indeed mixing the transfertext and the transferspreadsheet commands.
The transferspreadsheet  doesn't need a filespecification as you can give the needed format in the destinationtable like:
DoCmd.Transferspreadsheet acImport,acSpreadsheetTypeExcel3,"TargetTable","c:\test.xls",true

When you have in the "TargetTable" the three needed fields defined as text (or number/text/text), Access "knows" the format.

Nic;o)
0
 

Author Comment

by:robert
ID: 13392720
Hi Nico,

Your tip of copying the header row seems to work. When I have a copy of the header row I dont get the Error Table created and when I remove the second header row I get the Error Table again.

The only problem with this method however is this 2nd Header Row gets imported into my Table data. I guess I could add some code to find this row and then delete it but to be honest this is quite an ugly work-around. Any other ideas/suggestions ?

Regards
Robert.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 13392761
there is another way where we can use the ImportSpecification
and that need renaming the .xls to .csv
and we will use the  TransferText with import spec

somthing like this
    DoCmd.TransferText acImportDelim, "ImportSpecA", "TableName", strCSV, True, ""
0
 

Author Comment

by:robert
ID: 13392762
Hi Nico,

In my Access "TargetTable" [Import] all the fields are defined as Text or Memo. The problem seems to be that when Access is doing the TransferSpreadsheet it decides that my Excel column has a Date field in it and then is unwilling to parse a Date field (from Excel) to Text field (to Access) . I maybe wrong but that is what seems to be happening.

I would love to be able to get the TransferSpreadsheet command to work as this seems a neater solution than copying a 2nd Header Row and then having to seek and destroy it in the Access table.

Regards
Robert.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13392898
Hmm, Access is rather stubborn in wanting to detect the field's datatype and it will check the first 8 rows to determine that.
So in your case when they are all date you'll get a date, else text....
Thus the second header trick will "fool" Access but needs additional handling and I use it only for incidental imports.
But when you use VBA, the removal of this extra header can be easily solved with this command:

currentdb.execute ("delete * from tblExcelImport where row = 'row';")

Did you try to link a table to the excel sheet ?

Nic;o)
0
 

Author Comment

by:robert
ID: 13392976
Hi Capricon
thanks for your suggestion. I am just trying to create an Import Specification for a CSV file but not having much luck. I am not exactly sure how this would work. Do I create the import specification from within Access or Excel ?

Hi Nico,
Thanks for your comments. I am not sure how linking the Spreadsheet will work, I have to read up on that. Thanks for your command for seeking and destroying the 2nd header row. I will give that a go and see how I get on too.

Regards
Robert.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13393209
The linking is just creating a new table and select "Link" from the options.
Now select on the popup form as Filetype "MS Excel .xls" and navigate to the spreadsheet.
The wizard will guide you.

The problem will however not be solved :-(
Best to go into the spreadsheet and select all cells to apply the Format/Cells... and define them explicitly to be "Text".

This should solve the problem for both the spreadsheet transfer and the linked "excel" table

Nic;o)
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 13393229
from access
File>GetExternalData>Import
follow the wizard and set fields as they should be
before finishing click Advanced and create the import spec.

use the import spec in your command line.
0
 

Author Comment

by:robert
ID: 13393531
Hi Guys,

Thank you for your support. I've tried all your suggestions.

Capricon,
with creating an Import Spec I am still getting errors on the import file, more errors than when I simply use the Transferspreadsheet command ! I have a feeling that the order that the fields are loaded-in is important. This is a nice feature of the TransferSpreadsheet function that the Field order is not important it seems to do a match automatically. I am not 100% sure if this is correct but this seems to be the case.

Nico,
I tried formatting every cell in my spreadsheet to Text but I still get Type Conversion Failure errors !

I am still in a position now where my only "working" option seems to be to create a 2nd header row and then do a delete of the 2nd row after the TransferSpreadsheet. If you have anymore ideas please let me know.

Regards
Robert.
0
 

Author Comment

by:robert
ID: 13393632
Hi Guys,

Im going to go for Nico's solution. Thanks Capricon for your support too. I'm giving you a share of the spoils ! :) (50 points for your help) and giving Nico the rump, 200. Thank you Nico.

See you both around. I'll probably be back !  :)

Regards
Robert.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question