473,326 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Import data from one Excel column

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
10 8694
Dököll
2,364 Expert 2GB
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
booboo
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
8,834 Expert 8TB
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
booboo
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
8,834 Expert 8TB
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
booboo
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
8,834 Expert 8TB
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
booboo
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
keirnus
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
8,834 Expert 8TB
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

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

Similar topics

2
by: Hon Seng Phuah | last post by:
Hi all, I have a huge excel format file wants to export to sql server database. One of the field has combination of numeric and alphanumeric. When I import the excel format to sql server...
1
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
2
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
2
by: Siu | last post by:
Hi, I use the following code to export and import a file Excel from resp. into a Web page with the following code: //EXPORT Response.Clear(); Response.Buffer = true; Response.ContentType =...
3
by: Schultz | last post by:
I would like to know if it is possible to import data from MS Excel 2000 to a dataset using asp.net. The excel file would need to be uploaded to the server from a web page first, before the loaded...
11
by: MD | last post by:
Hello, I need to import a sheet of 884 different excel-file with same lay- out. The sheet name is 'Totaal' and is the same in all different files. Is there a script (module) in order to: 1....
2
by: lildiapaz | last post by:
Can someone please explain to me how to select a specific column in an excel spreadsheet and import the information in that column to an sql table in a c# windows application. Let's say my excel...
1
by: thesti | last post by:
hello, i know that we can import data to SQL server from excel files, i've tried this in SQL server 2000 enterprise manager. but i'm stucked at how to configure which table the data will be...
0
by: apssiva | last post by:
I need to import some particular column data from csv or excel files in my student table so that for each student ID it will update an existing field. My tools for accessing the database on the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.