?
Solved

Type Conversion Failure using TransferSpreadsheet during Excel to Access Import

Posted on 2005-02-24
14
Medium Priority
?
2,218 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

615 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