469,315 Members | 2,130 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

Importing only some columns of an excel file to access

68
I have an excel file that has 15 columns but I only need 4. I need to pull them into an Access table w. VBA.
I need
Column A: Client,
Column C: City, and
Column D: State.
The file will be of variable length.

My problem is that Column B is a phone number field that I don't want and what ever I do I can't avoid my database creating a import errors table for this field.
It contains a variety of numbers and spaces so I whether I have my table set to text or number they all error.
I don't care whether I import all the excel fields, or only the 3 I need, but I need some way to not error on column B.

Is it possible to import only certain columns? If so, does anyone have the code?
I would love the whole thing to be text and use import specs, but that's for TransferText and for excel you need to do TransferSpreadsheet.

I have tried looking on the web for the answer, but keep coming across people importing a single range. Thanks!
Jun 11 '11 #1
20 31333
Mihail
759 512MB
Just an idea: Copy-Paste the columns you need to another sheet or workbook (in Excel). Then import.
Good luck !
Jun 12 '11 #2
dk4300
68
For sure, but it will be run every few days and I'm trying to make it so the users don't need to adjust the import file.
Jun 12 '11 #3
No prob... Use PHP and extract the data from the excel file and import it into ur DB. If u use cron job, it will do automatically.
Jun 13 '11 #4
NeoPa
32,173 Expert Mod 16PB
It's interesting/unfortunate that MS don't provide the ability to define an Import/Export Spec for Excel files. It is possible during the manual import to specify columns to avoid, but this can't be saved for automation it seems.

It seems you can do it, but for non-contiguous columns, probably only into separate tables.

I used the following code to handle a duplicate of your situation :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub ACD()
  5.     Dim strSQL As String
  6.     Dim db As DAO.Database
  7.     Dim rs As DAO.Recordset
  8.  
  9.     Call DoCmd.TransferSpreadsheet(acImport, _
  10.                                    acSpreadsheetTypeExcel9, _
  11.                                    "tblData", _
  12.                                    "H:\ProfileName\Access\ImportTest.Xls", _
  13.                                    False, _
  14.                                    "A:A")
  15.     strSQL = "ALTER TABLE [tblData] ADD COLUMN [F2] Text(255)"
  16.     Call CurrentDb.Execute(strSQL)
  17.     strSQL = Replace(strSQL, "[F2]", "[F3]")
  18.     Call CurrentDb.Execute(strSQL)
  19.     Call DoCmd.TransferSpreadsheet(acImport, _
  20.                                    acSpreadsheetTypeExcel9, _
  21.                                    "tblDataTmp", _
  22.                                    "H:\ProfileName\Access\ImportTest.Xls", _
  23.                                    False, _
  24.                                    "C:D")
  25.     Set db = CurrentDb
  26.     With db.TableDefs("tblData").OpenRecordset(dbOpenTable, dbDenyWrite)
  27.         Call .MoveFirst
  28.         Set rs = db.TableDefs("tblDataTmp").OpenRecordset(dbOpenTable)
  29.         Call rs.MoveFirst
  30.         Do Until .EOF
  31.             Call .Edit
  32.             !F2 = rs!F1
  33.             !F3 = rs!F2
  34.             Call .Update
  35.             Call .MoveNext
  36.             Call rs.MoveNext
  37.         Loop
  38.         Call rs.Close
  39.         Call .Close
  40.     End With
  41.     Call db.TableDefs.Delete("tblDataTmp")
  42. End Sub
Clearly, there are potential changes to be made for a more sophisticated result.
Jun 13 '11 #5
Rabbit
12,516 Expert Mod 8TB
You could import into a temporary table, run an append, and then drop the temporary table.
Jun 13 '11 #6
NeoPa
32,173 Expert Mod 16PB
DK4300:
My problem is that Column B is a phone number field that I don't want and what ever I do I can't avoid my database creating a import errors table for this field.
It contains a variety of numbers and spaces so I whether I have my table set to text or number they all error.
I don't care whether I import all the excel fields, or only the 3 I need, but I need some way to not error on column B.
That would still error Rabbit. See second paragraph of OP.
Jun 13 '11 #7
Rabbit
12,516 Expert Mod 8TB
Shouldn't setting that field in the table and spreadsheet to text work? You just need to automate excel to modify the column to text and save before you do the import.
Jun 13 '11 #8
Rabbit
12,516 Expert Mod 8TB
If it's just the import errors table you're worried about, you can just drop that table if it exists after the import.
Jun 13 '11 #9
NeoPa
32,173 Expert Mod 16PB
No. It's not as simple as either of those.

Deleting a table that you don't know the name of is a problem and automating Excel is more complicated (even) than importing as separate ranges and merging them together as my code illustrates. Certainly there is no straightforward solution. What I posted was the closest to that I could come up with (though I appreciate it's quite wide of the mark).
Jun 13 '11 #10
dk4300
68
Shoot! It's definately frusterating that it allows me to set up and save the import specs but not use them in vba!

