DoCmd.TransferSpreadsheet skipping columns

[Sephaerius]

I have the need to import 30 files at a time, but I do not need columns A:Z so to speak. I understand range importing, but I'm not sure how to tweak this to skip unwanted columns.



What I have now is something like:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "destinationtable", varFile, True, "Log!B6:Q100"

And what I'd like to do is something like this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "destinationtable", varFile, True, "Log!B6:D100,F6:H100,O6:Q100"

I've been tinkering with it but can't get it to work. Is this even possible?

[ranman256]

The source query has the columns.
if you don't need them,omit them in the query.

[Sephaerius]

The source query has the columns.
if you don't need them,omit them in the query.

This isn't a query, I'm importing spreadsheets. I only want to import specific, non-sequential columns from the spreadsheets I import.

[CJ_London]

it can't be done like that, columns have to be contiguous - suggest copy to a temporary table (or link to the spreadsheet instead) and use an append query to copy the required columns to your destination table

[Peter M]

I think you can do this through XML import specifications. I do it frequently with CSV files and am pretty sure I've done it with excel too.

Use the wizard to Import your excel file, and skip the columns you want to skip, then "Save Import Steps." In the visual basic immediate window

? CurrentProject.ImportExportSpecifications.count
? CurrentProject.ImportExportSpecifications(#).name
? CurrentProject.ImportExportSpecifications(#).descr iption
? CurrentProject.ImportExportSpecifications(#).xml

where # is the most recent importSpec

Copy this XML string into a VBA module and then write a script to cycle through the files (as you would have done with the DoCmd.TransferSpreadsheet)


The following is probably more than you need but it's copied from my reference documents.
The XML description is a string description that can be edited and set using CurrentProject.ImportExportSpecifications(#).xml = [Valid XML String]
To manipulate long strings in VBA, I find it preferable to concatenate string pieces. I actually copy the specification in Excel to easily add the following:

xml = xml & “next line of XML code” & vbcrlf

for all the column lines


The components of the XML include the following:
Header
xml = xml & "<?xml version='1.0' encoding='utf-8' ?>" & vbCrLf
xml = xml & "<ImportExportSpecification Path='" & filepath & "' xmlns='urn:www.microsoft.com/office/access/imexspec'>" & vbCrLf
xml = xml & " <ImportText TextFormat='Delimited' FirstRowHasNames='true' FieldDelimiter=',' CodePage='437' Destination='" & tableName & "'>" & vbCrLf
xml = xml & " <DateFormat DateOrder='YMD' DateDelimiter='-' TimeDelimiter=':' FourYearDates='true' DatesLeadingZeros='false' />" & vbCrLf
xml = xml & " <NumberFormat DecimalSymbol='.' />" & vbCrLf


The header contains

• Source File: ImportExportSpecification Path = “full path filename” (Note – the example above uses chr(34) to populate a “ into the string. Alternatively you can use two double quotes “” or single quote. My preference is single quotes because they are easier to manipulate in Excel and easier to read. Access defaults to double double quotes when creating the XML.
• Text File Type: TextFormat – the options are “Delimited” or “Fixed”
• Header Row: FirstRowHasNames – True or False depending on whether or not the first row is the header row
• CodePage=”437” – unsure; ‘1252’ also works
• Destination Table:

• Destination= “MS Access Table name” (Creating a new table)
• AppendToTable = “MS Access Table name” (Appending to existing table)
• Date Format: How are date fields formatted in this file? DateOrder = “YMD” or “MDY”; DateDelimiter =”-“ or “/”; TimeDelimiter = “:”; FourYearDates=”true”; DatesLeadingZeros=””false””
• Number Format: Decimals identified with “.”

As mentioned above, copy this from a successful data import specification. You probably want to change the Source File and Destination File.

Columns and Primary Key
The listing of Columns is the core of the XML specification. It begins with the line <Columns PrimaryKey=””fld_PrimaryKey””>. If there is no primary key then fld_PrimaryKey is set to {none} otherwise it is the field name. This can be dynamically controlled and the code example below toggles between using the TCN as a primary key and not.
If NoPrimaryKey Then ‘If there is a primary key then set the primary key to none.
xml = xml & " <Columns PrimaryKey=""{none}"">" & vbCrLf
xml = xml & " <Column Name=""Col1"" FieldName=""C_TCN_NUM"" Indexed=""YESDUPLICATES"" SkipColumn=""false"" DataType=""Text"" Width=""17"" />" & vbCrLf
Else ‘There is a primary key, in this case the C_TCN_NUM
xml = xml & " <Columns PrimaryKey=""C_TCN_NUM"">" & vbCrLf
xml = xml & " <Column Name=""Col1"" FieldName=""C_TCN_NUM"" Indexed=""YESNODUPLICATES"" SkipColumn=""false"" DataType=""Text"" Width=""17"" />" & vbCrLf
End If


After the line <Columns...> the columns are defined with
<Column Name=””Col1”” FieldName=””fld_Name”” Indexed=””No”” SkipColumn=""false"" DataType=""Text"" Width=""17"" />
This contains the Column Name (sequentially listed), the Field Name, whether it is indexed or not (No, YesDuplicates, YesNoDuplicates), if the column is skipped or not, the DataType and Data Parameters (see below)
The column data types are the critical component of importing files and the following XML options are accepted. (Again, defer to the XML that was created with the Access wizard.)
DataType Options

• Text
• DateTime
• Currency
• Long

The list of columns is closed with </Columns>
Then </ImportText>
</ImportExportSpecifications>
To run the XML spec first an ImportExportSpecification must exist. The following is not necessary if you are over-writing an existing specification, but if no specification exists and error will post. To create a specification from scratch:
'Now you can add the specification to the project
step_CreateImportExportSpec:
CurrentProject.ImportExportSpecifications.Add name_of_spec, xml
CurrentProject.ImportExportSpecifications(0).xml = xml ‘Overwrites the ImportExportSpecification in index 0
DoCmd.SetWarnings False ‘turn off warnings like “unable to undo this command”
CurrentProject.ImportExportSpecifications(0).Execu te
DoCmd.SetWarnings True ‘turn warnings back on.


Some error checking can be added to deal with things like Primary Key Violations, over-writing table names, etc.