SQLPRO CREATE TABLE FROM CSV CODE

, col_01 = ltrim(substring(v.inputString, 1, p01.pos - 2)) Here is a function that can be used to return up to 12 elements from a delimited string: ALTER Function. To accomplish this in SQL Server - we can use charindex and substring, or you can use a combination of splitting the string to a table, and rolling the values back up in a GROUP BY. Unless I am mistaken - split_part parses a delimited string into component elements and returns the desired element from that string. | 42 | Cherry, Banana | Red,Yellow | NULL | NULL | | ID | Name | Color | LogDate | Unknown | Quark.csv 'ID'|'Name'|'Color'|'LogDate'|'Unknown' , FIRSTROW = 2 - real data starts on the 2nd row SQL Server allows to query *.csv file as a virtual DB table on a file system. Output - using POSTGre db but require same for sql server select split_part(p.Lines, ',',1) As Col1,įrom ( select unnest(string_to_array(:csvdata, chr(10))) as Lines)p Unfortunately, sql server has a similar function STRING_SPLIT ( string, separator ) but notice it does NOT have the position aspect SPLIT_PART(string, delimiter, position) offered in POSTGre.

This can be done in POSTGRE using a simple function split_part because that function includes the ability to search the csv concate string for the position of the field delimiter example ',' comma separator.

SQLPRO CREATE TABLE FROM CSV CODE

To provide some context, I need to wire up this code in a third party reporting engine which is talking to sql server in addition to other multiple data sources such as oracle etc. I have multiple use cases whereby I need to effectively create a virtual sql server table from a csv source and I need to do it preferably using pure sql on the fly- ie not using a procedure.

Sqlpro create table from csv