Results 1 to 8 of 8
  1. #1
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74

    Problem during the importing of CSV File if it includes inch character


    Good afternoon everyone, i'm using the TransferDatabase function to import a CSV file into one of my table database and when the customer includes " (inch) character in one of the field who i'm importing this transform the imported string. Here the example and what i use to import :

    Original String from CSV file : 6" LINE FM FW-427 TO P47278 TEST HEADER
    How the string is show in my table : "6"" LINE FM FW-427 TO P47278 TEST HEADER"
    I use: [DoCmd.TransferText acImportDelim, "04Class Template1 Import Specification", "loadfile_FLOC_Class", Me.my_loadfile, False]

    Thank you for kindly support.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont use
    TransferDatabase for text, use
    TransferText for CSV files.
    then in the SPEC sheet, tell it not to use " delimeters. (hopefully you have commas)

  3. #3
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Quote Originally Posted by ranman256 View Post
    dont use
    TransferDatabase for text, use
    TransferText for CSV files.
    then in the SPEC sheet, tell it not to use " delimeters. (hopefully you have commas)
    Thank you for the answer and my apologies, i effectively use TransferText as follow :
    [DoCmd.TransferText acImportDelim, "FlocImportSpecification", "loadfile_FLOC", Me.my_loadfile, True]
    and in the "FlocImportSpecification" sheet i'm using "commas" delimeter however when i import it i have that result anyway.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In the specification you have a field delimiter (should be comma) and a text delimiter (which usually defaults to "), try to remove it, save the spec and try again. If still doesn't work maybe you can run an update query to remove the extra " after the import.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Quote Originally Posted by Gicu View Post
    In the specification you have a field delimiter (should be comma) and a text delimiter (which usually defaults to "), try to remove it, save the spec and try again. If still doesn't work maybe you can run an update query to remove the extra " after the import.

    Cheers,
    Thank you for the answer. Yes the text delimiter is set to (NONE) however the string coming as i showed. I believe the only solution is as you suggested, one imported to run a query to remove the extra ".

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what about the text qualifier? usually when your csv contains " it would be '.

    not clear from your example what your csv file actually contains and the outcome - does it really include " at start and end? You need to open the csv file in notepad, not excel. If opened in excel and subsequently saved, that can mess with the formatting

  7. #7
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    My CSV file is a template with around 15 columns (to be more precise is a list of Industrial Equipment with some characteristics) and i need a CSV file because, after my Quality verification in my database, the same file should be exported in SAP (which is the company system for the maintenance).
    So, as per my example, sometimes, the contractor use " for indicate the size of a valve or pipe and when i import the CSV file in my table the result is that the " (inch) are added at the start of the string, at the end and in the middle. Please see below my example. I check the Spec, there is not Text Delimiter. Thank you

    Original String from CSV file : 6" LINE FM FW-427 TO P47278 TEST HEADER
    How the string is show in my table : "6"" LINE FM FW-427 TO P47278 TEST HEADER"
    I use: [DoCmd.TransferText acImportDelim, "04Class Template1 Import Specification", "loadfile_FLOC_Class", Me.my_loadfile, False]

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not Text Delimiter.
    I was referring the qualifier, not delimiter. And from your repeated example, it is adding " to beginning and end of the string as well as the existing "

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

Similar Threads

  1. Problem importing data from a .csv file
    By RickK in forum Access
    Replies: 4
    Last Post: 09-22-2016, 08:58 AM
  2. problem with numbers when importing txt file into Access
    By Atoga in forum Import/Export Data
    Replies: 7
    Last Post: 04-27-2015, 06:02 PM
  3. Replies: 5
    Last Post: 11-10-2014, 03:19 PM
  4. Problem with code which includes many IF
    By gstylianou in forum Access
    Replies: 5
    Last Post: 10-25-2014, 11:29 AM
  5. Replies: 9
    Last Post: 08-19-2013, 01:08 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