.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Converting a portion (numbers) of a text file name into a date type

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

I am working with a number of Flat Files that will be processed daily. The FileName is a combination of text and numbers. The numbers are the date of transaction. Therefore, I need to pick up the numbers and use it as a Date (DateAdded) inserted into the database. However, the positioning of the numbers is not fixed.  

Examples of what the File Names:   Dial20100610.txt and Dialog20100611.txt

Now, I have inserted all the data into a staging table called Raw_Staging with File Name as the name of the column (eg. Dial20100610.txt, Dialog20100612.txt)

Is there a way to strip the text character from the columns with tsql so I am only left with the numbers and then convert it to a Datetime datatype so I can insert it into the destination table?

View Complete Post

More Related Resource Links

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

Check if Type is numeric, text or date



How can i check if a type (of class Type) is numeric, text or date? Is there a simple method for this already?
Currently I'm checking it like this:

if(type == typeof(Int16) || type == typeof(UInt16) || type == typeof(Int32) ... and so on) return "numeric";

Report Builder Filter shows Field as Date Type =Text instead of Date


My Data was created by retrieving ODBC data - using PowerPivot for Excel 2010, and has Birthdate defined as a Data Type = "Date".  This was uploaded to SharePoint, and a New Dataset was created to point at it.

I'm using Microsoft SQL Server 2008 R2 Report Builder 3.0.  I tried to filter this data on Birthdate, but when I go into the Filter, and specify the field that was defined as DataType = "Date" in PowerPivot - Report Builder changes the type to "Text", and gray's out the field so that I can't change it to "Date".

If I create an Expression as Cdate(Birthdate.Value), it lets me change the Data Type to "Date", and works appropriately, but we don't want to have to do this with every date field.

Why doesn't Report Builder see this field as a Date ? 

SFTP is converting text file format, now SSIS sees an empty file


Using BIDS that came with SQL Server 2008 R2, I created a text file import package.  I used files generated from my local machine for local testing.  However, I find that these files get their format changed after being SFTP'd to the data warehouse.  Not being able to visually see a problem with the text file to be imported, I copied it down to my local machine and compared to one generated at my machine.  Not until I chose "Show All Characters" in Notepad++ did I see the difference: my local file ends its lines with a carriage return-linefeed character, whereas the file that went through SFTP ends its lines with a linefeed character only.

I suspect there's something very basic going on here that I don't know.  I won't ask anybody to explain it, but I'm wondering if there's some way I can import both file formats.  The column numbers and lengths are identical, it's the just line ending character that's different and causing zero rows to get imported.  The only other thing I know to do is recreate my flat file connection, and I haven't even tried that yet.


Parser Error Message: Could not load type MasterPage File



Server Error in '/' Application.

Parser Error

Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Could not load type 'wamsilverlight.Web.Wam'.

Source Error:

Line 1:  <%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Wam.master.cs" Inherits="wamsilverlight.Web.Wam" %>
Line 2:  
Line 3:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Source File: /Wam.Master    Line: 1

Version Information: Microsoft .NET Framework Version:2.0.50727.4952; ASP.NET Version:2.0.50727.4927


All of the following have been tried over and over again:

1.) Delete all existing .dlls

2.) Delete MasterPage File and recreate it.

3.) Delete MasterPage perform a c

Conversion from string to type 'Date' is not valid



Im receiving a strange error Error Message: Conversion from string "15/08/2010 22:21:35" to type 'Date' is not valid.

I know this is generally down to cultural date formats etc, however the reason this is strange is it just randomly occured twice in the last week, the website has been running since october and nothing has changed,

The error constantly occured until the IIS was reset and recompilled the site, its been running fine since, however im just trying to figure out what could cause such an error to occur.

I've narrowed it down to a line of code which is:

bktime = DReader.Item("Bktime").ToString

bktime = Year(CDate(bktime)) & "-" & fmt(Month(CDate(bktime)), 2) & "-" & fmt(Day(CDate(bktime)), 2)

Has anyone else encountered this randomly occuring and is there anything i can put in place to prevent this in future?



Writing to a text file



I have the following  subroutine in an asp.net application class. The subroutine writes data to a text file upon application login.   My question is that if this function was called at the same time by two different  users would it cause any kind of error. Is there a need for a try catch?

 Public Shared Sub writeToLogFile(ByVal UserName  As String)
        Dim strLogMessage As String = String.Empty
        Dim strLogFile As String = System.Web.HttpContext.Current.Server.MapPath("~/Log.txt") Dim swLog As StreamWriter
        strLogMessage = DateTime.Now.ToShortDateString().ToString()  " ==> " & UserName

        If Not File.Exists(strLogFile) Then
            swLog = New StreamWriter(strLogFile)
            swLog = File.AppendText(strLogFile)
        End If


Error converting data type varchar to numeric.


I create a form to record data using ASP.NET and SQL database...
when i run the project show error "Error converting data type varchar to numeric"

what's the problem actually.i try to change in datatype in database..
but still showing error...


