קליטת קובץ לטבלה מרובת עמודות

20/02/2014

אין תגובות

יש לנו קובץ ואנחנו מעוניינים לקלוט אותו לטבלה תוך פירוקו לעמודות (מקובל שהעמודות בקובץ מופרדות בפסיקים).
יש לכך שלל פתרונות, והמקובל או המיידי הוא להשתמש באשף המובנה: קליק ימני על הדטבייס אליו רוצים לייבא, Tasks, ו- Import Data. האשף יודע לספק תצוגה מקדימה של הנתונים, לבחור בטבלת יעד, ועוד. דא עקא, שלאחר שבחרנו, והגדרנו ואישרנו; הריצה מתחילה ונופלת באמצע באמתלאות שונות: Page Code, הגדרות שפה, תווי סיום שורה, אי התאמה של חלק מהנתונים וכו'; וידינו קצרו מלהושיע.. למי יש כח עכשיו להתחיל להתרוצץ בכל מיני פורומים ולהריץ שאילתות בגוגל כשיש לתקתק את המשימה?
כשאני נמצא במצב כזה אני מנסה קודם כל לייבא את הנתונים לטבלת ביניים: או טבלה בת עמודה אחת שכל שורה מהקובץ תיקלט בה כשורה בטבלה, או טבלה שכל עמודותיה טקסטואליות כך שלא "תעשה עניין" מנתונים לא חוקיים בעמודות שנועדו להכיל תאריכים או מספרים וכו', וכעת כשהנתונים בדטבייס – הטיפול באמצעות TSQL הרבה יותר פשוט: מפרקים את השורות לעמודות, בודקים שהנתונים חוקיים, קולטים לטבלת היעד את הנתונים התקינים, ומטפלים בשגויים.

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

Create    Table #T1(Col Varchar(Max));

Create    Table #T2(Col1 Varchar(10),

                Col2 Varchar(20),

                Col3 Varchar(20),

                ColN Decimal (5,2),

                DolD Date);

 

Insert

Into    #T1

Values    ('a1,b1,C1,123.45,20140220'),

        (',,,0,20130201'),

        ('aaa3,bbb3,Ccc3,67,20142020'),

        ('aaaa4,bbbb4,Cccc4,X,20140101');

 

Select * From #T1;

image

בשורה 3 נשתל בזדון תאריך לא חוקי,
ובשורה 4 מספר לא חוקי.
כיצד מפרקים את השורות? הפונקציה CharIndex מאפשרת למצוא את הפסיק הראשון,
אלא שכדי למצוא את הפסיק השני יש להתחיל לחפש אותו לאחר הראשון,
וכדי לעשות זאת יש להשתמש בפקודות מקוננות מסובכות או לקנן את השאילתות זו בתוך זו, או לשרשר שליפות CTE, או הכי פשוט – לטעמי – להשתמש באופרטור Cross Apply:

Select    *,

        SubString(Col,1,CA1.S1-1) Col1,

        SubString(Col,CA1.S1+1,CA2.S2-CA1.S1-1) Col2,

        SubString(Col,CA2.S2+1,CA3.S3-CA2.S2-1) Col3,

        SubString(Col,CA3.S3+1,CA4.S4-CA3.S3-1) Col4,

        SubString(Col,CA4.S4+1,CA5.S5-CA4.S4-1) Col5

From    #T1

Cross Apply (Select CharIndex(',',Col) S1) CA1

Cross Apply (Select CharIndex(',',Col,CA1.S1+1) S2) CA2

Cross Apply (Select CharIndex(',',Col,CA2.S2+1) S3) CA3

Cross Apply (Select CharIndex(',',Col,CA3.S3+1) S4) CA4

Cross Apply (Select Len(Col)+1 S5) CA5;

image

השיטה די ברורה, כך אני מקווה, ו-5 העמודות (שכנגדן יש 5 Cross Apply) מספיקות כדי להבין את הרעיון.
כדאי לשים לב של Cross Apply תלוי בזה שלפניו,
וכל אחד מחזיר את מיקום הפסיק הבא לאחר זה שמצא קודמו, חוץ מהאחרון שמחזיר את אורך המחרוזת + 1 (מיקומו של הפסיק האחרון לו הוא היה מתווסף).

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

With    T As

(Select    SubString(Col,1,CA1.S1-1) Col1,

        SubString(Col,CA1.S1+1,CA2.S2-CA1.S1-1) Col2,

        SubString(Col,CA2.S2+1,CA3.S3-CA2.S2-1) Col3,

        SubString(Col,CA3.S3+1,CA4.S4-CA3.S3-1) ColN,

        SubString(Col,CA4.S4+1,CA5.S5-CA4.S4-1) ColD

From    #T1

Cross Apply (Select CharIndex(',',Col) S1) CA1

Cross Apply (Select CharIndex(',',Col,CA1.S1+1) S2) CA2

Cross Apply (Select CharIndex(',',Col,CA2.S2+1) S3) CA3

Cross Apply (Select CharIndex(',',Col,CA3.S3+1) S4) CA4

Cross Apply (Select Len(Col)+1 S5) CA5)

Select    *,IsDate(ColD)

From    T

Where    IsNumeric(ColN)=1

        And IsDate(ColD)=1;

image

באופן דומה ניתן להציג רק את הנתונים השגויים.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *