spreadsheet import problem

[polina]

Hi

I have a spreadsheet i need to import. One of the speadsheet's column contains LastName,FirstName separated by comma.
I only want to import the last name from that column

Please help to solve this problem,

Thanks

 

[afloyd]

I just did a quick check.....

Use the File -> Import and select File Type -> .xls

then select your file name.

An import wizard will launch. The wizard will take you through several screens. On the third screen the wizard will ask you information about the fields you are importing. There is a check box that says "Skip do not import." Select that box and you should be all set.

 

[polina]

the operation you described will skip the field
but I need to extract the part of the field. The field in the spreadsheet has Lee, John - when importing I only want Lee. How can I truncate that field?

 

[Pat Hartman]

You can't truncate it as part of the import process. Either import to a temp table and use an append query to select all the columns but extract the just the last name from the full name column. Alternatively, you can append to your main table and then run an update query.

 

[afloyd]

An add on to Pat's comment. You can save just the name field from the excel file as a comma delimited text file. That will split the field. Then use the import wizard to import to two fields in a temp table and then append just the last name field to the data table.

 

[tweetyksc]

I have something kind of similar I am trying to do.
I need to extract the number out of a field that has the unit of issue displayed as for example 100/box all together.
I need to export just what is before the "/" which is not always the same length so Left will not work. (1/carton, 1000/sheet)
I found some code on an excel website for creating a function called "Extract Element" and I'm hoping it will work. I'm putting a link here in case anyone wants to try it out also.
http://j-walk.com/ss/excel/tips/tip32.htm