Results 1 to 6 of 6
  1. #1
    johnson8809 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    11

    Help with interesting VBA Code puzzle

    I have a comma delimited text qualifying csv file that I'm trying to
    import. One of the fields sometimes has a comma or quoted values within
    the field. I have requested that the source change from comma delimited to
    pipe delimited and was told no. I'm trying to find a way to strip the
    extra commas within the field to import the file using access VBA. Need
    assistance. A time sensitive matter so I am at my most desperate.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you cant change the delimiter and you are stuck with commas, then there's nothing to be done. Its a shame you cant get people to work with you.

    IF the commas are INSIDE the quotes, then you are fine. The import should ignore commas inside quotes BUT...
    if you have commas and NO quotes, then your data wont parse correctly and import wrong. There's no way for you to know where the break goes, except manually fix it.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have requested that the source change from comma delimited to
    pipe delimited and was told no
    why not? Or use tab, Another alternative is to request they use text delimiters - if they are IT people, they should understand the issue. Alternatively ask if they can change single commas in text to double comma's so you can identify them and take action. Or change single commas to spaces

    But if not, you need a way to identify a comma separator from a comma within some text. You can only do this by inspecting the data and coming up with a bespoke solution. So if you want help, you'll need to provide some example data, including headers, fieldtypes etc - with no guarantees of success

    To get you thinking, for example if a separator comma is always followed by a space and an invalid comma is not, you can replace ", " with "| " yourself

    Another option may be to do with a character count of some sort, or identifying the field type before and after the rogue field

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by johnson8809 View Post
    ...text qualifying csv...
    If it is Text Qualifying, the commas should not matter. Have you tried opening the CSV with a text editor to see what text is being used to qualify the delimiter?

    The wizard should help you to import a CSV as a text file. Understand what the text qualifier is and use the advanced options to specify/verify the text qualifier. Its been a while but I believe the whole thing can be managed using the Wizard. If you save the Import process you can then call it using VBA or maybe a Macro too.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Triplicate threads merged. Please don't post the same question more than once.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you post a sample 'good' line and a sample 'bad' line. Depending on the format you may be able to deduce where the break should be.

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

Similar Threads

  1. Replies: 9
    Last Post: 03-26-2015, 03:51 PM
  2. Replies: 4
    Last Post: 10-08-2013, 09:49 PM
  3. Iff Puzzle VBA and DLookup
    By Ruegen in forum Forms
    Replies: 5
    Last Post: 08-20-2013, 12:25 AM
  4. VB Strategy Games - Circle Puzzle and Others
    By pkstormy in forum Code Repository
    Replies: 4
    Last Post: 03-05-2011, 03:18 AM
  5. Unique Puzzle
    By Christina G in forum Database Design
    Replies: 0
    Last Post: 03-14-2009, 06:37 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