Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-29
5
Medium Priority
?
113 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 41

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 41

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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

636 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