Help with DTS and ActiveX Script to import text file to a table

Help with DTS and ActiveX Script to import text file to a table

Post by Andre Andraus Filh » Sun, 13 May 2001 03:57:29



GOAL:  From an input text file with 80 occurrencies for fields [cod_merc]
and [qtd_merc] , I need to generate the table tb_unid_comercializada, one
line for each occurency where [cod_merc] is not spaces and [qtd_merc] is not
zeros.

Also, I need to test each field of the input file to check if its content
is according to its datatype. If  not, I need to convert it.
For example, for [valor_fob]  = '00002356840' to [valor_fob]  = 23,568.40
How can I do this conversions  and  checks??

Input Text File Layout:

 [num_cnpj_cpf]                  [alpha]   (14)
 [num_li]                             [numeric] (10)
 [num_di]                            [numeric] (10)
 [data_desembaraco_di]     [smalldatetime]
 [cod_ua_reg_di]                [numeric] (7)
 [sigla_uf_ua_reg_di]         [char]    (2)
 [cod_ua_zona_pri]            [numeric] (7)
 [sigla_uf_ua_zona_pri]     [char]    (2)
 [cod_ua_zona_sec]           [numeric] (7)
 [sigla_uf_ua_zona_sec]    [char]    (2)
 [cod_pais_orig]                [numeric] (3)
 [cod_pais_aquis]              [numeric] (3)
 [cod_ncm]                         [numeric] (8)
 [peso_liq]                         [numeric] (10,5)
 [valor_fob]                        [numeric] (9,2)
 [valor_frete]                      [numeric] (9,2)
 [valor_seguro]                   [numeric] (9,2)
 [valor_cif]                         [numeric] (9,2)
 [valor_tributavel_ii]          [numeric] (13,2)
 [valor_tributavel_ipi]        [numeric] (13,2)
 [valor_ii_arecolher]          [numeric] (13,2)
 [valor_ipi_arecolher]        [numeric] (13,2)
 [valor_dump_arecolher]    [numeric] (13,2)
 [cod_regime_tributacao]    [char] (1)
 [natureza_juridica]             [char] (3)
 [cod_merc]                         [char] (1600)  total lenght     <==
occurs 80 times  x  20 bytes.
 [qtd_merc]                         [numeric] (1120) total lenght <==
occurs 80 times  x  (9,5) numeric

Table tb_unid_comercializada:

 [num_cnpj_cpf]               [char] (14)
 [num_di]                          [numeric] (10)
 [cod_ncm]                       [numeric] (8)
 [cod_unid_comerc]         [char] (20)
 [qtd_unid_comerc]          [numeric] (9,5)

MY SOLUTION:

But needs some adjustments in ActiveX Script, because it seems to process
all 80 occurencies, but generates just one entry on the table
tb_unid_comercializada, for each input line, with the values from last
occurency (80th) .

In DTS, I have done a datapump from the text file to the database using
ActiveX script , with the following code:

Function Main()
               Main = DTSTransformStat_OK
strCod = DTSSource("cod_merc")
strQtd = DTSSource("qtd_merc")
w_cnt = 1
w_icod = 1
w_iqtd = 1

While w_cnt > 80
    cCod = Mid(strCod, w_icod, 20)
    cQtd = Mid(strQtd, w_iqtd, 14)

    If (cCod <> "                    ") Then
       DTSDestination("num_cnpj_cpf") = DTSSource("num_cnpj_cpf")
       DTSDestination("num_di") = DTSSource("num_di")
       DTSDestination("cod_ncm") = DTSSource("cod_ncm")
       DTSDestination("cod_unid_comerc") = cCod
       DTSDestination("qtd_unid_comerc") = cQtd
   End If
       Transform = DTSTransformStat_OK
   w_icod = w_icod + 20
   w_iqtd = w_iqtd + 14
   w_ctn = w_ctn + 1
Wend
End Function

QUESTIONS:

1) How to correct the ActiveX script , so that it will generate one line in
the table, for each occurency where [cod_merc](w_ctn) is not spaces and
[qtd_merc]](w_ctn) is not zeros.

2) Is there any better way to do this ??

3) How can I check the fields datatype and made conversions between
datatypes??

Thanks for your time and attention,

Best Regards,
Andr Andraus Filho

 
 
 

Help with DTS and ActiveX Script to import text file to a table

Post by Darren Gree » Tue, 15 May 2001 02:54:03




