April 25, 2011 at 10:58 am
I have a csv file that has data with the following columns & values
Unit - "Unit1"
Date -"4/25/2011"
Time - "17:30:30"
Timestamp - :17:27:52"
Dispatch - "17:35:00"
Ene - 100
Reg - 0
SpRsv - 0
SupRsv-0
I got to insert/upload the data to database table. I created a database table and the design form is like
DISPId - Unique Identifier
Unit - nvarchar(50)
Readingdate - datetime
ReadingHour - datetime (Time)
RecTimestamp- dateTime(Timestamp)
DispatchTime - datetime(Dispatch)
Ene - float
Reg - float
spRsv -float
SupRsv - Float
I have a stored procedure written to insert the values. When i call the SP it gets executed but i didnt see any data in the database table.
I have noticed the followings:
1. If i tweek the csv file columns "Time" as - "4/25/2011 17:30:30" and similarly for Timestamp and DispatchTime and then if i call the SP means i can see the data in the database table. Else I couldnt.
Do i need to change the datatype for "ReadingHour", "RecTimestamp", "DispatchTime" because I need the column to hold "hh:mm:ss" . I tried changing the column to nvarchar even then its not showing the values in the table.
Could anyone please guide me on this. Appreciate your help and thanks in advance.
April 25, 2011 at 1:35 pm
Hmmm. There are a few things troubling.
First, a datetime data type holds both the date and the time together. I can see why you may have made your table like this, to match the csv without requiring any transforming while you upload this data into a work table before massaging it and moving it to the real table which may have a single datetime field with both the date and time values in it. So if you could tweak the csv to have the date and time together, this would be best. If not, then you may want to transform the value before it lands in your real table. It is not a great idea to use an nvarchar for this type of field because you really don't want to save the data in a "hh:mm:ss" format. You want to save it has a 'date and time' (or, datetime), and DISPLAY it as hh:mm:ss. If the datetime is stored as a 'string', then it prevents you from quickly and easily searching the rows based on a range. Difficult to code, and poor performance for SQL Server to pull this apart and work with it.
That aside, I also see a DISPid which looks like it's your PK on that table. It is recommended that you don't choose a guid as (or as part of) a clustered index for many reasons. This is another topic though.
---------------------
To address your issue, you can debug your stored proc or pull it apart and run the query in pieces to see if there are any errors and to test any typecasting code in the proc. You'll need to post your stored proc so we can see what you are trying to do.
If you are calling a proc, but also dealing with a csv file, and you are not posting in an SSIS category, then I'm assuming that you are using some programming code (C#?) to open the csv and call the proc with the proper parameters. If so, then debug your app to get the call to the stored proc first. Then copy/paste that statement into SSMS and run it from there, and/or open the stored proc contents, declare variables and set parameters (if you don't do stored proc debugging) and test from there.
Really, the first step is to isolate if this is a programming issue, or a stored proc issue as to why an insert is not reporting an error, but also not adding your data. THEN you can break it down more to isolate the issue better.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 27, 2011 at 6:44 am
Jim,
Thanks for your response.
I just got an update here, the client will be sending us the source (csv) file with the date and time together. so, that will make us to import the data without any issues.
Thanks
Der
April 27, 2011 at 9:00 am
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply