تبدیل xml به table

Printable View

چهارشنبه 09 آبان 1397, 20:11 عصر
ATA_TABRIZ

تبدیل xml به table

با سلام و تشکر از دوستان و مدیران گروه
کسی در مورد تبدیل فایل xml یا تکست به جدول با استفاده از vba را کد نویس کرده یا نه لطفا در صورت داشتن اطلاعات راهنمایی نماییید
ممنون از لطفتون
شنبه 12 آبان 1397, 12:43 عصر
mazoolagh

نقل قول: تبدیل xml به table

هم میتونین از منو external data-->import xml استفاده کنین و هم از دستور importxml
یک شنبه 13 آبان 1397, 10:28 صبح
ATA_TABRIZ

نقل قول: تبدیل xml به table

نقل قول:

نوشته شده توسط mazoolagh

هم میتونین از منو external data-->import xml استفاده کنین و هم از دستور importxml

سلام جناب آقای Mazoolagh و تشکر از لطفتون که همیشه شامل حال بنده بوده
از Import استفاده کردم ولی جداول جداانه میاره که ارتباط رکوردها را باهم نمیشه تشخیص داد
<Y>
<HR>
<DC>71</DC>
<PT>760</PT>
<CR>09144151582</CR>
<FD>951001</FD>
<TD>951030</TD>
<RC>13</RC>
</HR>
<X>
<PH>
<SQ>1</SQ><ND>951004</ND><SN>1016434987-1</SN><PC>129680</PC><PT>103</PT>
<PP>147840</PP><PS>44352</PS><IS>103488</IS><RD>951004</RD>
</PH>
<BY>
<MH><MG>701650</MG><MP>147840</MP><MS>44352</MS><MI>103488</MI><MD>1</MD><MR>1</MR></MH>
</BY>
</X>
<X>
<PH>
<SQ>2</SQ><ND>951007</ND><SN>1382752261</SN><PC>138217</PC><PT>103</PT>
<PP>850080</PP><PS>255024</PS><IS>595056</IS><RD>951008</RD>
</PH>
<BY>
<MH><MG>701555</MG><MP>462000</MP><MS>138600</MS><MI>323400</MI><MD>1</MD><MR>1</MR></MH>
<MH><MG>701655</MG><MP>388080</MP><MS>116424</MS><MI>271656</MI><MD>1</MD><MR>1</MR></MH>
</BY>
</X>
<X>
<PH>
<SQ>3</SQ><ND>951015</ND><SN>1739445082</SN><PC>7317</PC><PT>103</PT>
<PP>609840</PP><PS>182952</PS><IS>426888</IS><RD>951016</RD>
</PH>
<BY>
<MH><MG>701555</MG><MP>462000</MP><MS>138600</MS><MI>323400</MI><MD>1</MD><MR>1</MR></MH>
<MH><MG>701650</MG><MP>147840</MP><MS>44352</MS><MI>103488</MI><MD>1</MD><MR>1</MR></MH>
</BY>
</X>
</Y>

این xml را که قبلا با لطف شما ساختیم اگه ایمپورت کنیم به سه جدول HR , MH , PH تبدیل میکنه که من میخوام جدول MH , PH در یه جدول باشه و رکوردهاش باهم باشن به همین خاطر کد vba میخوام تابتونیم داده های لازم را در یک رکورد قرار دهم
( بخش PH مقادیرش ثابته ولی بخش MH تعداد سطرهاش شاید فرق بکنه )
البته این فایل میتونه تکست هم باشه در هر صورت من نیاز به خواندن دو بخش MH , PH و ذخیره در یک رکورد جدول را دارم

مجددا از توجه شما نهایت تشکر را دارم
دوشنبه 14 آبان 1397, 08:29 صبح
mazoolagh

نقل قول: تبدیل xml به table

سلام و روز خوش - شما لطف دارین
اجازه بدین اول چند موضوع باز بشه:

1- با توجه به اینکه این فایل xml از روی دیتا شما ساخته میشه چه نیازی هست دوباره روند معکوس رو پیاده کنین؟

2- این نمونه که شما گذاشتین در واقع 3 جدول هست:
یک جدول HR که احتمالا منظورشون header بوده
یک جدول PH که هر رکوردش یک نسخه است
یک جدول MG که هر رکوردش یک سرویس یک نسخه است
اگر اشتباه میکنم تصحیح کنین

بین جدول PH و MG باید یک ارتباط یک به چند باشه (بر اساس SQ که احتمالا منظورشون sequence بوده و همون ID هست) - اگر هر دو رو در یک جدول بیارین باید دیتا تکراری نگه داری کنین؛ یعنی بازای هر رکورد MG باید تمام فیلدهای PH رو هم اضافه کنین که کلا کار نادرستی هست.

راه درست و اساسی این هست که اگر واقعا دسترسی به دیتا اصلی ندارین و فقط همین فایل xml رو دارین باید یک فایل xslt بسازین که موقع ایمپورت جدول (ها) رو بر اساس ساختار موردنیازتون بسازه که البته کار آسونی هم نیست!

البته پروسس مستقیم فایل و استخراج دیتا و بازنویسی اون هم هست که راه ساده تری هست نسبت به فایل تراسفورم و اگر کسی مسلط به xsl نباشه این سریعتر جواب میده.

شما عجالتا اصل فایل xml و همینطور ساختار جدول نهایی (در قالب accdb) رو اینجا پیوست کنین
سه شنبه 15 آبان 1397, 10:22 صبح
ATA_TABRIZ

1 ضمیمه

نقل قول: تبدیل xml به table

نقل قول:

نوشته شده توسط mazoolagh

سلام و روز خوش - شما لطف دارین
اجازه بدین اول چند موضوع باز بشه:

1- با توجه به اینکه این فایل xml از روی دیتا شما ساخته میشه چه نیازی هست دوباره روند معکوس رو پیاده کنین؟

2- این نمونه که شما گذاشتین در واقع 3 جدول هست:
یک جدول HR که احتمالا منظورشون header بوده
یک جدول PH که هر رکوردش یک نسخه است
یک جدول MG که هر رکوردش یک سرویس یک نسخه است
اگر اشتباه میکنم تصحیح کنین

بین جدول PH و MG باید یک ارتباط یک به چند باشه (بر اساس SQ که احتمالا منظورشون sequence بوده و همون ID هست) - اگر هر دو رو در یک جدول بیارین باید دیتا تکراری نگه داری کنین؛ یعنی بازای هر رکورد MG باید تمام فیلدهای PH رو هم اضافه کنین که کلا کار نادرستی هست.

راه درست و اساسی این هست که اگر واقعا دسترسی به دیتا اصلی ندارین و فقط همین فایل xml رو دارین باید یک فایل xslt بسازین که موقع ایمپورت جدول (ها) رو بر اساس ساختار موردنیازتون بسازه که البته کار آسونی هم نیست!

البته پروسس مستقیم فایل و استخراج دیتا و بازنویسی اون هم هست که راه ساده تری هست نسبت به فایل تراسفورم و اگر کسی مسلط به xsl نباشه این سریعتر جواب میده.

شما عجالتا اصل فایل xml و همینطور ساختار جدول نهایی (در قالب accdb) رو اینجا پیوست کنین

با سلام و ممنون از توجه شما که همیشه منو شرمنده کردین
باید به عرض برسونم که :
1- با توجه باینکه قرار است اطلاعات دو تا مرکز با هم ادغام بشه و با یک کد یه xml ساخته بشه که از یک مرکز فقط فایل xml را داریم و دسترسی به بانک اطلاعاتی نداریم به همین جهت باید به شکل مهندسی معکوس کار کنیم اطلاعات xml اون مرکز را به جدول تبدیل و با اطلاعات خودمون ادغام بکنیم
2- طبق نظر شما سه جدول هستش و لی جدول اول HR داده های مشترک است که خودمان داریم و نیاز نیس خونده شود و طبق فرموده شما باید بین دو جدول دیگه ارتباط یک به چند ساخته شود که هنگام ایمپورت که شما فرموده بودید این کار عملی نیس و کاملا بدون ارتباط درس میشه
لذا به نظر بنده اگه بتونیم با کد نویسی اطلاعات را خونده و در دو جدول و یا حتی در یک جدول چون ریخته شود مراحل بعدی مثل کارهای گذشته است که با کمک شما انجام دادیم (‌برای هر نفر بیش از 5 خدمت قابل ارائه نیس )‌لذا جهت راحتی میشه از یک جدول هم استفاده کرد یا اگه دو جدول باشه یه کد ارتباطی مثل SQ باید داشته باشیم

فایل xml ‌به شکل همون بالایی میباشد
جداولی هم که میشه برای ذخیره داده های خونده شده استفاده کرد :
ضمیمه 149213
سه شنبه 15 آبان 1397, 18:25 عصر
ATA_TABRIZ

2 ضمیمه

نقل قول: تبدیل xml به table

ضمیمه 149217[ATTACH]149218[/ATTACH
دوتا فایل تکست که میشود با تغییر پسوند به xml تبدیل شوند
سه شنبه 15 آبان 1397, 18:28 عصر
ATA_TABRIZ

1 ضمیمه

نقل قول: تبدیل xml به table

اینم فایل دوم
ضمیمه 149219
شنبه 19 آبان 1397, 13:01 عصر
mazoolagh

نقل قول: تبدیل xml به table

تبدیل اطلاعات به شکل جدول totalxml برای ارائه شاید قابل قبول باشه ولی برای محاسبات و عملیات نمیشه استفاده کرد.
بهترین کار همون دو جدول جداگانه ولی مرتبط هست که کدشو براتون مینویسم و همینجا میگذارم.

Time ...
شنبه 19 آبان 1397, 16:20 عصر
ATA_TABRIZ

نقل قول: تبدیل xml به table

نقل قول:

نوشته شده توسط mazoolagh

تبدیل اطلاعات به شکل جدول totalxml برای ارائه شاید قابل قبول باشه ولی برای محاسبات و عملیات نمیشه استفاده کرد.
بهترین کار همون دو جدول جداگانه ولی مرتبط هست که کدشو براتون مینویسم و همینجا میگذارم.

Time ...

ممنون از محبتتون منتطر هستم

نقل قول: تبدیل xml به table

روتین زیر آدرس فایل رو میگیره (Source) و دو فایل xml میسازه ؛ یکی برای PHها و یکی MHها و در کنار خود برنامه ذخیره میکنه .
پسوند فایل اهمیتی نداره یعنی لازم نیست حتما xml باشه - فقط ساختار و محتوی مهم هست.

Sub Make_PH_MH(Source As String)

Dim Doc_In As New MSXML2.DOMDocument60

Dim Doc_PH As New MSXML2.DOMDocument60

Dim Doc_MH As New MSXML2.DOMDocument60

Dim NL_X As MSXML2.IXMLDOMNodeList

Dim NL_PH As MSXML2.IXMLDOMNodeList

Dim NL_MH As MSXML2.IXMLDOMNodeList

Dim N_X As MSXML2.IXMLDOMNode

Dim N_PH As MSXML2.IXMLDOMNode

Dim N_BY As MSXML2.IXMLDOMNode

Dim N_MH As MSXML2.IXMLDOMNode

Dim NPH_Out As MSXML2.IXMLDOMNode

Dim NMH_Out As MSXML2.IXMLDOMNode

Dim N_SQ As MSXML2.IXMLDOMNode

Dim N1 As MSXML2.IXMLDOMNode

Dim FileName As String

Dim FD As String

Dim p1, p2 As Integer

Set NPH_Out = Doc_PH.createNode("element", "PHS", "")

Doc_PH.appendChild NPH_Out

Set NMH_Out = Doc_MH.createNode("element", "MHS", "")

Doc_MH.appendChild NMH_Out

Doc_In.Load (Source)

FD = Doc_In.selectSingleNode("Y/HR/FD").Text

p1 = InStrRev(Source, "")

p2 = InStrRev(Source, ".")

FileName = Mid(Source, p1 + 1, p2 - p1 - 1) & "_" & Left(FD, 4)

Set NL_X = Doc_In.selectNodes("Y/X")

For Each N_X In NL_X

    Set N_PH = N_X.selectSingleNode("PH")

    NPH_Out.appendChild N_X.selectSingleNode("PH")

    Set N1 = N_PH.selectSingleNode("SQ")

    Set NL_MH = N_X.selectNodes("BY/MH")

    For Each N_MH In NL_MH

        Set N_SQ = Doc_MH.createNode("element", "SQ", "")

        N_SQ.Text = N1.Text

        N_MH.insertBefore N_SQ, N_MH.firstChild

        NMH_Out.appendChild N_MH

    Next

Next

Doc_PH.Save (CurrentProject.Path & "" & FileName & "_PH.xml")

Doc_MH.Save (CurrentProject.Path & "" & FileName & "_MH.xml")

End Sub

نمونه:

Make_PH_MH (CurrentProject.Path & "\nos11.txt")

برای پرهیز از سردرگمی اسم فایل ورودی و ماه و سال مربوط به دیتا در اسم فایل خروجی گنجانده شده.
نمونه خروجی:

nos11_9707_MH.xml

nos11_9707_PH.xml

دوشنبه 21 آبان 1397, 09:58 صبح
mazoolagh

4 ضمیمه

نقل قول: تبدیل xml به table

پس برای فایل nos11.txt با محتوای زیر:
ضمیمه 149255

فایل PH به این شکل خواهد بود:
ضمیمه 149256

و فایل MH هم به شکل زیر:
ضمیمه 149257

و بعد از ایمپورت کردن این دو فایل به اکسس نتیجه به این شکل خواهد بود:
ضمیمه 149258

نقل قول: تبدیل xml به table

نکته مهم این هست که اگر قرار باشه اطلاعات فایلهای مختلف (نامهای مختلف و تاریخهای مختلف) در کنار هم نگهداری بشن کد بالا جوابگو نیست چون SQ فقط در یک فایل یکتا است.
بنابراین لازم هست که خودمون یک فیلد یکتا بعنوان کلید برای PH بسازیم و رابطه رو هم بر اساس اون تعریف کنیم.

روتین زیر همین کار رو با اضافه کردن فیلد ID که ترکیب اسم فایل ورودی و تاریخ (سال وماه) و SQ هست و تضمین یکتا بودن داره انجام میده:

Sub Make_PH_MH_With_ID(Source As String)

Dim Doc_In As New MSXML2.DOMDocument60

Dim Doc_PH As New MSXML2.DOMDocument60

Dim Doc_MH As New MSXML2.DOMDocument60

Dim NL_X As MSXML2.IXMLDOMNodeList

Dim NL_PH As MSXML2.IXMLDOMNodeList

Dim NL_MH As MSXML2.IXMLDOMNodeList

Dim N_X As MSXML2.IXMLDOMNode

Dim N_PH As MSXML2.IXMLDOMNode

Dim N_BY As MSXML2.IXMLDOMNode

Dim N_MH As MSXML2.IXMLDOMNode

Dim NPH_Out As MSXML2.IXMLDOMNode

Dim NMH_Out As MSXML2.IXMLDOMNode

Dim N_SQ As MSXML2.IXMLDOMNode

Dim N1 As MSXML2.IXMLDOMNode

Dim N_ID As MSXML2.IXMLDOMNode

Dim FileName As String

Dim FD As String

Dim ID As String

Dim p1, p2 As Integer

Set NPH_Out = Doc_PH.createNode("element", "PHS", "")

Doc_PH.appendChild NPH_Out

Set NMH_Out = Doc_MH.createNode("element", "MHS", "")

Doc_MH.appendChild NMH_Out

Doc_In.Load (Source)

FD = Doc_In.selectSingleNode("Y/HR/FD").Text

p1 = InStrRev(Source, "")

p2 = InStrRev(Source, ".")

FileName = Mid(Source, p1 + 1, p2 - p1 - 1) & "_" & Left(FD, 4)

Set NL_X = Doc_In.selectNodes("Y/X")

For Each N_X In NL_X

    Set N_PH = N_X.selectSingleNode("PH")

    NPH_Out.appendChild N_X.selectSingleNode("PH")

    Set N1 = N_PH.selectSingleNode("SQ")

    Set NL_MH = N_X.selectNodes("BY/MH")

    Set N_ID = Doc_PH.createNode("element", "ID", "")

    N_ID.Text = FileName & "_" & N1.Text

    N_PH.insertBefore N_ID, N_PH.firstChild

    For Each N_MH In NL_MH

        Set N_SQ = Doc_MH.createNode("element", "ID", "")

        N_SQ.Text = N_ID.Text

        N_MH.insertBefore N_SQ, N_MH.firstChild

        NMH_Out.appendChild N_MH

    Next

Next

Doc_PH.Save (CurrentProject.Path & "" & FileName & "_PHx.xml")

Doc_MH.Save (CurrentProject.Path & "" & FileName & "_MHx.xml")

End Sub



برای جلوگیری از تداخل با نتیج روتین قبلی به اسم فایلهای خروجی یک x اضافه شده.

دوشنبه 21 آبان 1397, 10:09 صبح
mazoolagh

3 ضمیمه

نقل قول: تبدیل xml به table

حالا خروجیها به شکل زیر خواهند بود:
ضمیمه 149259

ضمیمه 149260

ضمیمه 149261
دوشنبه 21 آبان 1397, 10:11 صبح
mazoolagh

1 ضمیمه

نقل قول: تبدیل xml به table

برنامه همراه با نمونه های ورودی و خروجی
چهارشنبه 23 آبان 1397, 10:07 صبح
ATA_TABRIZ

نقل قول: تبدیل xml به table

ممنون از لطفتون
واقعا شرمنده محبتتون هستم
مطمئنا با اضافه نمودن یه کد که بتونه مستقیما به فایل مورد نظر ایمپورت بکنه عالی خواهد بود.
از لطف شما نهایت تشکر را دارم

اینم کد ایمپورت
Application.ImportXML CurrentProject.Path & "\NOS1.TXT", acStructureAndData

جناب Mazoolagh بی نهایت از لطف شما ممنون هستم