By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

Import data from one Excel column

P: 20
If someone would tell me if there is a simple or any way to import data from only one field in an Excel spreadsheet to a table field ?
Jan 25 '08 #1
Share this Question
Share on Google+
10 Replies


Dököll
Expert 100+
P: 2,364
If someone would tell me if there is a simple or any way to import data from only one field in an Excel spreadsheet to a table field ?
Hiya, booboo!

Have you previously searched this forum for input?

You may find a solution here...

Unless of course you mean Importing Excel to Access, which I think you can skillfully achieve by going to your Access database and selecting New in Tables, and Import Table in pop up, then you'd need to fetch the Excel you're refering to...

If you need to do this in code, and you have an almost working code, post it for a closer look. Good luck with the project:-)
Jan 25 '08 #2

P: 20
Thanks, I know how to do an entire file into a table.
I am needing to import one column/field with data from an Excel spreadsheet to an existing table.
Yes I have searched for this but only found info on doing the whole file.
Jan 25 '08 #3

ADezii
Expert 5K+
P: 8,679
Thanks, I know how to do an entire file into a table.
I am needing to import one column/field with data from an Excel spreadsheet to an existing table.
Yes I have searched for this but only found info on doing the whole file.
There is actually a very simple Method to Import a single Column of an Excel Spreadsheet into an Access Database. Let's assume your Spreadsheet is named Employees.xls and it is in the C:\Test\ Directory:
  1. In Excel, define a Range for the single column
    1. Select the Column.
    2. Insert.
    3. Name.
    4. Define
    5. Enter a Name for the Range. For this Demo, we'll simply name it LastName.
  2. Execute the following line of code which will Import the LastName Range (last Argument) from the C:\Test\Employees.xls Spreadsheet into a Table named Test Table, with the First Row designated as having Field Names (5th Argument - True).
    Expand|Select|Wrap|Line Numbers
    1. 'acSpreadsheetTypeExcel9 is the Argument for Excel 2000.
    2. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test Table", "C:\Test\Employees.xls", True, "LastName"
  3. The entire Excel Column will be Imported meaning 65,536 Rows, whether or not they contain data. You can easily the restrict the number of Rows when you initially Define the Range.
  4. Now, you can run a Query on Test Table to do whatever you like with the data.
  5. Good Luck, and any other questions, please feel free to ask.
Jan 25 '08 #4

P: 20
I really appreciate that. I need to import specific columns from Excel (2003) spreadsheets to existing tables. I am trying to do this to update initial data in tables in the db without having to redo all the tables, forms, querys, etc or in essence, the whole db.
Point is, I am building an Access 2003 program to replace an old Foxpro for dos program I built 12 years ago for my sister. Problem is, she is using the old program every day while I am building the new one.
Unfortunately, I need to change make the table structures different than the old ones and was thinking if I could import specific columns, I wouldn't have to rebuild.
But, I don't think it's going to be worth it.
Probably just have to rebuild it.
But what you gave me will sure come in handy and I appreciate your time and expertise.
I've got 4 or 5 other questions I need help on but I am reluctant to keep bothering you guys.
Jan 25 '08 #5

ADezii
Expert 5K+
P: 8,679
I really appreciate that. I need to import specific columns from Excel (2003) spreadsheets to existing tables. I am trying to do this to update initial data in tables in the db without having to redo all the tables, forms, querys, etc or in essence, the whole db.
Point is, I am building an Access 2003 program to replace an old Foxpro for dos program I built 12 years ago for my sister. Problem is, she is using the old program every day while I am building the new one.
Unfortunately, I need to change make the table structures different than the old ones and was thinking if I could import specific columns, I wouldn't have to rebuild.
But, I don't think it's going to be worth it.
Probably just have to rebuild it.
But what you gave me will sure come in handy and I appreciate your time and expertise.
I've got 4 or 5 other questions I need help on but I am reluctant to keep bothering you guys.
But what you gave me will sure come in handy and I appreciate your time and expertise.
I've got 4 or 5 other questions I need help on but I am reluctant to keep bothering you guys
First of all, that is what we are all here for, of our own free will. (LOL).

