Link to home
Start Free TrialLog in
Avatar of Bryan Kipp
Bryan Kipp

asked on

Import CSV into SQL Server 2008 table where index is in a second table

I have a folder that contains multiple CSV files. (one per day for the last three years) I need to import the data into an existing table in SQL Server 2008. The issue I am having is when mapping the columns. The CSV file has a column "Tagname". The tagname corresponds to a TagIndex in the table I'm importing in to. The "TagIndex" comes from a second table.

Example:
Table: dbo,TagTable
Columns: TagName, TagIndex, TagType, TagDataType

Table: dbo.FloatTable
Columns: DateAndTime, Millitm, TagIndex, Val, Status, Marker

CSV File Columns: Date, Time, Millitm, Tagname, Value, Status, Marker, Internal

I need a way to map this so that the dbo.FloatTable is updated with the information in the CSV file. That means somehow combining the "Date" and "Time" columns from the CSV into the "DateAndTime" column of the FloatTable as well as mapping the "Tagname" column in the CSV file so that when it imports, it lists the corresponding tag index.

Once the mapping is figured out, I'd also appreciate being able to have a routine that will complete the import task for all CSV files in the folder.

Please feel free to ask anything necessary to clarify what I'm trying to do.

Thanks!
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image
You're going to need to do this in a couple of steps . . . SSIS is probably your best bet.

Essentially you're going to want to import into a staging table.  with the data as is

then you can do something like

insert into floattable
select
cast(  date + time as datetime), milltim, tt.tagindex, val, status,marker
from staging s
join tagtable tt on s.tagname = tt.tagname
Avatar of Bryan Kipp
Bryan Kipp

ASKER

I'm not familiar with SSIS, so if you could give me a brief "SSIS for Dummies" lesson, that would be appreciated! Also, and perhaps this is a dumb question, how would I get this to "Automate". In other words, I get the entire package ready and when I say go, it steps through all the files in that folder.
Avatar of Bryan Kipp
Bryan Kipp

ASKER

Thanks for the excellent information! One last dumb question. Once I have everything set in SSIS through Visual Studio, how do I call the procedure to run?
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial