Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    626

    Import CSV File Into Access


    I am attempting to import a csv file with over 50,000 rows of data in it. This file contains a zip code field and sometimes the zip code fields holds a - in it. For all those rows the zip code fails to import. I have tried to set the field to short text, long text and memo and it continues to fail.

    I need to handle this through access vba as I am unable to manually manipulate the .csv file

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    Please post some sample data and give an example of what you need for output.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,279
    save the csv file to the same place everytime: c:\temp\ImportCsv.csv
    link it internally as an external table.
    then you can run an append query that only imports the valid zip codes in the query.
    and another query to handle the 'bad' zip codes.

  4. #4
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    626
    Quote Originally Posted by orange View Post
    Please post some sample data and give an example of what you need for output.
    This is the code I am using

    Code:
    Function ImportData()
        Dim excelapp As Object
        Dim wb As Object
        Dim importFile As String
        Dim importPath As FileDialog
        Dim rowNum As Long
        
        Set importPath = Application.FileDialog(msoFileDialogFilePicker)
    
    
        With importPath
            .AllowMultiSelect = False
            .Title = "Select the import file"
            .Filters.Add "All Files", "*.*"
            If .Show = -1 Then
                importFile = .SelectedItems(1)
                
                'Capture the range to import
                Set excelapp = CreateObject("excel.application")
                Set wb = excelapp.Workbooks.Open(importFile)
                rowNum = excelapp.Application.CountA(wb.worksheets(1).Range("A1:a1048576"))
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Test", importFile, True, "A1:AP" & rowNum
                
                wb.Close
                Set wb = Nothing
                excelapp.Quit
                Set excelapp = Nothing
            Else
                MsgBox "You did not select a spreadsheet to import"
            End If
        End With
    End Function
    And this is a sample spreadsheet http://www.filedropper.com/book1_4

    I'm on a MacBook and it won't let me upload my csv or convert to a .zip for "security" on this work computer.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    The zip codes with "-" are known as zip+4.
    From wikipedia
    A ZIP+4 Code uses the basic five-digit code plus four additional digits to identify a geographic segment within the five-digit delivery area, such as a city block, a group of apartments, an individual high-volume receiver of mail, a post office box, or any other unit that could use an extra identifier to aid in .......

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    626
    Yes - but how can I import them into an access table w/o access throwing an error and not importing?

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    I downloaded your csv file; used access to import the data; I did set your "joined" to short text; then imported to access without errors.

    You seem to be doing this using Excel??

    ID Joined UserID Firstname Lastname Address City Region PostalCode
    1 11/28/2015 1111 A JONES A Miami FL 33170
    2 11/28/2015 2222 B K B Homstead FL 33032-1234
    3 12/11/2015 333 C Lord C Homestead FL 33032
    4 12/22/2015 44444 D M D WARNER ROBINS GA 31095
    5 3/9/2016 555555 E N E Hollywood FL 33020
    6 3/10/2016 66666 F O F Coral Gables FL 33134
    7 3/14/2016 7777 G P G Fort Pierce FL 34946
    8 3/31/2016 8888 H Q H Sunrise FL 33313
    9 4/13/2016 9999 I R E Miami Springs FL 33266-9998

  8. #8
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,268
    you can use sql to import from a text file

    the basic query would be

    SELECT * FROM [TEXT;DATABASE=C:\FilePath;HDR=Yes].FileName.csv

    which you can use to view the data

    go back to the sql window and surround with brackets and alias

    SELECT * FROM (SELECT * FROM [TEXT;DATABASE=C:\FilePath;HDR=Yes].FileName.csv) txt

    which you can then turn into an aggregate query in the QBE to count the number of records, or apply criteria - or change to a make table or insert query

    you can also join it to other tables - perhaps with a left join so you only import new records or to add FK's for relationships or even modify the data (remove unwanted characters, format as proper case, etc)

    you can build the sql string in vba so you can populate filepath and filename values from your filedialog

  9. #9
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    626
    Quote Originally Posted by orange View Post
    I downloaded your csv file; used access to import the data; I did set your "joined" to short text; then imported to access without errors.

    You seem to be doing this using Excel??

    ID Joined UserID Firstname Lastname Address City Region PostalCode
    1 11/28/2015 1111 A JONES A Miami FL 33170
    2 11/28/2015 2222 B K B Homstead FL 33032-1234
    3 12/11/2015 333 C Lord C Homestead FL 33032
    4 12/22/2015 44444 D M D WARNER ROBINS GA 31095
    5 3/9/2016 555555 E N E Hollywood FL 33020
    6 3/10/2016 66666 F O F Coral Gables FL 33134
    7 3/14/2016 7777 G P G Fort Pierce FL 34946
    8 3/31/2016 8888 H Q H Sunrise FL 33313
    9 4/13/2016 9999 I R E Miami Springs FL 33266-9998
    im using access 2016 and it gives me the error. Anything with the hyphen in the zip error.

    what vba syntax are you using to import the file successfully?

  10. #10
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,268
    I think Orange is referring to the code you provided where you have

    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Test", importFile, True, "A1:AP" & rowNum
    if you are importing text you can either use the method I suggested or you would use the transfertext function

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    I used Access 2016 (part of O365) did the External Data; New Data Source; From File; From TextFile and followed the Import wizard in Access. Identified the downloaded csv and put it into a table "Book1".

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,803
    Here is an example of what Ajax was referring to.
    First, I manually stepped through importing the CSV file, saving an Import Specification (which is used in the transfertext command).
    Then I modified the code....
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    Steve,
    You saved your Spec via the advanced option or does your version still use the MSysIMEXSpecs/Columns?

    I have O365/AC2016 used the External Data wizard and saved the spec into the "mysterious XML cave".
    I just now, after seeing your approach with the spec name, redid the wizard and by using the avanced button I can save the spec into MSysIMEXSpecs/Columns and use the transfertext method.
    I hadn't appreciated that you could still save the spec into those tables.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,803
    Yes, I did the import manually and clicked on the ADVANCED button to to give the import spec a name, then finished the manual import.
    Knowing the import spec name allowed me to use it in the TransferText command.
    I never could save an import spec, then find out where Access stored it.


    But now I am going to look into the MSysIMEXSpecs/Columns table - it was always hard to change the import spec. Had to change the spec, then had to save it with a new name and edit the VBA code.


    Its easy to do things that can't be done when no one tells you "You can't do that".
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    Steve,
    Yes I figured that's what you did. I saw a reference to that when searching for a way to get to the new method for saving specs via XML somewhere in the bowels of Access.
    I used to change the specs in the MsysIMEX tables back when I was working and was trying to automate a number of imports.
    You could read in the tables and adjust/rename etc programmatically.. But the new way of saving the spec has changed that. For the longest time, I thought the specs were just not being saved since they weren't in the MSysImex tables.
    Getting too old it seems.

    Looks like the OP was here also.

    I'm not an Excel person, but wouldn't be surprised if the "-" was attempting to do some arithmetic.
    Good luck.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 34
    Last Post: 08-09-2017, 09:57 AM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. Replies: 3
    Last Post: 10-08-2014, 11:05 AM
  4. Import EDI file to Access
    By rhaner in forum Import/Export Data
    Replies: 4
    Last Post: 11-30-2012, 01:18 PM
  5. Import KML File Into Access
    By JonMulder in forum Import/Export Data
    Replies: 2
    Last Post: 05-18-2012, 11:06 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 - Senior Forums