SQL Server type conversion

I recently wrote a script to summarize some type conversions involved. Because the data is read from a CSV file, that is, the file type separated by commas (,), can be opened in Excel, or can be opened in text documents, there is no concern,

• File Reading.

*. CSV files contain *. ini files.

[<FILENAME>. CSV]
Colnameheader = false // whether to read the column name
Format = csvdelimited
Characterset = ANSI
Col1 = reference texts width 200
Col2 = ***** text width 200

Since we do not know the specific file type and size at the beginning, all of them are listed as nvchar, so we need to process the float and datetime values in the database to be imported.

Code
/*
* {0} folder path
* {1} file name
*/
Insert into table
Select * From
OpenRowSet ( ' Msdasql ' ,
' Driver = {Microsoft text Driver (*. txt; *. CSV )};
Defaultdir = { 0 }; Extensions = CSV; ' ,
' Select * from "{1}. CSV" ' )

 

• Read from nvchar200 to 50, remove the first space, and then read the previous 50

Code
Left (ltrim (T. Reference ),50) As reference,

• Convert nvchar of ddmmyy to datetime
  T. date is the ddmmyy type of nvchar200.

 

Code
Case when T. Date Is Not Null  
Then cast (substring (T. date, 5 , 4 ) + Substring (T. date, 3 , 2 ) + Substring (T. date, 1 , 2 ) As Datetime)
Else Null
End

• Float Conversion

 

Code
Cast (S. PriceFloat),