Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9

    Unanswered: Import LARGE .csv into Excel to import into .mdb

    OK .. Im a database noob.

    I've had a web site that users search using a .mdb file converted from a .csv into a .xls then imported into a .mdb.

    The file has now reached a point where it's too big to open in excel. Needless to say, the searching I have done has not proved successful.

    That being said, I need to be able to open this .csv file into excel, save it as a .xls and then import it into my .mdb.

    Any suggestions? or is there a better way of doing this?

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    If you are using Excel 97 - 2003 you can import 65536 rows into a single spreadsheet. If you more reocrds more than this you could save your CSV into 2 files with half the data in each or divide into more files if it is bigger than 131072 records. Excel 2007 can import over 1 million rows.

    Your easiest approach to this may be to import the CSV using MS Access and save your MDB with Access. Where you don't have the rows limit.

    If you don't have MS Access, that may be why you want to use Excel to convert the data and save as an MDB, it may be worth the expense to abtain a copy of MS Access if your doing this frequently.

    An even better option may be to use MySQL or SQL Light as the Back End database for your web site. This will require some programming changes in your site design but I think it would improve your site performance and eliminate the need to convert the data from CSV. Most hosting services offer MySQL database access with thier accounts. There are many scripts configured to read and write to CSV Files which can be access and parsed quickly using a PHP or Pearl script.
    ~

    Bill

  3. #3
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9
    Thanks ...

    I have both excel and access 2002/2007. Access wont let me import the csv file.. gives me some error about "{filename} does not contain data"

    This is corrected (previously) when I opened the .csv in excel and saved as .xls (dont ask me why, but it worked).

    Anyhow .. I've seen comments about opening the .csv into 2 files.. Can you explain how to accomplish this? What I read involved a vb code ... and unless its spelled out for me ... it's outta my league.

    PS .. its my web server and I'd love to explore MySQL ..but just havent been able to find real guidance. Have a look .. www.npi-search.com
    Steve

  4. #4
    Join Date
    Apr 2008
    Posts
    6
    This can be done with VBA but I don't have time to explain it to someone who needs VBA spelled out as you put it. My best advice is to go and buy a book called "VBA and Macros for Microsoft Excel" by Bill Jelen and Tracy Syrstad chapter 18 covers exactly what you want to do and if you ever do pivottables then this book has some great stuff on it.

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    If you search on your error message "Access file does not contain data" you'll find some posts with people having problems with large files. 2 GB file size seems to cause problems with importing or linking CSVs in Access. You may experiment by cutting the file in 2 parts or smaller files to test if your near 2 GB.

    I'm not sure why your needing to take the MDB file converted to a CSV then open it with Excel and finally import to Access? Have you tried using FTP to download your MDB file from the Server? Then you could open that with Access directly and skip the round trip.

    here's a post about the Large file size problem
    http://www.accessmonster.com/Uwe/For...le-into-Access

    I would try to do this without code unless you want to automate it or are doing this very often.

    Here's a basic example of using code to import a text file to give you an idea how to put the text into multiple sheets. You'd have to do additional processing to to parse out the CSV values to columns. Change the number after ".CopyFromRecordset" to a smaller number to see how this works, if you have fewer than 65536 line text file when you test it.
    Code:
    Sub ImportLargeFile()
    'Imports text file into Excel workbook using ADO.
    'If the number of records exceeds 65536 then it splits it over more than one sheet.
    
    Dim strFilePath As String, strFilename As String, vFullPath As Variant
    Dim lngCounter As Long
    Dim oConn As Object, oRS As Object, oFSObj As Object
    
    
    'Get a text file name
    vFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
    
    If vFullPath = False Then Exit Sub 'User pressed Cancel on the open file dialog
    'Application.ScreenUpdating = False
    
    
    'This gives us a full path name e.g. C:\folder\file.txt
    'We need to split this into path and file name
    Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
    
    strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path
    strFilename = oFSObj.GetFile(vFullPath).Name
    
    
    'Open an ADO connection to the folder specified
    Set oConn = CreateObject("ADODB.CONNECTION")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strFilePath & ";" & _
    "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
    
    Set oRS = CreateObject("ADODB.RECORDSET")
    
    'Now actually open the text file and import into Excel
    oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1
    
    While Not oRS.EOF
    Sheets.Add
    ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
    Wend
    
    oRS.Close
    oConn.Close
    
    
    End Sub
    ~

    Bill

  6. #6
    Join Date
    Jun 2009
    Posts
    1

    Large CSV to multiple worksheets in Excel workbook

    Hi Bill

    Do you think you could elaborate on the code you provided.
    I can see how additional sheets are added.
    How woud you parse out the CSV values to columns for each row up to 65536


    Many thanks
    Shaun

  7. #7
    Join Date
    Feb 2004
    Posts
    533
    I would run it and see how the data comes over. It may be parsed. If not use Text to columns or loop through each row with a macro and parse into cells. I don't have an example handy just right now.
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •