January 4, 2005 at 5:31 am
I have 8 Excel files that need to be imported each day into SQL Server. The Excel column layout is the same for all files. The files have three columns, with no header record.
The files are named:
Red_01of02
Red_02of02
Blue_01of02
Blue_02of02
White_01of02
White_02of02
Black_01of02
Black_02of02
One problem with the file is there is no key field, so I need to retain the same order in SQL as the Excel file. I'm assuming DTS does this. It would be nice to add an identity record as it imports.
What would be great would be to have the filename as a column in the import, that way I could identify which file the data was imported from (it is important for me to know the source of the data, later in the application).
There are days I might end up with a third file from the color group. Sometimes I might see Black_03of03 or a Red_03of03.
MY PROBLEM is I've never used DTS before (other than a manual routine), so this is somewhat confusing. I'd like to call this from a stored procedure if possible, and have no security concerns.
Thanks!
January 4, 2005 at 4:47 pm
I'm a hackboy. I can get some stuff done in SQL Server, but I'd probably have an easier time learning to speak German than understand DTS, LOL.
Actually, I could use bulk insert if I could get past one problem. My CSV data files are garbage, and I have no control over the vendor.
Lets say it is a 6 field table, here is what my data looks like:
,dog,,,$100.00,
,cat,small,,"$1,000,000.00",
,mouse,big,ugly,"$3,000.00",
The data has numerous null fields, the killer is the formatting of the money fields. If it is more than 1000 they quote it and send commas. Bulk insert lets you pick the delimeter, but does not let you indicate quoted text.
If I could get rid of the commas in the money field, I'd be in good shape. To add to the insanity, there are about 10 transaction types in each set of records, some have more than one money field, although I can identify the record type (you can't see that in my example).
,dog,,,$100.00,
,cat,small,,"$1000000.00",
,mouse,big,ugly,"$3000.00",
I should probably post this as a separate question, with a different header.
January 5, 2005 at 7:09 am
A great way to also the filename is to store it as a global variable, and then use an Active X task in the data pump to import that variable into the desired column.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply