Results 1 to 7 of 7
  1. #1
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328

    Import csv file - Column order limitations

    I am using A2007 with W7. I import a csv file that is downloaded into a table from a website. No problem until recently when they changed the order of columns. This caused data to be imported into the wrong fields. Example, zip code ended in the Last Name field. Apparently, the order of column names matters and gets printed in stone somehow.

    I tried deleting the existing table before running the import code so that the old table wouldn't influence the procedure, but still happens. Here is basic code that I used.



    DoCmd.DeleteObject acTable, "tableName"

    DoCmd.TransferText acImportDelim, "Spec Name", "tableName", myPath & csvfilName, True, "" '

    Is there a way to not be dependent on column order or is there something wrong with my Access? Any help much appreciated. I really need a way not to be dependent on column order. Driving me nuts.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The only other way I know to import CSV is to open the file as an object in VBA and read each line of the text file, parse the string, and save each value.

    So even though the table is deleted, Access retains something that defines that table because the db has not been Compacted & Repaired? Weird and unfortunate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If the data structure within the CSV changes you will need to create a new Import procedure to replace "Spec Name". Your old procedure, "Spec Name", is not aware of the data structure change, nor does it care. It sees the file as text and follows the instructions it was given at the time "Spec Name" was created.

    As for analyzing a CSV, what is the point? It is formatted as text. If the column order can change then I would not depend on the column header name always being consistent.

    As June mentioned, read it line by line and write it to a table. The only thing I can imagine to add would be to maybe use code to copy the CSV and paste it as XLSX. Then interact with the new file as an Excel object.

  4. #4
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    It is hard for me to understand that Access is just "looking" at relative locations instead of column head names. When using the import wizard, you certainly treat each field in the column head as if it were a field in a table-it is text or long or date or etc.. Yet, when imported, it is just a location. I think what June and ItsMe are telling me is that the import spec is excel-like in that it feeds on location, not field name.

    In this case, for some reason, the column head order can change even though the names stay the same. I will look into converting it to excel.

    Again-thank you both-if it weren't for this site and folks like you, I would have to give it up.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    The wizard asking for data types is for the table properties you will be importing into. The table properties will truncate data or throw an error if the text in the text file, yes text file, does not fit the data type of the field in the table.

    The docmd you are using specifies the CSV is a text file and that is standard for CSV.

  6. #6
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    I have played with this more. The code below links to a CSV file and does not "care" what the column order is. The column heads in the linked CSV file are treated just like field names in an Access table.

    DoCmd.TransferText TransferType:=acLinkDelim, TableName:="registrants", _
    FileName:=myPath & "Registrants.csv", HasFieldNames:=True

    For some reason, if I change acLinkDelim to AcImportDelim, won't work with the HasFieldNames

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    That is good info. I usually steer clear from linking. So I was unaware that doing so would focus on the Field Names. You could link to the file and run an append query to your permanent table.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-31-2013, 08:46 PM
  2. Import Excel file based on a date and time in file name
    By nhylan in forum Import/Export Data
    Replies: 4
    Last Post: 04-16-2013, 03:26 PM
  3. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  4. Column Order, Hidden, Name
    By Eightball3 in forum Forms
    Replies: 1
    Last Post: 06-30-2010, 12:35 PM
  5. Replies: 6
    Last Post: 05-11-2010, 02:22 PM

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