I have a process that will be dumping comma delimited files - one line per file - into a folder. These will be uniquely named and I may have more than one file in the folder at a time to be processed. Need to use a wildcard - c:\import\*.txt, or something.
I have gone through the Import/Export wizard - and this works great - but I can't specify multiple files. I can process one file if I set the name to what is in the package.
Can I use the import/exp wizard with multiple file names, or read from a folder?
And, very important, I need to delete each file after the data is inserted or better, move to a processed folder - c:\import\processed.
Table looks like this -
CREATE TABLE [dbo].[xxorder](
[line_id] [int] IDENTITY(1,1) NOT NULL,
[pat_id] [int] NULL,
[ndc] [varchar](11) NULL,
[qty] [int] NULL,
[clinic] [varchar](30) NULL,
[date] [datetime] NULL,
[status] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[line_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Text file looks like this - 1026,23490644804,12,Johnny Thunders,
I map the first field in the file to the pat_id, second field to NDC, third filed to qty and fourth file to clinic.
Appreciate any assistance with this request - would be a huge time savings if someone could point me to a specific web resource, or detail it out.
Thanks!