The file reached the maximum download limit. Check that the full text of the document can be meaning



I'm facing an issue with the indexing.

I have 1 WFE+Index server+DB server.

Index server is not installed with MS FIlter pack 1.0

When crawling, the there will be document with warning in crawl log:
The file reached the maximum download limit. Check that the full text of the document can be meaningfully crawled.

Documents that with warning are such as doc, ppt, xls, docs, ppts and many others.
However, I view into the successful crawled document, there are doucments with ext doc and ppt.

For large file index, there are MaxGrowthFactor + MaxDownLoadSize required to be added into the index server.

As my understanding is, MS Filter Pack should installed into index server(already did, correct me if i'm wrong).

I looked into the Office SharePoint Seach(CA>Services in farm), if the server is appointed to "Use this server as indexing server", then MS Filter Pack is suppose to be installed into that particular server as well.

At the bottom, there also has another option is "Use all web front end for crawling".

The question here is, IF the option "Use all web front end for crawling" is selected.
Does the WED FRONT END Server required to installed the Ms Filte

Update Profile Database from text file using BDC



I have a requirement to import the profile properties from a text file in sFTP to update the MOSS Profile Database. The objective is to create a single repository for the Staff Directory in MOSS.

Can I use BDC to connect and update the Profile database?

What is the alternate approach?


When create a suvery using type Rating Scale, is it possible to create 5 range text?


When create a suvery using type Rating Scale, is it possible to create 5 range text?

Default range text only show 3 fields

Read an XML file, setting uname/pw, Accept, Content-Type

Hi; To read some REST XML data we need to set the username, password, and tell it for the http request Accept: application/xml Content-Type: application/xml Is there a sample anywhere showing how to do this? thanks - daveVery funny video - Reporting as a Metaphor

Conversion Failed when Converting the nvarchar value 'N' to data type int.

Hi.. I am facing a strange situation. when i executing the following query it is returning the error SELECT  s.row_id as shipment,    SUM(CAST(la5.attr_value AS INT)) AS Cases     FROM    shipment s WITH(NOLOCK)    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND          s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local     INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3)  WHERE s.spare2 = 'RELEASED'       AND shift.shift_desc = 'c'   AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000')    AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000')    AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000') GROUP BY s.row_id  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'N' to data type int. The column  type of attr_value of lot_attr table is a USER D

Deleting Last Line of Text in a text file

Hi, I needed some help on the best way to delete the last line of  text in a text file. I have a subroutine that looks like this, but it is missing some code. Could anyone advise me on this? Dim strLogFile As String = System.Web.HttpContext.Current.Server.MapPath("~/Log.txt")         Dim fs As New FileStream(strLogFile, FileMode.Open, FileAccess.ReadWrite, FileShare.None)         Dim swLog As New StreamReader(fs)         Dim array As String()         Dim value As String = swLog.ReadToEnd ''Array that holds the lines of the text file.         array = value.Split(vbCrLf)        ''code for deleting last line: . . .  fs.Close()

Operand type clash: xml is incompatible with text

hi to all .thank in advance ..i am using sql server .i want insert below xml into one column in table which is xml data type but i am getting error Operand type clash: xml is incompatible with text .pls tell me solution for this ?<Message xmlns="http://www.abcscripts.com/messaging"><Header><To>mailto:6033168728001.spi@abcscripts.com</To><From>mailto:9900007.ncpdp@abcscripts.com</From><MessageID>541481b182404a3ea819f0aba886e652</MessageID><RelatesToMessageID/><SentTime>2010-08-23T18:27:28.2Z</SentTime></Header><Body><RefillRequest><RxReferenceNumber>163U067PL4E5LYK0D1EGXTA57JUW5HCFQ4B</RxReferenceNumber><Pharmacy><Identification><NCPDPID>9900007</NCPDPID><FileID>TW1FKB1P1H2P468L3L7RM812GR7X5O70VQ3</FileID><StateLicenseNumber>5602NPPQGU765FX7262US8X6G18NMG67H47</StateLicenseNumber></Identification><StoreName>Druglix</StoreName><Pharmacist><LastName>Pharmacist last name goes here noww</LastName><FirstName>Pharmacist First name goes in here</FirstName><MiddleName>Pharmacist Middle name goes here</MiddleName><Suffix>Junior iii</Suffix><Prefix>Pharmacist</Prefix></Pharmacist><PharmacistAgent><LastName>Pharmacist agent Last name go

Converting Hindi Text to English Text

Hi,Can someone guide me in converting hindi text to english text using c#.  The application will be windows based.  When I googled it says it will be possible using google/translate.  However I am not aware of how to use that in my application.Any ideas?Regards,Karthizen

Data loading from text file

Hi, I am loading the data from a flat file into a sql table. The values in one of the column have spaces at the end of the value. I want to remove those spaces and load into the table. I am thinking of using derived column and use rtrim on that column, how can I use that as an expression. Thanks.sqldev
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend