By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

ImportExportSpecifications

P: 69
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

Expand|Select|Wrap|Line Numbers
  1. private sub EXECUTE_IMPORT(byval MyFieldWidths as string, _
  2.                            byval MyFileName as string, _
  3.                            byval MyDelimChar as string, _
  4.                            byval MyTextQualChar as string)
  5. Dim BASE_XML As String
  6. Dim IMPORT_SPEC As ImportExportSpecification
  7. Dim CNTR As Long
  8. Dim COLS() As String
  9. Dim COLUMNstr As String
  10.  
  11.     'Create the base XML string
  12.     BASE_XML = "<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbCrLf & _
  13.                "<ImportExportSpecification Path = ""<<DB_NAME>>"" xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & vbCrLf & _
  14.                "      <ImportText TextFormat=""<<DB_TYPE>>"" FirstRowHasNames=""<<FIRST_ROW_NAMES>>"" FieldDelimiter=""<<DELIM>>"" TextDelimiter=""<<TEXT_QUAL>>"" CodePage=""437"" Destination=""OX_TXT_IMPORT_tbl"" >" & vbCrLf & _
  15.                "               <DateFormat DateOrder=""MDY"" DateDelimiter=""/"" TimeDelimiter="":"" FourYearDates=""true"" DatesLeadingZeros=""false"" />" & vbCrLf & _
  16.                "           <NumberFormat DecimalSymbol=""."" />" & vbCrLf & _
  17.                "             <Columns PrimaryKey=""""><<COLUMNS>>" & vbCrLf & _
  18.                "             </Columns>" & vbCrLf & _
  19.                "   </ImportText>" & vbCrLf & _
  20.                "</ImportExportSpecification>"
  21.  
  22.     'Split the MyFieldWidths string into an array
  23.     COLS = Split(MyFieldWidths , ",", , vbTextCompare)
  24.  
  25.     'Loop through the fields and build the column string
  26.     For CNTR = LBound(COLS) + 1 To UBound(COLS) + 1
  27.         COLUMNstr = COLUMNstr & "                 <Column Name=""Col" & CNTR & """ FieldName=""Field" & CNTR & """ Indexed=""NO"" SkipColumn=""false"" DataType=""Text"" Width=""" & COLS(CNTR - 1) & """ />" & vbCrLf
  28.     Next CNTR
  29.  
  30.     'Customize the base XML
  31.     BASE_XML = Replace(BASE_XML, "<<DB_NAME>>", MyFileName)
  32.     BASE_XML = Replace(BASE_XML, "<<DB_TYPE>>", "Fixedwidth")
  33.     BASE_XML = Replace(BASE_XML, "<<FIRST_ROW_NAMES>>", "true")
  34.     BASE_XML = Replace(BASE_XML, "<<DELIM>>", MyDelimChar)
  35.     BASE_XML = Replace(BASE_XML, "<<TEXT_QUAL>>", MyTextQualChar)
  36.     BASE_XML = Replace(BASE_XML, "<<COLUMNS>>", COLUMNstr)
  37.  
  38.     'Execute the XML
  39.     CurrentProject.ImportExportSpecifications.Add "TXT_IMPORT", BASE_XML
  40.     Set IMPORT_SPEC = CurrentProject.ImportExportSpecifications![TXT_IMPORT]
  41.     IMPORT_SPEC.Execute
  42.  
  43.  
Dec 19 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 21 '08 #2

P: 69
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
Dec 22 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 22 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.