SQL Server 2008 - TSQL Read CSV file -


i working on project entails on importing csv file sql server 2008 r2 database. csv file generated excel file populated "manager" pr hours employees. includes additional information such job , phase employees working on , includes number of hours equipment (if used).

once generate csv file that, it's not usual straighforward "column" based csv file. it's more "row" based csv file each row being kind of unique. due caveat involved, cannot straight dump (using bulk insert or openrowset) sql, essential create (temp) table appropriate column filled data.

i looking use fields within csv file based on "location" of field in row.

so, positions of data remain same, since every csv based on template file - have navigate through csv file using sql code find right field based on it's position in row. hope gives guys better understanding of trying achieve here. sorry long wall of text.

i researched bit , here's have come far:

reads csv files temp table through custom sql function (reading lines file) https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

this 1 interesting. dumps whole file blob , can sift through data. http://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/

finally, 1 essential splits out rows , creates separates records per row. interesting.. http://ask.sqlservercentral.com/questions/17408/how-to-read-a-text-file.html

if has suggestions or steps follow through this, appreciate it.

to mods: if have posted (especially links) shouldn't here, please feel free remove it. apologize if did.

thanks much.. hope hear positive responses! :)

warm regards, pranav

if file not large, option post-process file in excel using vba macro. of course, you'd need come speed using excel object model , vba, recording function makes simple. 1 advantage of vba approach seems want row row processing, , vba better that, whereas sql better set-based operations.


Comments

Popular posts from this blog

curl - PHP fsockopen help required -

HTTP/1.0 407 Proxy Authentication Required PHP -

c# - Resource not found error -