JSON

JSON (JavaScript Object Notation), veriyi ad-değer çiftleri ve listeler halinde düz metin olarak tutan, web hizmetleri ve uygulamalar arasında çok yaygın kullanılan bir veri biçimidir. Excel'de JSON dosyaları ve web kaynaklarından gelen JSON verileri Power Query ile tabloya dönüştürülür. Excel JSON'u doğrudan okuyup yazan bir hücre işlevi içermez, ancak formüllerle basit JSON metinleri üretilebilir ve okunabilir.

JSON'un yapısı​

JSON yalnızca iki yapı taşıyla kurulur. Nesne süslü parantez içindeki ad-değer çiftleridir, dizi ise köşeli parantez içindeki değerler listesidir. Örneğin bir kayıt {"ad":"Ayşe","yas":34} biçiminde yazılır. Kayıt listesi ise bu nesnelerin köşeli parantez içinde virgülle sıralanmasıdır.

• Metin: Çift tırnak içinde yazılır. Tek tırnak geçerli değildir.
• Sayı: Tırnaksız yazılır ve ondalık ayırıcı her zaman noktadır (24.5 gibi). Türkçe Excel'in kullandığı virgül JSON'da geçersizdir.
• Mantıksal değer: Küçük harfle true ve false yazılır. Excel'deki DOĞRU ve YANLIŞ ile aynı sözcükler değildir.
• Boş değer: null olarak yazılır.
• Tarih: JSON'da ayrı bir tarih türü yoktur. Tarihler genellikle "2024-05-17" gibi ISO biçiminde metin olarak taşınır.

Ad-değer çiftlerindeki adlar da çift tırnak içinde olmalıdır. JSON'da yorum satırı yoktur ve son öğeden sonra fazladan virgül konamaz. Dosyalar genellikle UTF-8 kodlamasıyla kaydedilir, bu sayede Türkçe karakterler bozulmadan taşınır.

JSON verisini Excel'e aktarma​

JSON'u okumanın doğru yolu Power Query'dir. Komut Veri > Veri Al ve Dönüştür > Veri Al > Dosyadan > JSON'dan (Data > Get Data > From File > From JSON) yolunda bulunur. Bir web hizmetinden gelen JSON için Veri > Web'den (Data > From Web) komutu kullanılır.

• Komutu seçin ve .json uzantılı dosyayı gösterin. Power Query Düzenleyicisi açılır.
• Kök öğe bir kayıt listesi ise Tabloya (To Table) komutuyla listeyi tabloya çevirin.
• Sütun başlığındaki genişlet simgesine (Expand) tıklayın ve almak istediğiniz alanları işaretleyin. İç içe nesneler bu yolla düzleştirilir.
• Sütun veri türlerini (Tarih, Ondalık Sayı, Metin) kontrol edin.
• Giriş > Kapat ve Yükle (Home > Close & Load) ile sonucu çalışma sayfasına yükleyin.

Power Query arka planda Json.Document işlevini kullanır. Kaynak güncellendiğinde Veri > Tümünü Yenile (Data > Refresh All) komutu verileri yeniden çeker.

Excel'den JSON üretme ve sınırlar​

Excel'in tabloyu JSON dosyası olarak kaydeden hazır bir komutu yoktur. Küçük işler için ELEMAN birleştirme işleçleriyle (&) her satırdan bir JSON nesnesi metni üretilebilir. Bu metinleri METİNBİRLEŞTİR işleviyle köşeli parantezler içinde birleştirerek dizi elde edersiniz. Daha büyük ve düzenli işlerde VBA, Excel'in web sürümündeki Office Komut Dosyaları (Office Scripts) ya da Power Automate tercih edilir.

VBA'nın JSON'u okuyan yerleşik bir nesnesi yoktur. VBA ile JSON ayrıştırmak için ayrıca yazılmış bir kod kitaplığı gerekir. Aşağıdaki örneklerde yalnızca çalışma sayfası formülleriyle JSON üretme ve basit bir JSON metninden değer çıkarma gösterilmektedir.

1. Her satırdan bir JSON nesnesi üretme​

A1:D6 aralığına ürün listesini yazın. E2 hücresine metin birleştirme (&) ile JSON nesnesi üreten formülü girin ve aşağı kopyalayın. Metin alanları çift tırnak içine alınır, sayı tırnaksız yazılır, mantıksal değer ise EĞER ile true ya da false metnine çevrilir. SUBSTITUTE işlevi, ondalık virgülü varsa noktaya çevirir.
E2 hücresi ilk ürünün JSON kaydını verir: {"kod":"K-101","urun":"Dizüstü Bilgisayar","fiyat":24500,"stokta":true}. Üçüncü satırdaki ürün stokta olmadığı için E4 hücresinde stokta alanı false olarak yazılır: {"kod":"K-103","urun":"Mekanik Klavye","fiyat":1850,"stokta":false}. Bu metni bir metin düzenleyiciye yapıştırıp .json uzantısıyla kaydedebilirsiniz.

