Import CSV into SQL Server 2008 table where index is in a second table
Posted on 2015-01-29
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.
Columns: TagName, TagIndex, TagType, TagDataType
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.