تبدیل xml به table

[ATA_TABRIZ]

با سلام و تشکر از دوستان و مدیران گروه
کسی در مورد تبدیل فایل xml یا تکست به جدول با استفاده از vba را کد نویس کرده یا نه لطفا در صورت داشتن اطلاعات راهنمایی نماییید
ممنون از لطفتون

[mazoolagh]

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

[ATA_TABRIZ]

هم میتونین از منو 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 و ذخیره در یک رکورد جدول را دارم

مجددا از توجه شما نهایت تشکر را دارم

[mazoolagh]

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

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

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

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

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

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

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

[ATA_TABRIZ]

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

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

[ATA_TABRIZ]

149217[ATTACH]149218[/ATTACH
دوتا فایل تکست که میشود با تغییر پسوند به xml تبدیل شوند

[ATA_TABRIZ]

اینم فایل دوم
149219

[mazoolagh]

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

Time ...

[ATA_TABRIZ]

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

Time ...

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

[mazoolagh]

روتین زیر آدرس فایل رو میگیره (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

[mazoolagh]

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

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

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

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

[mazoolagh]

نکته مهم این هست که اگر قرار باشه اطلاعات فایلهای مختلف (نامهای مختلف و تاریخهای مختلف) در کنار هم نگهداری بشن کد بالا جوابگو نیست چون 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 اضافه شده.

[mazoolagh]

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

149260

149261

[mazoolagh]

برنامه همراه با نمونه های ورودی و خروجی

[ATA_TABRIZ]

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

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

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