The way I see the situation, you have 2 alternatives:
  1. Define multiple Ranges in Excel for the Columns you wish to Import, Import the Columns, then perform multiple Append operations.
  2. Use Automation code to Import the data into Access, this approach would be considerably more complicated.
  3. If you like, you can:
    1. Send me the Database with a subset of the data.
    2. Send the Excel Spreadsheet.
    3. Explain exactly which Columns in the Spreadsheet you would like to Import, which Table(s) the data gets appended to, in what order, etc.
  4. If you are interested, let me know and I'll send you my E-Maill address in a Private Message and look at it as soon as I can.
Jan 25 '08 #6

P: 20
That is extremely nice of you.
However, I told my sister this morning that there would be a point that she will have to log everything she does until I am finished so that she or I could make the manual updates to the new program.
I just setup a new pc for her and it appears it is just too fast for the old dos program. It is too erratic when she is in the program, like hesitates on keystrokes and such. So I figured it's time to get it into Windows.
I haven't done any programming in 10 or 12 years and trying to catch up enuff to do this. So the syntax for queries and such - just hard to get my head around.
Plus, there are no examples / samples of accounts receivable stuff out there. So I'm starting from scratch on stuff like entering payments (checks received) which of course, has to adjust the existing balance once the ck amount is entered.
What I need is an expert for hire to just help me around the corners. lol
Jan 25 '08 #7

ADezii
Expert 5K+
P: 8,679
That is extremely nice of you.
However, I told my sister this morning that there would be a point that she will have to log everything she does until I am finished so that she or I could make the manual updates to the new program.
I just setup a new pc for her and it appears it is just too fast for the old dos program. It is too erratic when she is in the program, like hesitates on keystrokes and such. So I figured it's time to get it into Windows.
I haven't done any programming in 10 or 12 years and trying to catch up enuff to do this. So the syntax for queries and such - just hard to get my head around.
Plus, there are no examples / samples of accounts receivable stuff out there. So I'm starting from scratch on stuff like entering payments (checks received) which of course, has to adjust the existing balance once the ck amount is entered.
What I need is an expert for hire to just help me around the corners. lol
Can you run the old FoxPro for DOS in a different Compatability Mode?
Jan 25 '08 #8

P: 20
Yes I've tried each of them, edited the config.nt, changed every variable in the properties of the shortcut (command), etc. etc.
It ran on her previous pc which was 1.6ghz CPU and this one is 3.3ghz, runs but erratically.
Just as well, I need to get it into Windows anyway.
Jan 25 '08 #9

P: 48
Thanks, I know how to do an entire file into a table.
I am needing to import one column/field with data from an Excel spreadsheet to an existing table.
Yes I have searched for this but only found info on doing the whole file.
I have the same problem with yours.
I searched in the net and found that
it only imports the whole sheet in Excel to Access DB.

Here's my example:
[tblTable].Col1 = Sheet1!A1
[tblTable].Col2 = Sheet1!A2
[tblTable].Col3 = Sheet1!C5
[tblTable].Col4 = Sheet2!B4
[tblTable].Col5 = Sheet2!B5
[tblTable].Col6 = Sheet2!G7

"tblTable" is in MS Access DB.

Is this possible?
If so, how?
Aug 11 '08 #10

ADezii
Expert 5K+
P: 8,679
I really appreciate that. I need to import specific columns from Excel (2003) spreadsheets to existing tables. I am trying to do this to update initial data in tables in the db without having to redo all the tables, forms, querys, etc or in essence, the whole db.
Point is, I am building an Access 2003 program to replace an old Foxpro for dos program I built 12 years ago for my sister. Problem is, she is using the old program every day while I am building the new one.
Unfortunately, I need to change make the table structures different than the old ones and was thinking if I could import specific columns, I wouldn't have to rebuild.
But, I don't think it's going to be worth it.
Probably just have to rebuild it.
But what you gave me will sure come in handy and I appreciate your time and expertise.
I've got 4 or 5 other questions I need help on but I am reluctant to keep bothering you guys.
Even many 'Old DOS Programs' have the capability to Export their data into a ASCII Delimited Text File which could then be easily imported into Access. Any chance that this is the case here?
Aug 12 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.