How to import large mdb file

[Arthur Lin Ku]

Hi,

I have to import large .mdb files and transfer into stata file. As the databases are saved in 32 byte, I followed instruction from previous discussions and advises online that adding them into user data source through ODBC data source administration and run the data by stata 15 IC 32 bytes.

 odbc load, table("20110106")
op. sys. refuses to provide memory
    Stata's data-storage memory manager has already allocated 1088m bytes and it just attempted to
    allocate another 16m bytes.  The operating system said no.  Perhaps you are running another
    memory-consuming task and the command will work later when the task completes.  Perhaps you
    are on a multiuser system that is especially busy and the command will work later when
    activity quiets down.  Perhaps a system administrator has put a limit on what you can
    allocate; see help memory.  Or perhaps that's all the memory your computer can allocate to
    Stata.

Although some files works, there are still other files that are too large that exceed 2GB, and therefore stata 32 byte cannot read them. Even if I tried to set memory larger, still cannot read the table.

I also try free version StatTransfer, but seems not work as well. I also attempted to separated the tables in mdb files into smaller tables, but many times it just shows "Cannot open database. It may not be the database that your application recognizes, or the file may be corrupt.", despite of the fact that the database is open in front of me.

Is there anything else I can do?

[Joseph Coveney]

As the databases are saved in 32 byte

I don't understand what you mean by this.

I've just created an old pre-Windows 95 Access Version 2 .mdb file of the Auto dataset (using Stat/Transfer) and read it via 64-bit ODBC drivers into 64-bit Stata. So that's the route that I would try. Get rid of the 32-bit ODBC DSNs, install 64-bit Stata and work from there via the 64-bit ODBC driver.

I'm not sure what the limit is for Stata IC, so you might hit a ceiling there. You might need to read them in piecewise, compress and append the pieces, or read in a subset of the variables. Or upgrade.

Edited to add: also, make sure that you have enough memory installed. From the error message, that might be the prime suspect.

[Sergiy Radyakin]

Arthur, what specifically didn't work with the StatTransfer? Thank you. Sergiy.

[Arthur Lin Ku]

I don't understand what you mean by this.

I've just created an old pre-Windows 95 Access Version 2 .mdb file of the Auto dataset (using Stat/Transfer) and read it via 64-bit ODBC drivers into 64-bit Stata. So that's the route that I would try. Get rid of the 32-bit ODBC DSNs, install 64-bit Stata and work from there via the 64-bit ODBC driver.

I'm not sure what the limit is for Stata IC, so you might hit a ceiling there. You might need to read them in piecewise, compress and append the pieces, or read in a subset of the variables. Or upgrade.

Edited to add: also, make sure that you have enough memory installed. From the error message, that might be the prime suspect.

Hi, thank you for the try and reply. In the first attempt I tried to use odbc commands to link the database with stata, and therefore only 32-byte stata could read it. I attach the differences
In Stata 32

  . odbc list  Data Source Name                   Driver ------------------------------------------------------------------------------- 2010-01-11                         Microsoft Access Driver (*.mdb) 2010-09                            Microsoft Access Driver (*.mdb) 2009-12                            Microsoft Access Driver (*.mdb) 2010-12                            Microsoft Access Driver (*.mdb) staccr_32                          Microsoft Access Driver (*.mdb) 2011-01-06                         Microsoft Access Driver (*.mdb) -------------------------------------------------------------------------------

In Stata 64

  odbc list  Data Source Name                   Driver ------------------------------------------------------------------------------- -------------------------------------------------------------------------------

The problem I encounter for StatTransfer was I am not sure whether it really works. I have been waiting for hours but still the program is still running. As this is my first time using it, I am not quite sure it is normal. However, when using Stata odbc command and so long stata can link to the data, it process far more faster than the time that StatTransfer.

Even if StatTransfer really works, I still have to find a way to set a queue for StatTransfer, for I have more than 20 files with similar size waiting to be transfer. The software seems not providing means to give a sequence of works.

Thank you all.

[Joseph Coveney]

You don't have any DSNs set up for the 64-bit ODBC drivers. You need to go into the Windows settings or control panel and set them up, or else use a DSN-less connection from within Stata (put the information in a connection string).

What you describe is not normal behavior of Stat/Transfer. In my experience, it goes lickety split even for large files. Also, you can use wildcards in an .STCMD file (and maybe from the operating system prompt, too) and so there's no need to set up a queue for Stat/Transfer at all.

Have you checked on your machine's memory?

[Arthur Lin Ku]

You don't have any DSNs set up for the 64-bit ODBC drivers. You need to go into the Windows settings or control panel and set them up, or else use a DSN-less connection from within Stata (put the information in a connection string).

What you describe is not normal behavior of Stat/Transfer. In my experience, it goes lickety split even for large files. Also, you can use wildcards in an .STCMD file (and maybe from the operating system prompt, too) and so there's no need to set up a queue for Stat/Transfer at all.

Have you checked on your machine's memory?

Hi, thanks for reply. I checked my laptop and it has 8 gb of memory. Although not very large, but it seems enough larger than any single file I have to transfer (the largest is 2.1 gb)

Would you mind to describe more details on setting DSN in windows settings? I only find ODBC data source on my machine. Probably find it wrong. For the DSN-less connection within Stata, what are the commands that I should search for?

[Joseph Coveney]

Would you mind to describe more details on setting DSN in windows settings?

In Windows 10, open Settings from the little gear icon and type "odbc" in its search feature. Choose "Set up ODBC data sources (64-bit)". On the GUI that pops up, go to the "System DSN" tab and click "Add". Choose "Microsoft Access Driver" and take it from there.

For the DSN-less connection within Stata, what are the commands that I should search for?

Use the connectionstring() option for Stata's odbc load command. Instructions are in Stata's online help for the command. For what text to put in the option, copy the text from here, changng the directory and file name to yours.

Keep in mind that, based on the behavior you describe with the dedicated application* and with Stat/Transfer, it seems as if at least one of your files has been corrupted.

* "I also attempted to separated the tables in mdb files into smaller tables, but many times it just shows "Cannot open database. It may not be the database that your application recognizes, or the file may be corrupt.", despite of the fact that the database is open in front of me."