NeoPa, I'm going to check out your code. Thanks for the reminder that I wouldn't know the name of the file I was going to delele, because I was assuming it was something similar (or started in something similar each time). Would there be any other way to identify the import errors table?
Jun 15 '11 #11
Rabbit
12,516 Expert Mod 8TB
The import errors table always has the string "$Import_Errors" at the end of it.
Jun 15 '11 #12
NeoPa
32,173 Expert Mod 16PB
dk4300:
Shoot! It's definately frusterating that it allows me to set up and save the import specs but not use them in vba!
That's a misunderstanding. Access allows Import/Export specs for Text files (not Excel) and they certainly can be used from VBA. See comment in post #5.
Jun 15 '11 #13
dk4300
68
NeoPa, Yes, I was referring to Excel. I think it's strange that I can manually import an excel file, set up import specs, save the specs, but then have no way of using them again.

I know that text files are different and that you can use your specs name in the TransferText function.

I wasn't expecting not to be able to do this. There is no other function to use?

Rabbit, In the meantime while I was waiting for a reponse I ended up setting up something to delete the import table. Thanks for the post I will update it to delete where the end is "$Import_Errors"
Jun 15 '11 #14
NeoPa
32,173 Expert Mod 16PB
A number of confusions here.
  1. You say you can save Excel import specs. I can't in 2003 (Not for Excel - only for Text). Are you using a later version possibly? If you're using a feature not available to all versions it's necessary to indicate the version in the question (See the various instructions on how to post).
  2. If you are using a version that allows saving of Excel Import specs then I suspect that it is indeed possible to reuse these specs from your code. I have no way for now of testing this out, but I'd be very surprised if it were not so.
  3. You say "In the meantime while I was waiting for a reponse". Did you ever try out the code I included in post #5? There is no direct response to it. It's really down to you to ensure you read all of the replies.

Frankly, if there are saved Excel specs then I'd use them in preference, but if not then the code I posted worked perfectly for me.
Jun 15 '11 #15
dk4300
68
NeoPa,
Thank you a TON for reading and replying! My tone may have come across less appreciative that I intended!

1. I'm using 2007. You are right that I should state that. It didn't occur to me. Also, I have had some time off from building databases so I don't actually remember what I was doing in previous versions. I'll double check this point, and maybe see if I still have things I've built in the past to get a better handle of what I have and what I've done in the past.
2. I agree which is why I was so confused about it. I'll do more research
3. When I said waiting for a response that had nothing to do with the responders or me not reading them. I'm very interested in what everyone has had to say and read it all. I only meant that I was playing around with it RIGHT after I posted it and that's what I came up with but I was hoping you all had a better solution for me. I copied your code but haven't yet tried it. I am reluctantly deleting the import table since I had already set that up (though incorrectly w/ the table name) before I got your suggestion. I want to check yours out as another/better option, but I have a few more things I need to figure out/build in the next couple of days.
4. In fact, if I haven't gotten a chance to respond sooner, it's becuase I was reading (and bookmarking) some of your other posts! Seriously. :) Forums like these are super helpful.
Thank you!
Jun 16 '11 #16
NeoPa
32,173 Expert Mod 16PB
I was simply curious. Sometimes people overlook a post when someone else posts before they get a chance to look. Essentially, they work under the assumption that when they are notified a new post is available there is necessarily only one post. It's a mistake, but one that occurs from time-to-time, especially by those not used to working in a forum. I honestly felt this may have happened so was drawing your attention to it. I won't deny it's a good idea for you to respond to all posts in one of your threads, but not everyone does, and I'm generally not too worried about it. In this case I was just worried about the possibility that after all this time you hadn't even noticed it.

In short, your tone was not an issue and thank you for your kind words. I'm very glad that some of my posts can help people looking to learn more about Access or Excel.
Jun 16 '11 #17
dk4300
68
I am new to forums and don't have it all figured out (I've ended up clicking on the link to the question to get all the reponses. I don't know how it's supposed to work).

I was actually about to respond to all the posts, but then thought I shouldn't. Thought it might be too much of me. :).
I made a brief reference to your code earlier, but only to say I was going to check it out.

I've encountered the pesky security window in automatically sending emails via outlook and this afternoon I was looking at your outlook automation but didn't know if it took that pesky window into account...
Jun 16 '11 #18
NeoPa
32,173 Expert Mod 16PB
dk4300:
I made a brief reference to your code earlier, but only to say I was going to check it out.
Of course you did. I overlooked that when I saw the later post. I was getting a bit confused with a bunch of things that didn't seem to make sense, but you're quite right. You did refer to it. That's all that I was looking for really (but I missed it when it was).
dk4300:
I've encountered the pesky security window in automatically sending emails via outlook and this afternoon I was looking at your outlook automation but didn't know if it took that pesky window into account...
You'll have to try it out and see. I've really done very little with Outlook due to exactly that problem. That code was provided by another (very helpful) member (fauxanadu).
Jun 16 '11 #19
Rabbit
12,516 Expert Mod 8TB
You can get around the Outlook limitations by using the CDO object instead.
Jun 16 '11 #20
NeoPa
32,173 Expert Mod 16PB
CDO (and CDONTS) are alternatives to using Outlook at all (and as such get around its restrictions of course). Environments vary of course though, and not all environments allow the sending of SMTP packets (as done by the CDO & CDONTS approaches) from all machines.

In reality most do, but in very secure environments such messages can be blocked at the firewall.
Jun 16 '11 #21

Post your reply

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

Similar topics

4 posts views Thread by Janelle.Dunlap | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.