Quote:>GOAL:  From an input text file with 80 occurrencies for fields [cod_merc]
>and [qtd_merc] , I need to generate the table tb_unid_comercializada, one
>line for each occurency where [cod_merc] is not spaces and [qtd_merc] is not
>zeros.

>Also, I need to test each field of the input file to check if its content
>is according to its datatype. If  not, I need to convert it.
>For example, for [valor_fob]  = '00002356840' to [valor_fob]  = 23,568.40
>How can I do this conversions  and  checks??

<Snip>

>QUESTIONS:

>1) How to correct the ActiveX script , so that it will generate one line in
>the table, for each occurency where [cod_merc](w_ctn) is not spaces and
>[qtd_merc]](w_ctn) is not zeros.

>2) Is there any better way to do this ??

>3) How can I check the fields datatype and made conversions between
>datatypes??

>Thanks for your time and attention,

>Best Regards,
>Andr Andraus Filho

Andr,

Your problem at present is that your a looping within a single
transform. You need to let DTS move onto the next transform so you can
perform the next "insert". To do this you can use the transformation
status DTSTransformStat_SkipFetch, which as the name implies will not
fetch a new record. This is just what you want because you need to skip
80 times whilst you insert each new row from the original.

To keep track of where you are in the loop environment use some global
variables.

For a full example of transforming columns into rows here, see the FAQ
File name "dtscolstorows.txt" at http://www.sqlserverfaq.com

and another example at-
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7836&pg=2

Not sure what you are trying to do with the type issue, but perhaps
IsNumeric is what you want followed by x/100?

--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

 
 
 

Help with DTS and ActiveX Script to import text file to a table

Post by AndrĂ© Andraus Filh » Thu, 17 May 2001 05:33:49


Hi Darren,

Thank you for your help and attention.

Based on the information that you gave me , Ive wrote the ActiveX script
below, to convert columns from an input text file into rows.

Function Main()

DTSGlobalVariables("ColumnCounter") = DTSGlobalVariables("ColumnCounter") +
1

If           DTSGlobalVariables("ColumnCounter") <= 80 Then
             Col_Codigo = 25 + DTSGlobalVariables("ColumnCounter")
             Col_Quantid = 105 +  DTSGlobalVariables("ColumnCounter")
             If Col_Codigo < 100 Then
                 strCol_Codigo = "Col0" & Col_Codigo
             Else
                 strCol_Codigo = "Col" & Col_Codigo
             End if

             If IsNull(DTSSource(strCol_Codigo).Value) or
IsEmpty(DTSSource(strCol_Codigo).Value) Then
                     DTSGlobalVariables("ColumnCounter") = 0
                     Main = DTSTransformStat_SkipInsert
             Else
                      DTSDestination("num_cnpj_cpf") = DTSSource("Col001")
                      DTSDestination("num_di") = DTSSource("Col003")
                      DTSDestination("cod_ncm") = DTSSource("Col005")
                      DTSDestination("occurs") = Col_Codigo
                      DTSDestination("cod_unid_comercializada") =
DTSSource(strCol_Codigo)
                      DTSDestination("qtd_unid_comercializada") =
DTSSource("Col" & Col_Quantid)
                      Main = DTSTransformStat_SkipFetch
             End If
Else
             DTSGlobalVariables("ColumnCounter") = 0
             Main = DTSTransformStat_SkipInsert
End If

End Function

==========================================

This script is converting ALL columns to rows.

The problem is that I NEED TO CONVERT JUST the columns where
DTSSource(strCol_Codigo) is filled with a product name.
Other DTSSource(strCol_Codigo) columns that are EMPTY,NULL or SPACES,
doesnt need to generate a row in the table.
I have tought that the code below, will handle this situation, but it seems
that is not working this way.
What is wrong with it ??

         If IsNull(DTSSource(strCol_Codigo).Value) or
IsEmpty(DTSSource(strCol_Codigo).Value) Then
               DTSGlobalVariables("ColumnCounter") = 0
               Main = DTSTransformStat_SkipInsert

How can I do this ??
Where do i need to change my code ??

Thank you for any light you could give me.

Andr Andraus Filho.




> >GOAL:  From an input text file with 80 occurrencies for fields [cod_merc]
> >and [qtd_merc] , I need to generate the table tb_unid_comercializada, one
> >line for each occurency where [cod_merc] is not spaces and [qtd_merc] is
not
> >zeros.

