Results 1 to 3 of 3
  1. #1
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67

    Problem with Import CSV via XML but columns auto-order and type violations occur

    I routinely import multiple CSV data files using a XML script to set the data type, skip unnecessary fields, and apply indices and import the data into a unique DB per CSV file. The resulting file is typically close to 1 GB making it almost impossible to change the data type and/or index a field after a regular CSV import. It also very tedious to go through the wizard to create a new spec when there are variations in the base file. There are between 30 and 60 data fields depending on the flavor of the data extract.


    This used to work flawlessly except I am now having problems where the data fields are being automatically ordered alphabetically and Type Conversion Failures are being triggered.

    The underlying script is roughly:

    Code:
    xmlStr = ""
    xmlStr = xmlStr & "<?xml version='1.0' encoding='utf-8' ?>" & vbCrLf
    xmlStr = xmlStr & "<ImportExportSpecification Path = '" & filepath & "' xmlns='urn:www.microsoft.com/office/access/imexspec' >" & vbCrLf
    xmlStr = xmlStr & " <ImportText TextFormat='Delimited' FirstRowHasNames='true' FieldDelimiter=',' TextDelimiter='{DoubleQuote}' CodePage='1252' Destination = '& DestTableName & "' > " & vbCrLf
    xmlStr = xmlStr & " <DateFormat DateOrder='MDY' DateDelimiter='/' TimeDelimiter=':' FourYearDates='true' DatesLeadingZeros='false' />" & vbCrLf
    xmlStr = xmlStr & " <NumberFormat DecimalSymbol='.' />" & vbCrLf
      xmlStr = xmlStr & " <Columns PrimaryKey='{none}' >" & vbCrLf
    xmlStr = xmlStr & " <Column Name='Col1' FieldName='LongStringID_NotUnique' Indexed='YESDUPLICATES' SkipColumn='false' DataType='Text' />" & vbCrLf
    xmlStr = xmlStr & " <Column Name='Col2' FieldName='EventType' Indexed='YESDUPLICATES' SkipColumn='false' DataType='Text' />" & vbCrLf
    xmlStr = xmlStr & " <Column Name='Col3' FieldName='EventDate' Indexed='NO' SkipColumn='false' DataType='DateTime' />" & vbCrLf
    
    etc.
    xmlStr = xmlStr & " </Columns>" & vbCrLf
    xmlStr = xmlStr & " </ImportText>" & vbCrLf
    xmlStr = xmlStr & "</ImportExportSpecification>"
    The resulting output is two tables:
    DestTableName
    EventDate (no data)
    EventType (populated with LongStringID values)
    LongStringID (populated with EventDate value)

    DestTableName_ImportErrors
    Errror = Type Conversion Failure
    Field = EventDate

    Any idea why the columns are being auto-ordered? How do I prevent it? This also isn't happening with all of my scripts and I cannot tell the difference between them. (They all look the same as above only with variation in the number of columns.)

    Thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    have you tried importing csv into xl (using import text), then import the xl to Access?

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    There are more than a million records per file. (I just checked one file and it has 1,629,446. Excel's limit is 1,048,576.)
    I don't fully understand the XML syntax. e.g. Why does it require <Column Name="Col1" and FieldName="DescriptiveName" ... />.
    If I could force a sort, I could then ensure that it sorts on Name instead of FieldName.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 09-05-2017, 10:35 AM
  2. order multiple columns in list box
    By Dachbo in forum Forms
    Replies: 2
    Last Post: 07-13-2015, 06:45 PM
  3. Dynamic Columns - Import new columns?
    By memaxt in forum Import/Export Data
    Replies: 2
    Last Post: 06-24-2014, 06:02 AM
  4. How to order columns in ascending order?
    By darkingthereturn in forum Access
    Replies: 5
    Last Post: 06-18-2012, 05:24 AM
  5. Columns change order
    By copaaccess in forum Queries
    Replies: 1
    Last Post: 02-28-2012, 08:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums