הבלוג של גרי רשף

29/06/2010

קליטת קבצי טקסט לטבלה

Filed under: Uncategorized — תגיות: , , , , , , — גרי רשף @ 12:46

נתחיל מהמקרה הפשוט ביותר: נתון קובץ טקסט עם שמות עובדים אותם יש לקלוט, ונשמור אותו בתור C:\Tmp\TxtFile1.txt:

Avi
Batya
Carmel
Dana

ניצור טבלה מתאימה:

Use tempdb;

Go

If Object_Id('T_Tmp1') Is Not Null Drop Table T_Tmp1;

Go

Create Table T_Tmp1(Txt Varchar(Max));

Go

ונייבא את הנתונים מהקובץ לתוכה:

Bulk Insert T_Tmp1 From 'C:\Tmp\TxtFile1.txt';

Go

מה קורה אם בטבלה יש עוד עמודות מלבד זו שלתוכה יש לייבא את הנתונים?

ניצור טבלה שכוללת מלבד עמודה Txt כנ"ל גם עמודת מספור אוטומטי ועמודת תאריך (עם תאריך שוטף כברירת מחדל) שמתמלאות מאליהן:

If Object_Id('T_Tmp2') Is Not Null Drop Table T_Tmp2;

Go

Create Table T_Tmp2(Mone Int Identity,

                Txt Varchar(Max),

                Taarih DateTime Default Getdate());

Go

פתרון אפשרי יכול להיות יצירת View בעל עמודה אחת – Txt – שלתוכו ייובא הקובץ:

Create View V_Tmp As

Select Txt From T_Tmp2;

Go

Bulk Insert V_Tmp From 'C:\Tmp\TxtFile1.txt';

Go

כמובן שבפעמים הבאות אין צורך ליצור את ה-View מחדש..

פתרון חלופי יכול להוות שימוש בפקודת xp_cmdshell באופן הבא:

Insert Into T_Tmp2(Txt)

Exec xp_cmdshell 'Type C:\Tmp\TxtFile1.txt';

Go

כעת נפנה למקרה בו גם בקובץ הטקסט יש מספר עמודות: עמודת מספר אישי ועמודת שם עובד,

כדי ש"יהיה מעניין" נוסיף שורות רווח בין שורות הנתונים – אותן יש לפלטר,

ואת כל זה נשמור בתור C:\Tmp\TxtFile3.txt:

1,Avi

2,Batya

3,Carmel

4,Dana

לקובץ ניצור טבלה מתאימה שתכלול עמודות ID ו-Txt עבור הנתונים שבקובץ,

ועמודות Mone ו-Taarih שמתמלאות מאליהן כמו בדוגמאות הקודמות:

If Object_Id('T_Tmp3') Is Not Null Drop Table T_Tmp3;

Go

Create Table T_Tmp3(Mone Int Identity,

                ID Int,

                Txt Varchar(Max),

                Taarih DateTime Default Getdate());

Go

ליבוא הקובץ ניצור קובץ פורמט קבוע שיכלול מידע לגבי מבנה קובץ הנתונים (שאמור להשתנות בכל פעם),

ושנשמור אותו בתור C:\Tmp\TxtFile3.xml:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8000"/>

</RECORD>

<ROW>

<COLUMN SOURCE="1" NAME="ID" xsi:type="SQLSMALLINT"/>

<COLUMN SOURCE="2" NAME="Txt" xsi:type="SQLNVARCHAR"/>

</ROW>

</BCPFORMAT>

ונקלוט את הקובץ כך:

Insert Into T_Tmp3(ID,Txt)

Select    ID,

        Txt

From    OpenRowset(Bulk 'C:\Tmp\TxtFile3.txt',FormatFile='C:\Tmp\TxtFile3.xml') T

Where    Txt Is Not Null;

Go

כדאי לשים לב שדרך זו – ללא פקודת ה-Insert לתוך הטבלה – יכולה להיות דרך טובה לשליפת נתונים מקבצי טקסט ישירות לדוחות,

או אפילו תוך ביצוע Join עם טבלאות אחרות.

מודעות פרסומת

7 תגובות »

  1. […] על השימוש ב-Bulk Insert ודרכים אחרות לקליטת קבצי טקסט – בפוסט הזה. עוד דוגמה: אנחנו רוצים לדעת כמה שורות יש בכל View בשליפה […]

    פינגבאק של שירשור ערכים מטבלה - גרי רשף — 19/01/2012 @ 19:20

  2. […] מידע נוסף על השימוש ב-Bulk Insert ודרכים אחרות לקליטת קבצי טקסט – בפוסט הזה. […]

    פינגבאק של שירשור ערכים מטבלה « הבלוג של גרי רשף — 02/08/2010 @ 21:22

  3. […] בשיטה זו לקליטת תוכן של קובץ לתוך טבלה ניתן לראות בפוסט קודם שלי (יש לחפש שם את […]

    פינגבאק של הפרוצדורה xp_cmdshell « הבלוג של גרי רשף — 01/07/2010 @ 18:38

  4. צודק טעות שלי
    התבלבלתי. BULK INSERT אפשר
    לכתוב הייתה לי בעיה, ורק לתיקיה זה נפתר ללא צורך לשנות הרשאות כלשהן.

    תגובה של פלג — 30/06/2010 @ 14:06

  5. דרך אגב מאמר מצויין
    לא הכרתי אפשרות אחרת פרט ל BCP

    תגובה של פלג — 29/06/2010 @ 21:27

  6. 2 הערות חשובות :
    1) בד"כ לא ניתן לבצע BULK ישירות מ C או מ D חייבים תיקייה
    (כנראה עניין של הרשאות קורה ב 2003/2008).
    2)אם עובדים עם xp_cmdshell
    צריך לעשות
    EXEC master.dbo.sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC master.dbo.sp_configure 'xp_cmdshell', 1

    RECONFIGURE
    3)מה קורה כשאתה רוצה לטעון XML ולמפות את זה לעמודות( כמו שעשית בדוגמא האחרונה שהשתמשת במיפוי) אבל יש לך יותר מ XML אחד בקובץ?

    תגובה של פלג — 29/06/2010 @ 15:02

    • פלג- תודה רבה!

      פרוצדורת המערכת xp_cmdshell חסומה כברירת מחדל מכיוון שהיא יכולה לשמש כפרצה הקוראת לגנב..
      זו הסיבה שיש לקנפג אותה, כפי שציינת.
      בהזדמנות אולי אכתוב על כך.

      לגבי XML- שאלה טובה, מעולם לא ניסיתי..

      Bulk Insert ניתן בהחלט לבצע מ-C או D..
      יכול להיות שניסית לפנות מהעמדה שלך ל-C או ל-D המקומי?
      הפקודה פונה לכוננים של השרת עצמו ולא של עמדת המפתח!

      תגובה של גרי רשף — 30/06/2010 @ 14:03


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: