Solved TransferSpreadsheet - Telephone Numbers not formatted correctly

[AngelSpeaks]

Importing from Excel can save a lot of time, but cause a lot of headaches. The first spreadsheet from client used the format xxx-xxx-xxxx for the telephone number. Now they are using (xxx) xxx-xxxx. Is there a way while transfering the spreadsheet that this column can be formatted to one layout? The telephone is to be exported to a csv file to upload to a State portal. They want either xxx-xxx-xxxx or all numeric for that column. I would like format to be the same for all rows in my table.

Thanks

 

[Gasman]

Use the replace() function?

 

[silentwolf]

Hi,
why are u not importing the Excel File and then update the phonenumbers to whatever Format you like?
Rather then change it before import?

Access is more then capable to do just that.

Create a custom function to update your phone numbers and run an update query.

HTH

 

[The_Doc_Man]

You could try a triple replace.

Replace function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

newstring = Replace( Replace( Replace( oldstring, "(", "" ), ")", "-" ), " ", "" )

If you have oldstring = xxx-xxx-xxxx it will do nothing. But if you have oldstring = (xxx) xxx-xxxx you get it reformatted. (Note that the outer Replace removes extraneous spaces.)

 

[AngelSpeaks]

Ok, so the suggestions would be to do an update query after import. Thanks.

 

[Gasman]

Ok, so the suggestions would be to do an update query after import. Thanks.

You could do the Replace on the fly?, that was what I was thinking of?, however importing to a staging table and cleaning the data is a well practiced process.

 

[The_Doc_Man]

Do it when it is convenient based on your data flow, but yes, it probably would involve an update query sometime.

 

[CJ_London]

rather than using transferspreadsheet to import, link instead then run an insert query, correcting values as you go,

Or use a query - would look something like this as a select query, just modify to become an insert query

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'D:\Path\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes])  AS XL;

change sheet1$ as required - can also reference ranges

[sheet1$A1:B25]

[sheet1$C50:F85]

etc

 

[Pat Hartman]

Just because a phone number is currently numeric, doesn't mean you should store it as a number. It is technically a code so store it in a text field.

 

[AngelSpeaks]

I do have it as a text field. Thanks