Solved

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

Posted on 2015-01-29
5
91 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
  • 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now