How can I import data to SQL from CSV or XLS automatically incrementing a string field based on current records in DB?

Question

I need to import data from Excel into a SQL 2000 db.

I need to import 6 fields from the worksheet and increment a string field containing an integer padded to 5 characters with leading zeros. This field is not the primary key and the db does not automatically populate this. Also the DB will allow this field to be entered as NULL if this helps and then change afterwards if this helps.

I can get the data into the table I need using a combination of rookie DTS and insert statments and manually update the string field for the 20 records I have to do today, but next week I need to import around 1000 records.

Should I write a C#/ADO.net app to do this, [bearing in mind I'm a newbie so that'll take me a couple of days :-) ] or is there a way I can increment a string field using DTS directly or some sort of loop in an insert statement?

Thanks in advance G

EDIT: The table I'm inserting into is constructed as below and I need to update "cedeviceid", "vanwarehouse", "username", "devicesimnumber", "UserGroup" and "ServiceMgr". from the Excel sheet. "sendercode" is the string I need to increment

CREATE TABLE [dbo].mw_gsmprofile NOT NULL,
[mainwarehouse] varchar NULL,
[vanwarehouse] varchar NULL,
[username] varchar NULL,
[sendercode] varchar NULL,
[devicesimnumber] varchar NULL,
[usersupportgsm] [int] NULL,
[userisonline] [int] NULL,
[onlinedate] varchar NULL,
[lastsentsequenceno] [int] NULL,
[lastsentdate] varchar NULL,
[lastreceivedsequenceno] [int] NULL,
[lastreceiveddate] varchar NULL,
[EnableAutoDownloading] [int] NULL,
[EnableCompressFile] [int] NULL,
[LogonUserName] varchar NULL,
[LogonPassword] varchar NULL,
[LogonDomain] varchar NULL,
[UserGroup] varchar NULL,
[UseStorageCard] [int] NULL,
[SMSMapProfile] varchar NULL,
[SMPPClientFlag] [int] NULL,
[LASTUPDATE] varchar NULL,
[ServiceMgr] varchar NULL,
[VanLocation] varchar NULL,
[OnHireWarehouse] varchar NULL,
[OnHireWhsRepType] [int] NULL,
[HireDepotWarehouse] varchar NULL,
[HireDepotWhsRepType] [int] NULL,
CONSTRAINT [PK_mw_gsmprofile] PRIMARY KEY CLUSTERED
(
[cedeviceid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SAMPLE DATA

cedeviceid,vanwarehouse, username, devicesimnumber, UserGroup, ServiceMgr
3431, 999, INSTALL TEAM 1,,INSTAL, AHOA
3441, 999, INSTALL TEAM 2,,INSTAL, AHOA
3451, 999, INSTALL TEAM 3,,INSTAL, AHOA
3461, 999, INSTALL TEAM 4,,INSTAL, AHOA
3471, 999, INSTALL TEAM 5,,INSTAL, AHOA
3472, 999, INSTALL TEAM 6,,INSTAL, AHOA

Answer 1

I would do this with a small app. You have to get the first item of the table, sorted in reverse order (this will give you the maximum value of the id). After knowing the maximum value, you can increment it very easily with the expressiveness of a programming language.

Answer 2

1. Get the starting value from the database
2. Iterate over the imported records, inserting them one at a time into the DB. Use a counter variable to increment field. Use tostring("00000") overload to pad the number.

Answer 3

Some slifght own trumpet blowing here, but my own FOSS tool CSVfix can do this without writing any code, using the (inexplicably) uundocmented sequence commanbd. For example, given a CSV file:

foo,bar
one,two
three,four

then:

csvfix sequence -n 42 -p 5 afile.csv

would produce the output:

00042, foo, bar
00043, one, two
00044, three,four

the -p option specifies the padding and the -n option the starting number.

Now to find out how it got omitted from the help file....