none
Why can't I get importexportspecification to import fields in the order I want? RRS feed

  • Question

  • I'm struggling with creating an importexportspecification that works the way I want.  Here's a portion of my XML for importing a CSV file:

    <?xml version="1.0" encoding="utf-8" ?>
    <ImportExportSpecification Path = "C:\Users\JPower\Documents\_Follow up two\West Coast NCA_NCCA WQ 1999-2010.csv" xmlns="urn:www.microsoft.com/office/access/imexspec">
         <ImportText TextFormat="Delimited" FirstRowHasNames = "true" FieldDelimiter="," TextDelimiter="" CodePage=  "437" Destination = "West Coast NCA_NCCA WQ 1999-2010_csv" > 
    <DateFormat DateOrder="DMY" DateDelimiter="/" TimeDelimiter=":" FourYearDates="false" DatesLeadingZeros="false" />
    <NumberFormat DecimalSymbol="." />
    <Columns PrimaryKey="" >
                <Column Name="Col1" FieldName="Site_ID" Indexed="NO" SkipColumn="false"  DataType="Memo"/>
                <Column Name="Col2" FieldName="SITE_ID orig" Indexed="NO" SkipColumn="false"  DataType="Memo"/>

    more column specifications  ........

                <Column Name="Col42" FieldName="QA NO3" Indexed="NO" SkipColumn="false"  DataType="Memo"/>
            </Columns>
        </ImportText>
    </ImportExportSpecification>

    When I execute the specification the columns/fields are created (and read) in alphabetical order, and not in the order in which they appear in XML.  I want my fields to be ordered as Site_id, Site_ID orig....QA NO3, as they appear in the XML.  When I execute the above XML, Access reads the first column in the CSV file (containing Site_ID values) into a field named "Chl a", which happens to come first in an alphabetical listing of the fields.  The second column in the CSV file, holding Site_ID Orig, is read into a field named CHLA QACODE, which is next in the alphabetically ordered listing of the fields, and so forth.

    Any idea of what wrong with my XML?

    Thanks in advance

    Wednesday, October 18, 2017 11:20 PM

Answers

  • Hello,

    Glad that you found the problem. I suggest you mark it as answer to close this thread.

    Besides, I could reproduce the issue using Access 2010/2013/2016. If we do not specify the width, when importing with "Delimited", the field would be in the alphabetically ordered. However it doesn't matter what value it is because the width value would not affect column width of Access table. We could use any value but the element should be existing, then it would show excepted result. I don't find any document to explain why it happens and how Access calculates the width when importing. You may submit a feedback about the issue.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 8:40 AM
    Moderator

All replies

  • Hello,

    I suggest you manually import the file and save the import/export specification at the same time

    Then you may use the following code to get its xml.

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = objFSO.CreateTextFile("D:\test.XML")
    oFile.WriteLine CurrentProject.ImportExportSpecifications(0).XML ' or ImportExportSpecifications("Name").XML
    oFile.Close

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 19, 2017 7:58 AM
    Moderator
  • Thank you Celeste.  I followed your suggestion and found two differences between my XML and the XML that is generated by doing a "manual" import.  The first is that the XML generated by the manual import specified code page 65001 (UTF-8) and I now understand that's the better one to specify.  That change didn't solve my problem, though.

    The second difference is that I was not specifying the Width="xx" attribute in my individual

    <Column ........... />

    tags.  I don't understand how the width value is determined when doing a manual import.  I create my XML to read every column as Memo (Long Text), and I can't see any correspondence between the Width values that are generated by the manual import and the widths of the information (headers and data) that is in each of the import file's columns.

    Nonetheless, including an arbitrary Width="20" in all of my XML tags resulted in the fields being imported in the (correct) order that the appear in the CSV file.  So, I'm up and running, but I would like to find out how the Width= value is being determined during a manual import.  Setting things to arbitrary values makes me uncomfortable.

    Thanks again

    Thursday, October 19, 2017 3:39 PM
  • Hello,

    Glad that you found the problem. I suggest you mark it as answer to close this thread.

    Besides, I could reproduce the issue using Access 2010/2013/2016. If we do not specify the width, when importing with "Delimited", the field would be in the alphabetically ordered. However it doesn't matter what value it is because the width value would not affect column width of Access table. We could use any value but the element should be existing, then it would show excepted result. I don't find any document to explain why it happens and how Access calculates the width when importing. You may submit a feedback about the issue.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 8:40 AM
    Moderator
  • Thanks again Celeste.  At least I'm not crazy!

    Jim

    Friday, October 20, 2017 4:04 PM