• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

Import data to Access

I am trying to import data from couple of spreadsheet and text files. For Excel files, I may just need range or some columns from each of the file...also part of the text file. It's simple to import the whole file, and then save to assigned table. My problem is, the files come from Prime Broker everyday, how can make the process automatic instead of setting the range mannually every day ?

And does anyone meet this kind of problem before: some of the Excel file with headers in merged cells, then Access can't even open the file....but if I manually remove the header, then it works...:smt100:smt100:smt100

Can anyone give me a hint on how to make it smooth and efficient ? Thanks a lot.
 
I'm guessing VBA macro can accomplish this. Not sure how to do it specifically, havent come across this kind of problem just yet.
 
Are you doing import in Access 2003 or in Excel?

In Access 2003 as well as in Access 2007 you can check "First Row Contains Column Headings".
In Access 2003:
Right click on the Tables tab in Access - go to Import - choose a file - next - check First Row Contains Column Headings checkbox
In Access 2007 you have advanced functionality for import.
 
I am using Access 2003. Vlad, I can inport the whole file, my problm is (1) if there're merged cells in the file, the import fail..(error message says format is not correct); (2) if I want only some ranges or columns in the files, how can I accomplish?

Thanks a lot.
 
As an Access developer, I strongly recommend against importing data from Excel into Access. If it's at all possible (and since you want to automate the process, I'm sure you can) I would write a quick Sub procedure that converts the Excel file into a delimited text file. Once that is done, you can import the text file.

Importing from a delimited text file has several distinct advantages over importing from Excel. For one, Access gets very cranky when importing from Excel. This makes little sense, as one would expect the two programs to be highly interoperable, but since when do we expect sensible design from Microsoft Office? For another, you can create an Import Specification that can indicate which columns to import and which to skip, as well as enforce strict data typing on import. I never set up automated imports without import specs.
 
As an Access developer, I strongly recommend against importing data from Excel into Access. If it's at all possible (and since you want to automate the process, I'm sure you can) I would write a quick Sub procedure that converts the Excel file into a delimited text file. Once that is done, you can import the text file.

Importing from a delimited text file has several distinct advantages over importing from Excel. For one, Access gets very cranky when importing from Excel. This makes little sense, as one would expect the two programs to be highly interoperable, but since when do we expect sensible design from Microsoft Office? For another, you can create an Import Specification that can indicate which columns to import and which to skip, as well as enforce strict data typing on import. I never set up automated imports without import specs.


Adam, thanks for the suggests. Since files from prime brokers are both Excel and test files everyday, I do want to make it automated. But sometimes some positions closed and the Excel sheet format would change a little bit......:-ss
So, what's your idea for the import specs??? Thanks a lot.
 
Maggie,

Can you post a sample of the file or PM it to me? I can check it out.

Thanks

Chris


Chris, I probably couldn't do that....data is confidential :D...but I will make up one and PM you later....

Thanks.

BTW, I saw many Bloombrg guys wearing JP Morgan corporate run T-Shirt the other day when working out the building...I was wondering if you care to ran this year.....:)
 
TThis is the error message I got. The file definitely exists, and when I took out the headers in merged cells, it works...


he wizard is unable to access information in the file'200706021.1_Year_Delta_Normal_95%_by_Fund.xls'. Please check that the file exists and is in the correct format.
 
Here's my rough and dirty suggestions for proceeding with this project...
  1. Open the Excel file, delete the header row with the merged cells, and save it as a .txt file.
  2. Go to Access, and use the File => Get External Data => Import menu option.
  3. DO NOT HIT FINISH!!! Instead, set the file import the way you want, but hit the Advanced button in the lower left corner of the wizard.
  4. Make sure the Advanced settings are to your liking (i.e. the right fields import, the wrong fields get skipped, each field is named the way you want it and in the right data type), and hit Save Import Spec. Give the spec a name. Save the spec. Then, once the spec is saved, you can cancel out of the import.
Now, you have an import spec. Without it, automating the import becomes very difficult. With the spec, here's all you need to put into an automated import Sub procedure.
  1. Have the Sub open the Excel file, delete the header row with the merged cells, and save it as a .txt file, the same as step 1.
  2. Use DoCmd.TransferText to import the saved .txt file. Make sure you include the name of the previously created import spec in the appropriate argument.
Let me know how it goes...
 

fixedincomenyc

Fixed Income Analyst
Adam, nice post.

This seems to be a recurring problem in finance involving access/excel imports and exports. There is a transferspreadsheet method as well, with acexport, acimport, aclink paramerters for directional data flow. These functions you probably won't know of unless you are a vba guru because they aren't pure 'excel'.
 
I got an issue with Access 2007. Got multilpe text files and want to import them all at once using the same import spec and placing them in one table. Can this be done?

Im not a vba whiz and I guess ill be needing some code to do this.

Thanks
Yakult
 
if you manual procedure is more or less the same everyday, you should be able to similar that procedure (including removing header, de-merge cell, get external data, even Access database replication etc) using either VBA or C# etc.
 
if you manual procedure is more or less the same everyday, you should be able to similar that procedure (including removing header, de-merge cell, get external data, even Access database replication etc) using either VBA or C# etc.


Thanks for the reply. Was suprised this old thread emerged again. It was more than two years ago when I just started my intern with Citigroup. Yes, I had the procedures coded for external data imported to & from Access databased.
 
Thanks for the reply. Was suprised this old thread emerged again. It was more than two years ago when I just started my intern with Citigroup. Yes, I had the procedures coded for external data imported to & from Access databased.

Oh? How comes it popped up in the first page? ;-)

(I seldom read page that is not on the 1st page :->)
 
Top