Importing Data

[Iwra]

I have a spreadsheet that I import to Access. The only problem with this is that once the data is imported I am running a query to find duplicates on a name field. The Excel spreadsheet is in the format of Surname, Firstname while the database is set up as Firstname Surname so the names are in the reverse order and the comma is there also so this would then stop the query from finding the duplicates. Does anyone know if the format can be change while importing to match that of the database?

Thanks

 

[Hellfire]

Hi,

I don't know if this would be posible in your case, but, you could change the format of the spreadsheet before importing it.

Do you have two collumns in Access, one with first names, and one with last names, or do you have one collumns with Surname, Firstname

Let me know.

 

[Iwra]

I thought about changing the format of the spreadsheet but only problem with that is that it is sent onto me every month in this format (one column with Surname, Firstname) So I would have to change this every time the latest spreadsheet is sent. I just thought there might be a way to change the format automatically on importing that I didn't know about. Is this not possible then?

 

[Hellfire]

Not that I know of. It's not that much trouble changing the Spreadsheet every time, you just do one, and then let the sequince copy automatically.

There might be another way, i'm not very good, but not that I know of anyway.

Sorry!

 

[The_Doc_Man]

Well, there MIGHT be a way to do this. It depends on the reliability of that input format ALWAYS being LastName, FirstName. A few possibilities come to mind.

It is possible to build a function in VBA to do this sort of rearrangement. Or you can try to build a complex expression in one of your queries.

This function or complex expression can be used as a pre-processing step when you are importing but before you do your search. Perhaps something like, import your data from spreadsheet to temporary table, then run a MAKETABLE query to build the REAL data, using the complex expression in the MAKETABLE rather than the search query. MAKETABLE queries are linear. A search query might take longer with a complex expression in the midst of trying to match up names.

If you are not familiar with these functions and the concatenation operator, then read up on them. They work either in VBA or in the query design grid (and therefore SQL).

InStr, Len, Left, Right as functions and & as an operator.

OK, here is what you would do. Assume the input field name (with comma) is stIName for the snippet below and stOName is the re-ordered name. This is the VBA, with hard RETURN forced in to separate the components.

stOName =
Trim( Right( stIName, Len(stIName)-InStr(1,stIName,",")))
& " " &
Trim(Left( stIName, InStr(1,stIName,",")-1))

Now, if you want this in a query grid, drop the "stOName="

NOTE: This won't work correctly for any name that isn't in the format "LastName, FirstName" - so it would be a good idea to sanity check everything. Which is why I mentioned a VBA function. If you know how to write a public VBA function in a general module, you can use that function in queries, forms, etc.

 

[Iwra]

Thanks to you both. I will read up on this Doc Man, I know a little VBA but am not too advanced as yet. I'll have a play around with this and see what I can get to work for me.

Cheers