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