Solved

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

Posted on 2015-01-29
5
105 Views
Last Modified: 2015-01-30
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
Comment
Question by:Bryan Kipp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40577618
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
 

Author Comment

by:Bryan Kipp
ID: 40577642
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40577671
0
 

Author Comment

by:Bryan Kipp
ID: 40578094
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40579974
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

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question