o'zgaruvchan naqshli yozuvlardan bir qatorni ajratish

Menda shunga o'xshash excel fayli mavjud. Misol sifatida, men bu erda faqat 2 ta yozuvni namunali sifatida joylashtirayapman va bu kabi 10000 ta yozuvlar mavjud.

1) 8984 Beachwood Rd Wasaga Beach ON L9Z 2X8 Phone: (705) 422-1000 Fax: (705) 422-1006
2)Bronte Health Centre 78 Jones Street Oakville ON L6L 6C5 Phone: (905) 827-9865
Fax: (905) 827-3583

Ushbu satrlar bitta kodli PRIMARY PRACTICE LOC tagidagi bitta xonada. Endi faks raqamlarini faqat ushbu yozuvlardan chiqarishni istayman. Qo'l bilan bajarish variant emas, chunki ko'pchilik bor. SQL asoslarini tushunaman, shuning uchun agar kimdir menga bu yozuvlardan faqat faks raqamini chiqarib, yangi ustunga qo'yadigan so'rovni taklif qilishda yordam berishi mumkin bo'lsa, men bu erda qolib ketganim sababli loyiham bilan davom ettirishim mumkin. Onlaynda qidirdim va SQLning substring va splitstring funktsiyalarini topdim, lekin bu erda ulardan qanday foydalanishim haqida hech qanday tasavvurga ega emasman. Ushbu muammoni qanday hal qilish bo'yicha har qanday yo'l-yo'riqlar juda yaxshi bo'lardi.

1
Men taxminan Excelda muntazam ifoda imkoniyatlari . Men faqat CSVga eksport qilmoqchiman va keyin grep dan foydalangan bo'lar edim. MS SQL bunga ega emas, ammo: faqat naqshni moslashtirish emas, balki ekstraksiya.
qo'shib qo'ydi muallif 9000, manba
Nima uchun bu ma'lumotni excel ichida chiqarib tashlash va keyinchalik JBga import qilish kerak?
qo'shib qo'ydi muallif Ubercool, manba
= RIGHT (A1, LEN (A1) - 4-SEARCH ("Faks:", A1)) kabi funktsiyadan foydalanishingiz mumkin. Bu erda A1 uyali ma'lumotlar
qo'shib qo'ydi muallif Ubercool, manba

5 javoblar

Men buni excelda qilishni taklif qilaman va keyin DB ga (agar kerak bo'lsa)

just go to Data => Text To Columns Then separate by colon and play a bit with the columns.

Bu juda ham tezroq bo'ladi.

enter image description here

1
qo'shib qo'ydi
To'g'ri, shuning uchun yo'g'on ichakda
qo'shib qo'ydi muallif user1913615, manba
Har bir yozuvdagi bo'shliqlar soni har xil bo'lishi mumkin, shuning uchun har bir yozuv uchun turli xil ustunlar paydo bo'lishiga olib kelishi mumkin.
qo'shib qo'ydi muallif Ubercool, manba

Faks mag'lubiyatining boshlanishini topish uchun CHARINDEX dan foydalanganingizdan so'ng FAX raqamini stringdan chiqarish uchun SUBSTRING funksiyasidan foydalanishingiz mumkin

Jonli namoyishga qarang

create table yourtable ([PRIMARY PRACTICE LOC] nvarchar(max));
insert into yourtable values
('8984 Beachwood Rd Wasaga Beach ON L9Z 2X8 Phone: (705) 422-1000 Fax: (705) 422-1006'),
('Bronte Health Centre 78 Jones Street Oakville ON L6L 6C5 Phone: (905) 827-9865 Fax: (905) 827-3583');

select 
    Fax=
       SUBSTRING([PRIMARY PRACTICE LOC],CHARINDEX('Fax',[PRIMARY PRACTICE LOC]),LEN([PRIMARY PRACTICE LOC]))
from yourtable
1
qo'shib qo'ydi

SUBSTRING , PATINDEX va CHARINDEX - ular orasidagi masofa.

select *, SUBSTRING(data.row, fax.idx + 5, IIF(SpaceAfter.idx < 1, 20, SpaceAfter.idx - 1))
from (values
('8984 Beachwood Rd Wasaga Beach ON L9Z 2X8 Phone: (705) 422-1000 Fax: (705) 422-1006 '),
('Bronte Health Centre 78 Jones Street Oakville ON L6L 6C5 Phone: (905) 827-9865 Fax: (905) 827-3583')) data(row)
cross apply (select PATINDEX('%Fax: %', data.row) as idx) fax
cross apply (select CHARINDEX(' ', data.row, fax.idx + 15) as idx) SpaceAfter

Birinchi chiziq bu erda Fax: matnini topadi. Ushbu indeksdan keyin 5 ta nusxa ko'chirishni boshlaymiz. Ikkinchi chiziq faks raqamidan keyin bo'sh joy topadi. Hech bo'sh joy bo'lmasa, IIF funktsiyasi keyingi 20 ta nusxani nusxalash uchun SUBSTRING-ni aytib nusxasini oladi.

Albatta, siz qo'shimcha tekshiruvlar qo'shishingiz mumkin, masalan, Faks: matnning hech bo'lmaganda:

IIF(fax.idx = 0, null, SUBSTRING(data.row, fax.idx + 5, IIF(SpaceAfter.idx < 1, 20, SpaceAfter.idx - 1)))
0
qo'shib qo'ydi

Bundan tashqari, faksni har doim ustundagi oxirgi qiymatni qabul qilsangiz,

declare @table table(
[PRIMARY PRACTICE LOC] varchar(max))

insert into @table
values('8984 Beachwood Rd Wasaga Beach ON L9Z 2X8 Phone: (705) 422-1000 Fax: (705) 422-1006'),('Bronte Health Centre 78 Jones Street Oakville ON L6L 6C5 Phone: (905) 827-9865
Fax: (905) 827-3583')

SELECT
RIGHT([PRIMARY PRACTICE LOC],len([PRIMARY PRACTICE LOC])-CHARINDEX('Fax',[PRIMARY PRACTICE LOC])-3) as Fax
from  @table
0
qo'shib qo'ydi

Masalan, python kabi oddiy dasturlash lengiazasidan foydalanishga maslahat beraman, agar siz ushbu jadval pythonni ishni bajarish uchun formatlashni xohlasangiz

  1. Wrap the hole string
  2. Detect key words like Phone and Fax , in this phase you should have and array with 3 elements like [8984 Beachwood Rd Wasaga Beach ON L9Z 2X8, Phone: (705) 422-1000,Fax: (705) 422-1006 ]
  3. Then with a python librarie insert in the excel with the desire format

Also i found this link useful https://www.extendoffice.com/documents/excel/3639-excel-extract-part-of-string.html

0
qo'shib qo'ydi