Solved TransferSpreadsheet - Telephone Numbers not formatted correctly (1 Viewer)

AngelSpeaks

Active member
Local time
Today, 10:57
Joined
Oct 21, 2021
Messages
406
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
 

silentwolf

Active member
Local time
Today, 08:57
Joined
Jun 12, 2009
Messages
545
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

Immoderate Moderator
Staff member
Local time
Today, 10:57
Joined
Feb 28, 2001
Messages
26,999
You could try a triple replace.


Code:
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

Active member
Local time
Today, 10:57
Joined
Oct 21, 2021
Messages
406
Ok, so the suggestions would be to do an update query after import. Thanks.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:57
Joined
Feb 28, 2001
Messages
26,999
Do it when it is convenient based on your data flow, but yes, it probably would involve an update query sometime.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:57
Joined
Feb 19, 2013
Messages
16,553
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

Code:
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
 

Users who are viewing this thread

Top Bottom