> >Also, I need to test each field of the input file to check if its
content
> >is according to its datatype. If  not, I need to convert it.
> >For example, for [valor_fob]  = '00002356840' to [valor_fob]  = 23,568.40
> >How can I do this conversions  and  checks??

> <Snip>

> >QUESTIONS:

> >1) How to correct the ActiveX script , so that it will generate one line
in
> >the table, for each occurency where [cod_merc](w_ctn) is not spaces and
> >[qtd_merc]](w_ctn) is not zeros.

> >2) Is there any better way to do this ??

> >3) How can I check the fields datatype and made conversions between
> >datatypes??

> >Thanks for your time and attention,

> >Best Regards,
> >Andr Andraus Filho

> Andr,

> Your problem at present is that your a looping within a single
> transform. You need to let DTS move onto the next transform so you can
> perform the next "insert". To do this you can use the transformation
> status DTSTransformStat_SkipFetch, which as the name implies will not
> fetch a new record. This is just what you want because you need to skip
> 80 times whilst you insert each new row from the original.

> To keep track of where you are in the loop environment use some global
> variables.

> For a full example of transforming columns into rows here, see the FAQ
> File name "dtscolstorows.txt" at http://www.sqlserverfaq.com

> and another example at-
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7836&pg=2

> Not sure what you are trying to do with the type issue, but perhaps
> IsNumeric is what you want followed by x/100?

> --
> Darren Green
> SQL/DTS - http://www.swynk.com/friends/green/

 
 
 

Help with DTS and ActiveX Script to import text file to a table

Post by Darren Gree » Sun, 20 May 2001 19:35:36




Quote:>Hi Darren,

>Thank you for your help and attention.

>Based on the information that you gave me , Ive wrote the ActiveX script
>below, to convert columns from an input text file into rows.

>Function Main()

>DTSGlobalVariables("ColumnCounter") = DTSGlobalVariables("ColumnCounter") +
>1

>If           DTSGlobalVariables("ColumnCounter") <= 80 Then
>             Col_Codigo = 25 + DTSGlobalVariables("ColumnCounter")
>             Col_Quantid = 105 +  DTSGlobalVariables("ColumnCounter")
>             If Col_Codigo < 100 Then
>                 strCol_Codigo = "Col0" & Col_Codigo
>             Else
>                 strCol_Codigo = "Col" & Col_Codigo
>             End if

>             If IsNull(DTSSource(strCol_Codigo).Value) or
>IsEmpty(DTSSource(strCol_Codigo).Value) Then
>                     DTSGlobalVariables("ColumnCounter") = 0
>                     Main = DTSTransformStat_SkipInsert

Two things are wrong here I think. Whilst you don't insert the data, you
do move onto the next row. What happens if you have one blank column at
number 40, but onwards the data is fine? This will be lost, so do not
reset the counter, and don't move rows-

If IsNull(DTSSource(strCol_Codigo).Value) Then
 Main = DTSTransformStat_SkipInsert+DTSTransformStat_SkipFetch
Else

<snip>

Quote:

>The problem is that I NEED TO CONVERT JUST the columns where
>DTSSource(strCol_Codigo) is filled with a product name.
>Other DTSSource(strCol_Codigo) columns that are EMPTY,NULL or SPACES,
>doesnt need to generate a row in the table.
>I have tought that the code below, will handle this situation, but it seems
>that is not working this way.
>What is wrong with it ??

It would help if you describe the problem in more detail, but I hope the
answer above solves it.

--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

 
 
 

1. ActiveX Script Text box missing from ActiveX Script Task Properties

I have a user that, when they open an ActiveX Task to view the properties
(ie the code) the ActiveX Script Box (the window you type the code into) is
missing.  The tab box with language elements et al is there, but the code
box itself is just gone.  We have tried everything - resize, dragging
everything, whatever.  It's like the OCX that runs that box has phreaked
out.

Thoughts?

-------
William A. Sempf MCP, CIBS, CIW
POINT Senior Technology Consultant
Wrox Press .Net Author Team

2. Long Transactions

3. Import multiple text files via DTS - example script?

4. identity column

5. Importing a Text File via DTS and adding the File Name as a column via DTS

6. replication

7. Table Update Via Text File (DTS, Stored Proc, ActiveX, VBScript)

8. case statement problem

9. DTS launched from SP failing when importing from a text file to a SQL Server Table

10. Importing text files into SQL Server 2000 Database using DTS Import/Export Wizard

11. DTS Import Error When Importing Text File

12. Help Importing a text file with DTS Wizard