Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

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!
0
Bryan Kipp
Asked:
Bryan Kipp
  • 3
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
 
Bryan KippSr Applications Engineering ManagerAuthor Commented:
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.
0
 
Bryan KippSr Applications Engineering ManagerAuthor Commented:
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?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
There are a number of ways to run a package . . . you can use the SQLAgent to exec it or there's a DTEXEC utility which you can call from the command line (and thereby allow it from code or from a scheduled task.

Below covers the specifics on how to set that up:
http://www.mssqltips.com/sqlservertip/1775/different-ways-to-execute-a-sql-server-ssis-package/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now