Type Conversion Failure using TransferSpreadsheet during Excel to Access Import
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.
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.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
sorry, there is no import spec on transfer spreadsheet
ASKER
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.
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.
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,acSpreadsheetType
When you have in the "TargetTable" the three needed fields defined as text (or number/text/text), Access "knows" the format.
Nic;o)
The transferspreadsheet doesn't need a filespecification as you can give the needed format in the destinationtable like:
DoCmd.Transferspreadsheet acImport,acSpreadsheetType
When you have in the "TargetTable" the three needed fields defined as text (or number/text/text), Access "knows" the format.
Nic;o)
ASKER
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.
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.
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, ""
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, ""
ASKER
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.
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.
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)
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)
ASKER
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.
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.
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)
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)
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
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.
ASKER
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.
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.
File>GetExternalData>Impor
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.