ImportExportSpecifications

[sphinney]

Does anyone know where I can find a writeup about the XML that Access 2007 uses in its ImportExportSpecification objects?

I'm using the ImportExportSpecifications to import text files into Access tables. I can create, modify, and execute the XML just fine, except for one thing. I can't specify to begin importing the text on some line other than the first.

For example, some of the text files I want to import contain a few header lines at the top of the text file with extemporaneous data. I want to specify in my ImportExport XML to begin importing on, say, the 10th line.

I tried to use the Access "Get External Data" UI to perform a test import to see if I could specify to begin importing text of some line other than the first line. I thought I could "Save import steps" and then look at the resulting XML in the CurrentProject.ImportExportSpecifications collection. But the UI doesn't provided the functionality to skip the first few lines of a text file, either.

(One could try to use Microsoft Excel, which does allow the user to skip the first few lines of a text file when importing. But Excel doesn't allow the user to save the import steps and view the XML!)

So, if someone knows where there's an explanation or writeup for the ImportExport XML, I would be very greatful. Or, if you know how to tell access how to start importing text on line other than the first, that would be even better!

Below is a sample of how I'm using the XML (not sure how it will come through in a post, though).

Thanks in advance for your help.
sphinney

private sub EXECUTE_IMPORT(byval MyFieldWidths as string, _
                           byval MyFileName as string, _
                           byval MyDelimChar as string, _
                           byval MyTextQualChar as string)
Dim BASE_XML As String
Dim IMPORT_SPEC As ImportExportSpecification
Dim CNTR As Long
Dim COLS() As String
Dim COLUMNstr As String
 
    'Create the base XML string
    BASE_XML = "<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbCrLf & _
               "<ImportExportSpecification Path = ""<<DB_NAME>>"" xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & vbCrLf & _
               "      <ImportText TextFormat=""<<DB_TYPE>>"" FirstRowHasNames=""<<FIRST_ROW_NAMES>>"" FieldDelimiter=""<<DELIM>>"" TextDelimiter=""<<TEXT_QUAL>>"" CodePage=""437"" Destination=""OX_TXT_IMPORT_tbl"" >" & vbCrLf & _
               "               <DateFormat DateOrder=""MDY"" DateDelimiter=""/"" TimeDelimiter="":"" FourYearDates=""true"" DatesLeadingZeros=""false"" />" & vbCrLf & _
               "           <NumberFormat DecimalSymbol=""."" />" & vbCrLf & _
               "             <Columns PrimaryKey=""""><<COLUMNS>>" & vbCrLf & _
               "             </Columns>" & vbCrLf & _
               "   </ImportText>" & vbCrLf & _
               "</ImportExportSpecification>"
 
    'Split the MyFieldWidths string into an array
    COLS = Split(MyFieldWidths , ",", , vbTextCompare)
 
    'Loop through the fields and build the column string
    For CNTR = LBound(COLS) + 1 To UBound(COLS) + 1
        COLUMNstr = COLUMNstr & "                 <Column Name=""Col" & CNTR & """ FieldName=""Field" & CNTR & """ Indexed=""NO"" SkipColumn=""false"" DataType=""Text"" Width=""" & COLS(CNTR - 1) & """ />" & vbCrLf
    Next CNTR
 
    'Customize the base XML
    BASE_XML = Replace(BASE_XML, "<<DB_NAME>>", MyFileName)
    BASE_XML = Replace(BASE_XML, "<<DB_TYPE>>", "Fixedwidth")
    BASE_XML = Replace(BASE_XML, "<<FIRST_ROW_NAMES>>", "true")
    BASE_XML = Replace(BASE_XML, "<<DELIM>>", MyDelimChar)
    BASE_XML = Replace(BASE_XML, "<<TEXT_QUAL>>", MyTextQualChar)
    BASE_XML = Replace(BASE_XML, "<<COLUMNS>>", COLUMNstr)
 
    'Execute the XML
    CurrentProject.ImportExportSpecifications.Add "TXT_IMPORT", BASE_XML
    Set IMPORT_SPEC = CurrentProject.ImportExportSpecifications![TXT_IMPORT]
    IMPORT_SPEC.Execute
 
 

[NeoPa]

I'm afraid I cannot be of too much help as I don't use either XML or Access2007.

However, I've done some importing / exporting in my time and I'm fairly sure there is no way to specify which line to start on. There IS an option to treat the first line as a header, but this is a simple boolean (flag - on or off). I suspect that is all you will get as far as that option is concerned.

If importing, it may be possible to import all the data and then remove x lines at the top of the table (can't be done as indexed of course at this point).

Good luck.

[sphinney]

Thanks, NeoPa. I'd done a fair amount of searching on MSDN and the internet before posting here. I hadn't found much, but wondered if there was something I was missing. That's why I posted my question.

Your suggestion about importing all the data first is a good one. How would you do it?

I could import the entire file into a table/recordset with one field, then figure out how to parse it into multiple fields later. Problem is, some of my text files have 50+ fields and each line of text easily exceeds 255 characters (too big to put in a single "Text" field). I guess I could use a memo field, instead.

Does this sound reasonable or would you use some other object (as opposed to a table/recordset) to temporarily hold the text file data?

Thanks,
sphinney

[NeoPa]

It all really depends on the structure of your data records.

I never use Memo fields myself, but I've heard they're very much less flexible than text fields.

I would consider the approach you suggested, but you may have to give some consideration to the actual format of the data itself.