Results 1 to 8 of 8
  1. #1
    jasonvanwyk is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    2

    CSV into array

    Hi Forum


    This is my first post here.
    May someone please point me to the correct thread or help me search for the correct thread.
    I did try the search function, but it appears that csv is too short a phrase to search properly.

    I'm trying to use VBA to import a csv file into my access program and then put it into an array so that i can iterate through it and pick only the csv values that i want to populate my fields in my table.

    Many thanks in advance.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try searching for comma separated variables....

    I would start by using the import wizard to link or import your csv file.
    Then its easy to run suitable append queries to extract exactly what you want
    As it's a text file, all fields will also be text so you may need to first adapt some fields using CInt, CDate, CDbl etc

    Once you've done this successfully, you should be able to convert the process so its all done via a VBA procedure
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    jasonvanwyk is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    2
    Super! - Thanks Ridders52
    time to get cracking

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No time like the present! Good luck
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did a test. Import wizard recognized number and date/time values as appropriate data type in both Link and Import operations.
    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.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    Did a test. Import wizard recognized number and date/time values as appropriate data type in both Link and Import operations.
    It will often correctly identify the datatype but from experience that can't be guaranteed 100% of the time.
    As I understand it Access checks the first few rows in the import to determine the datatype.
    If any of those records have empty fields it may not work reliably.
    Similarly if the first few rows of a text field are actually numbers
    For that reason, I prefer to specify using CLng, CDate etc
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Agreed, and some fields containing numbers might really should be text type (ssn, phone, zip, serial). The wizard allows user to define the data type of each field and save an import/export specification. Although, never having used, can't vouch for reliability.
    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.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by June7 View Post
    <snip>The wizard allows user to define the data type of each field and save an import/export specification. Although, never having used, can't vouch for reliability.
    It was a bit of work setting up the import specification, but once working correctly, I have never had problems using the import spec, using VBA to import the CSV file (to a table).

    Of course, if the CSV file has garbage data, there will be problems. If you are trying to import a double that has formatting (as in $5,000.50) or date format is wrong, well then, boom! Other than things like that, we imported a minimum of 2 CSV files a month for over 5 years with no problems.

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

Similar Threads

  1. Global Array
    By kdbailey in forum Access
    Replies: 4
    Last Post: 05-19-2015, 03:04 PM
  2. Need help with array
    By programmingbeginner in forum Programming
    Replies: 14
    Last Post: 08-28-2014, 01:30 PM
  3. Would be an ARRAY statement in SAS.
    By arbnmedic in forum Access
    Replies: 2
    Last Post: 01-07-2012, 03:34 PM
  4. Array will not ReDim...
    By yeah in forum Programming
    Replies: 1
    Last Post: 10-08-2011, 06:30 PM
  5. How to use array? [ solved] Thanks.
    By wasim_sono in forum Programming
    Replies: 0
    Last Post: 10-20-2006, 12:00 AM

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