2. Basit JSON metninden değer okuma​

A sütununa her satırda tek bir JSON nesnesi içeren metinler yazın. B1:D1 hücrelerine okunacak alan adlarını (ad, sehir, bolum) yazın. B2 hücresine, A2 içindeki alan adını arayıp çift tırnaklar arasındaki değeri çıkaran formülü girin ve B2:D4 aralığına kopyalayın. Formül, başlık hücresindeki alan adını kullanır.
B2 hücresi ilk kaydın ad alanını (Ayşe Yılmaz), C2 hücresi şehir alanını (Ankara), D4 hücresi ise üçüncü kaydın bölüm alanını (İnsan Kaynakları) verir. Bu yöntem yalnızca tek düzeyli ve değerleri metin olan kayıtlarda çalışır. İç içe nesneler, sayılar ya da diziler içeren gerçek JSON verileri için Power Query kullanın.

3. Kayıtlardan JSON dizisi oluşturma​

A1:C5 aralığına personel listesini yazın. D sütununda her satır için bir JSON nesnesi üretin. E2 hücresinde METİNBİRLEŞTİR (TEXTJOIN) işleviyle bu nesneleri virgülle birleştirip köşeli parantez içine alın. TEXTJOIN işlevi Excel 2019 ve Microsoft 365 sürümlerinde bulunur.
D2 hücresi tek bir kaydı üretir: {"ad":"Ayşe Yılmaz","departman":"Muhasebe","maas":32000}. E2 hücresi dört kaydı köşeli parantez içinde tek metinde toplar: [{"ad":"Ayşe Yılmaz","departman":"Muhasebe","maas":32000},{"ad":"Mehmet Demir","departman":"Satış","maas":28500},{"ad":"Zeynep Kaya","departman":"İnsan Kaynakları","maas":30000},{"ad":"Ali Çelik","departman":"Lojistik","maas":26000}]. Bu metin, bir web hizmetine gönderilebilecek geçerli bir JSON dizisidir. Bir hücreye en fazla 32.767 karakter sığdığı için çok uzun listelerde bu yöntem yetmez.

Kullanırken dikkat edin​

• Ondalık ayırıcıya dikkat edin: Türkçe Excel ondalık ayırıcı olarak virgül kullanır, JSON ise nokta bekler. Formülle JSON üretirken sayıları metne çevirdiğinizde virgül kalırsa oluşan metin geçersiz olur. YERİNEKOY (SUBSTITUTE) ile virgülü noktaya çevirin.
• Excel'de JSON işlevi yoktur: Excel'de JSON metnini ayrıştıran ya da üreten yerleşik bir çalışma sayfası işlevi bulunmaz. Düzenli ve büyük veriler için Power Query'yi, kayıtlı bir dosya üretmek için VBA ya da Office Komut Dosyalarını (Office Scripts) kullanın. Metin işlevleriyle yapılan ayrıştırma yalnızca basit, tek düzeyli kayıtlarda güvenilirdir.
• Çift tırnak kuralı: JSON'daki tüm adlar ve metin değerler çift tırnak içinde olmalıdır. Excel formülünde çift tırnak karakteri yazmak için tırnağı iki kez yazarsınız (""). Metin içinde tırnak ya da ters eğik çizgi geçen veriler ayrıca kaçış karakteriyle düzeltilmelidir.
• Türkçe karakterler: JSON dosyalarını UTF-8 kodlamasıyla kaydedin. Farklı bir kodlamayla kaydedilmiş dosyalarda ğ, ş, ı, İ gibi harfler bozuk görünebilir. Power Query'de dosya seçildikten sonra gelen önizleme penceresinden kodlamayı kontrol edebilirsiniz.
• Tarih ve sayı türlerini kontrol edin: Power Query ile içe aktarılan tarihler çoğu zaman metin olarak gelir. Sütun türünü Tarih olarak değiştirin ve gerekirse yerel ayar seçerek dönüştürün. Sayıların yanlış tanınmaması için yükleme öncesi sütun türlerine bakın.
• İlgili konular:
  • Power Query: Veri Al ve Dönüştür grubundaki sorgu düzenleyicisi
  • Veri > Web'den: Web hizmetlerinden JSON ve tablo verisi çekme
  • CSV ve XML: Diğer metin tabanlı veri biçimleri
  • Excel tablosu: İçe aktarılan JSON kayıtlarının yüklendiği yapı
  • METİNBİRLEŞTİR: Birden çok hücreyi ayırıcıyla birleştirme
  • PARÇAAL, MBUL, UZUNLUK: Metin içinden değer ayıklama
  • YERİNEKOY ve METNEÇEVİR: Metin düzeltme ve sayıyı metne çevirme
  • EĞER: Mantıksal değeri true ya da false metnine çevirme

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.