Import .txt file to SQL

[bamundsen]

I have a table which is structured as so:

ID data1 data2
== ===== =====
1 sample00 sample
2 sample01 sample

ID is my PK. I have exported out the data from the table into excel and made changes to the data1 column. The table now looks as such:

ID data1 data2
== ===== =====
1 sample20 sample
2 sample21 sample

You will notice only the data1 column has changes. There are over 5000 records in this table, and the data1 value changed for all 5000 records. I have attempted an import using the DTS wizard to append data to the table. I created a .txt file from the excel spreadsheet. The .txt file has the ID field and the data1 field (I just cut out the data2 field since no changes were made). I made sure to check the "first row has column names" box. I have the "Enable Identity Insert" unchecked and "ignore" for the PK ID value selected. For some reason the changes are not reflected in the table. Am I missing something???? Thanks,

Brett

 

[dbomrrsm]

I would Truncate the table in SQL Server and then run an import to get the data back into the table from the text file.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[bamundsen]

I would truncate the table, but the .txt file is only effecting 1/4 of the rows in the table. I can't truncate because I would remove rows of data that do not need to be updated. One thought I have is to include an additional column in the import. I have a category column that I could use as an identifier. Should I include that in the import??

Here is how the table looks with the category column:

ID data1 data2 category
== ===== ===== =======
1 sample20 sample 250
2 sample21 sample 250
3 sample22 sample 251

ID3 would not need to be updated because the category id is 251. Hope this makes sense!

Thanks,

Brett

 

[dbomrrsm]

Just out of curiosity - why not do the update to the table within SQL Server is there a particular reason you took it out to excel to update it ?

Post back the rules of the update and we can help you to update it within SQL Server.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[bamundsen]

dbomrrsm:

Thank you for your replies! The reason I took the data out to excel is because I am modifying the description value for some products in my products table. I export out the entire products table to excel, remove the products I don't need to modify, make the necessary changes, and save the spreadsheet as a .txt file. The .txt file consists of the PK id value, the category value, and the modified cdescription value. I do not know of any other way to import and append these changes. I hope I am not making this more confusing. Below is an actual snapshot of data prior and post modifcation:

prior to change:

id cdescription category
== ======== =======
1 1999 F-150 (Details: Long Bed) 250
2 1999 F-250 (Details: Long Bed) 250
3 1999 F-350 (Details: Long Bed) 250

after change:

id cdescription category
== ======== =======
1 1999 F-150 250
2 1999 F-250 250
3 1999 F-350 250

Please keep in mind that there are many products I am not making changes to, and those products are not in the .txt file. Also, the only change that is being made is to the cdescription value. I assumed that if I appended the changes using the DTS Import wizard, and if I had the PK in the .txt file (and chose ignore for the PK Value), the changes would append. Thanks,

Brett

 

[bamundsen]

Should I set up a dummy table in SQL that is a duplicate of my products table? Then truncate the dummy table. Import the records I need updated into the dummy table and use an UPDATE query to append the changes. Something like:

update products
set cdescription = (select cdescription from products_dummy
where products.id = products_dummy.id)

 

[dbomrrsm]

Although it may not be as simple as it looks would this not work

UPDATE YOURTABLE
SET cdescription = substring(cdescription,1,10)
where cdescription like'%(Details: Long Bed)%')

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[bamundsen]

DBomrrsm:

The (Details: Long Bed) was just an example. There are over 5000 products I am updating, and 80% of them have a different cdescription value that I have modified. Would the above update query I wrote work? Thanks,

Brett

 

[dbomrrsm]

looks ok to me - can you copy your tables and do an update on the copies to check the update works before running it in prod - just to be on the safe side - but the update you suggest looks fine !

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Question on your PK. Is it an identity value?

If so, the DTS Import/Export Wizard allows appending to a table. Hit the elipses button (...) to do the transformation and where the row header says "Source", click inside the ID box and scroll up looking for the word <ignore>. This will enable you to append records (this will not update current ones) and allow the PK to auto generate new ID numbers so that there isn't a conflict of the PK restraint.

I only add this because I'm still confused as to whether you are simply wanting to update the records that changed or just append the changed records to all the old ones.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!