none
How to edit the transformations on import so that it knows which columns coming out of the text file map to which columns

    Question

  • Hello;

     

    I have table schema and text data file. After create the table, and then import the text data file. However, the text file always overwrite the table schema.  In the DTS import/export wizard, after choose Comma on specifiy column delimiter, all the column name changed to Col001, Col002, Col003 etc.

     
    How could I using existing table schema to import the text file?

    Once setting up the destination table, how to edit the transformations on import so that it knows which columns coming out of the text file map to which columns in the SQL Server database table.   I am using sqlserver 2000.

     

    Thanks


     
    Thanks,


     

     

    Monday, June 02, 2008 7:47 PM

All replies

  • Hi snow,  does your first row have the column names in it.  if so you can check the box that says "First row has column names".  If the column names match your table column names, then the transformation usually aligns them properly.  But you can always check by going to the Select Source Tables and Views section of the wizard and clicking on the button in the Transformation column. 

     

    Hope this helps.
    Monday, June 02, 2008 8:58 PM
  • Hello:

     

    Thank you very much for the reply. After run table schema DDL, there were columns. Then I clicked import data, select file, choose first row has column names, go to next bottom. There is a wizard: specify column delimiter. If I choose comma, I got error message:

     Error Source: Microsoft data transformation services flat file rowset provider.

    Error description: Duplicate column name '1'.

    Context: Error calling OpenRowset on the provider.

     

    If I choose tab, I got error message:

     Error Source: Microsoft data transformation services flat file rowset provider.

    Error description: Invalid delimited data: text qualifier must be followed by a column delimiter(except the last column).

    Context: Error calling OpenRowset on the provider.

     

    If I choose semicolon, I got the error message:

     Error Source: Microsoft data transformation services flat file rowset provider.

    Error description: Invalid delimited data: text qualifier must be followed by a column delimiter(except the last column).

    Context: Error calling OpenRowset on the provider.

     

    The only left is Other. How to specify other? Or what am I missing here?

     

    Thank you very much for your help.

     

    S

     

    Tuesday, June 03, 2008 2:33 PM
  • How is your data file separated?  Does it look like

     

    Snow, Tom, She, Him

    or

    "Snow", "Tom", "She", "Him"

    or

    Snow; Tom; She; Him

    or

    "Snow"; "Tom"; "She"; "Him"

    or

    Snow | Tom | She | Him

    or

    "Snow" | "Tom" | "She" | "Him"

    etc...

    However your file is separated is how you need to break up your columns.  The first 2 or 3 lines from your file would help.  Below are some articles that might help you work through this problem.

     

    http://www.databasejournal.com/features/mssql/article.php/3580216

     

    http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

    Tuesday, June 03, 2008 3:07 PM
  •  

    Hello:

    Thanks for the reply. My text file is separated by common, but inside the field has common too.
    1, "Name",   "Sign up,......,....,...."  1, 2,
    2, "Title",  "curent/file.doc         "  0,  2

    The flat file did not contain the column headers on the first line. Why choose common did not work?

    Thanks

    Tuesday, June 03, 2008 4:42 PM
  • In your above example, if you select the radio button that says "Delimited. The columns are separated by any character(s)"  You should see the drop down, Text qualifier, become enabled.  Change the drop down to say Double Quote{"}.  Then hit next.  When you select the radio button for Comma, you should see your columns separate correctly.
    Tuesday, June 03, 2008 6:34 PM
  • Hello:

     

    Thanks for the reply.

     

    After run table schema DDL, I could see the table create successfully. I click the import data on the table. Selecting text file as data source;  selecting the radio button that says "Delimited. The columns are separated by any character(s)" ;

    File type: ansi

    Row delimiter: {CR}{LF}

    Test qualifer: Double Quote{"}

    Skip rows: 0

    First row has column names: checked

     

    Click next

    Then I got the error message:

     

    Error Source: Microsoft data transformation services flat file rowset provider.

    Error description: Duplicate column name '1'.

    Context: Error calling OpenRowset on the provider.

     

    If I did not check First row has column names. I did not get the error message, but from the preview I could see all column name changed to Col001, Col002, Col003.....

     

    How to fix it?

     

    Thank you very much for the help.

     

    S

    Tuesday, June 03, 2008 7:26 PM
  • That would mean that your first row, the headers, has more than one 1.  I just now noticed from your example that you wrote:

     

    1, "Name",   "Sign up,......,....,...."  1, 2,
    2, "Title",  "curent/file.doc         "  0,  2

     

    The wizard is trying to make the first row, 1, "Name",   "Sign up,......,....,...."  1, 2,..., your column headers.  So they would need to be unique.

     

    If you want you column headers to be "Name", "Title", etc... you would need to transpose your information.  Does this make sense.  The current error you are getting is because of the red ones above.  Each column name needs to be distinct. 

     

    either 1, "Name",   "Sign up,......,....,...."  1a, 2,

    or uncheck the First row has column names:  and let the wizard randomly assign the column names.
    Tuesday, June 03, 2008 7:34 PM
  • Hello:

     

    Thanks for the quick response.

     

    I have the file:

     

    1, "Book",   "information,......,....,...."  0,  0, 6
    34, "Title",  "Link/name.doc         "      1  0,  26

     

    It did not work either. Is it the same reason? I could not change any content of the file because it is database information. If let the wizard randomly assign the column names, all column name will be changed col001, col002, col003 etc....

     

    It is different from the table schema DDL. The wizard created the table schema again. Then the error showed: the table already exit, can not create the table.

    How to fix it?

     

    Your help is highly appreciated.

     

     

    Tuesday, June 03, 2008 7:50 PM
  • You are welcome.  You can go into the Transformation section, by clicking the button, and then check the option: Drop and recreate destination table

    Tuesday, June 03, 2008 7:56 PM
  • Hello:

     

    Thanks for the reply. If I click Drop and recreate destination table. The new table was created, but all columns changed to be col001, col002, col003 which is totally different from the one supposed to be.

     

    Thanks

     

    Tuesday, June 03, 2008 8:37 PM
  • If you have the table already created then you need to select that table as your Destination in the Select Source Tables and Views section of the wizard.  After you select your existing table, click on the Transformation button.  This will open up Column Mappings and Transformations.  You should see your text file columns (Col001, Col002, etc...) and you table columns. 

     

    From here you can either delete the existing rows and insert your new ones from the text file or you can append to existing records.  Once you map your text file columns to the correct columns in your destination table, you should be good to go.

    Tuesday, June 03, 2008 9:19 PM
  •  

    Hello;

     

    After I have the table created, then choose import data wizard, The wizard showed up: choose a data source, I choose data source : text file, then browse the file to be used...... It come same problem I described before...... finally the wizard showed up: choose a destination. I choose the destination table.

     

    There is no way to select destination table first from the DTS wizard.

     

    Thanks

     

     

    Tuesday, June 03, 2008 9:45 PM
  • If you open up Enterprise Manager, select your database, and then right, you can select Task and Import Data.  You can do the above steps.  At the end of the wizard, it will give you the option to save the package.  If you check this, it will save your package as a DTS package that you can use over.  It might be easier to do what you need to this way.
    Wednesday, June 04, 2008 1:21 PM