Not All Data is Importing in to All Fields

[jalopez453]

Hello All,

I have been stuck on this import and I am not sure what else to look at to correct this issue. I have an excel file (XLSX) with a number of fields, which also match my table in my database. When I run my DoCmd.TransferSpreedsheet it does not import the first few fields of data but then will import the remaining fields. I have manually imported the file into the table and all the data for each field imports just fine. Any help would be great in resolving this problem or trying something else to get it to work.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblAfterADJ", filename , True, "HCH"

:banghead:

 

[The_Doc_Man]

Can you be more specific regarding the meaning of "first few" fields?

If the Excel spreadsheet data matches up correctly in format with the targeted table in Access, normally the TransferSpreadsheeet action works correctly. If there were type mismatches or if you were creating a new table, there is a known issue with potentially miscasting data types, but not for the case when the targeted table exists.

Do you get any error pop-ups?

 

[jalopez453]

My excel file has 29 fields, and the first 12 do not import the data from the excel file but there after the data imports just fine. I do not receive any type of error or error log once the import completes, which is why I don't understand why it is happening in the first place. The field format is general as the fields contain both numeric and alpha characters. The odd thing is that when I upload the file manually all the fields populate just from, 1 thru 29.

 

[Galaxiom]

My excel file has 29 fields, and the first 12 do not import the data from the excel file but there after the data imports just fine. .

Excel has Rows, Columns and Cells but not Fields.

 

[JHB]

... The odd thing is that when I upload the file manually all the fields populate just from, 1 thru 29.

Import it into a new table and then compare the two table structure, (fields type).

 

[The_Doc_Man]

For Excel, "General" is THE worst type of field from which to do an import because it is the (im-)moral equivalent of a Variant data type i.e. "faceless." JHB's advice might demonstrate to you that what is happening is that Excel doesn't know what to do with the import table's fields 1-12 corresponding to the worksheet's columns A-L. If you are creating a table from the import, the data type for each field comes from the first few rows of the spreadsheet for each column.

Is there any chance that for the spreadsheet you have some kind of formatting for a header where you have joined two cells, but that later on in the speadsheet this joining isn't done because you are past the header? (And it would be icing on the cake if the header resulted from joining 12 columns...)

 

[jalopez453]

After looking over everything and because the columns are formatted as general, the import is not sure what to do with the data. Looks like I will just have to import this manually for now until I can figure out away to format the source file to reflect the correct data type for the import instead of it being formatted as "General". The reason why this is a little more difficult for me to correct is because the data is coming from another program that is scripting in the information and pasting it into my excel. Hopefully I can update the copy paste scripting to see if I can apply formatting to the data it is copying and pasting into my excel file. Thanks again everyone for the tips and help, I really appreciate it.

 

[The_Doc_Man]

In past posts, we have seen cases where if you do the import to a table for which the correct data types have already been asserted, it SHOULD handle that correctly. It is the operation in which you create a new table that gives this import function the most trouble. However, if you did a "new table" type of import, it would TELL you what it thought the correct data types were by looking at the table design after the import. You would SEE the data types it thought it saw. That might give you some insight.

Another thought comes to mind. If this import happens often and the format is (or at least SHOULD) always be the same, consider treating the worksheet as a linked table at least temporarily, then run a query in which you do an INSERT INTO where the target is a pre-built but perhaps empty table and the source involves a query that enforces data typing.

INSERT INTO (A, B, C, I, J, K, R, S, T, X, Y Z) SELECT 
CDate([XLT]![A]), CDate([XLT]![B], ..., CLng( [XLT]![I]), CLng( [LXT]![J]), ...
CStr([XLT]![R]), CStr([XLT]![S] ), ..., CDbl([XLT]![X]), CDbl([XLT]![Y]), ... FROM XLT ;

The idea here is map the spreadsheet but do the import in a way that there is no possibility of misunderstanding the intended datatype to be stored in the target table. Yes, this would be a pain in the toches to type, but if this sort of thing is done often, you can probably write a VBA routine to do it once and be done with it.

You WOULD have to remember to unlink that temporarily mapped table when done with it, because otherwise the next time you open the DB file, it would probably barf if that mapping were not available.