Results 1 to 10 of 10

Error during csv file import

  1. #1
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    11

    Error during csv file import

    I am getting an error message when I attempt to import a CSV file into my db. Since I don't know how to type a portion of it that contains special characters, I've attached a screenshot. I've checked my spreadsheet and my table and they both appear to be the same, so I'm unsure why it's trying to rename the field in question. Here is how my code currently looks:

    Private Sub cmdImportMac_Click()


    DoCmd.TransferText acImportDelim, , "Macess_UL", "\\\Application_Reports\MacessProd.csv", True
    End Sub
    Attached Thumbnails Attached Thumbnails access-error.png  

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,734
    The "True" argument in your function tells Access that the first row of your data file contains field names, which must match the field names in your "Maccess_UL" table.
    I believe that error message is telling you that it is finding a field name which doesn't match.
    Based on the strange characters you are messing in that message, I am guessing that your data file has some special characters in it that is messing things up.
    Is "APP_USERNAME" the first field in your file? I have often seen extra special characters at the very beginning of files. Sometimes editing the data file and removing those special characters fixes it.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    11
    From what I can tell, they are exactly the same

    In Excel:
    Click image for larger version. 

Name:	access-error2.png 
Views:	12 
Size:	1.9 KB 
ID:	38408

    In Access:
    Click image for larger version. 

Name:	access-error3.png 
Views:	13 
Size:	3.5 KB 
ID:	38409

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,734
    Open the CSV file in a Text Editor (not Excel), and take a look at what the first character is.
    Sometimes, there are hidden characters, like non-breaking spaces or carriage returns that cause issues.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    11
    Still looks completely fine. Is there a better way to import data from a csv into a table than what I am using? I'm very novice to all this and trying to learn how to best accomplish these tasks.

    Click image for larger version. 

Name:	access-error4.png 
Views:	13 
Size:	3.5 KB 
ID:	38410

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,734
    No, that is a good way to do it.
    What happens if you try to manually import it (i.e. right-click on your Table, select Import Data, and go through the Import Wizard)?
    When it shows the data, can you see any strange characters at the beginning of the first row?

    Also, check you table to make sure that there isn't any spaces or anything at the beginning of that field name.

    By the way, how exactly are you creating the CSV file from the Excel file?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    11
    When I try to go through the standard Import Wizard, I am not given an option to select a CSV file, only XLS-type files.

    I verified there are no spaces in my field name as well.

    The CSV file is created through a SQL script that auto-creates the file based on application data.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    the characters at the beginning of your file are hidden characters. I've run in to this before.

    https://social.msdn.microsoft.com/Fo...ortingservices

    See this link for the description of what it is.

    https://www.sqlservercentral.com/for...porting-report

    see this link to 'fix' the problem (I haven't done it myself) it's in your SQL config file

    EDIT: you can also choose to rewrite your file prior to the time you import it, or export as a text file from SQL instead of as a csv.

  9. #9
    pooldead is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    11
    Thanks rpeare, I'll get with my SQL guys and have the config file updated.

    Also, thank you for all your help JoeM.

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,734
    Thanks for the assist rpeare. I was pretty sure it was some hidden characters in there somwehere. I have seen that before too, especially on text files created in something other than Excel or Access.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  2. Link File Import Error
    By Bdoe069 in forum Import/Export Data
    Replies: 1
    Last Post: 03-28-2016, 01:54 PM
  3. Link to text file creates #Num! error on import
    By GregTheSquarePeg in forum Import/Export Data
    Replies: 8
    Last Post: 10-11-2013, 04:32 AM
  4. Import Text File Error with quotes being stripped
    By examart in forum Import/Export Data
    Replies: 2
    Last Post: 10-08-2012, 08:11 AM
  5. Replies: 0
    Last Post: 12-08-2011, 09:12 AM

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
  •  
Tech Forums: Microsoft Office Forums