Situation: I work for a company that has a software application with around 100 clients. The application use MS-SQL 2005,2008 for the database. Another VERY large company supplies a datafile to our clients which our software needs to import. For whatever reason (lethargy I would assume) each record in the file is delimited by a character which can and does change. I had changed from using our application to process these files to bulk import which worked great. I would read the first 100 characters of the file which had the description of the delimiters used in the file. I would then specify to Bulk Import what the row delimiter was and load a table for further processing. BUT new security guidelines have been put in place at almost all our clients prohibiting bulk imports. So I need a method to read characters from the data file until I reach the "newline" character and put the resulting data into a row in a staging table where it can be processed further by the rest of the process. So is there a way to read char by char using OLE Automation or OLE DB tools? I also have an issue with what version of SQL I'm on because one of the fields of data can be well over 8K characters so on 2005 I need to use varchar(text) and on 2008 varchar(max). What environment or SQL server function can I use to get the SQL version I'm on?
Thanks in advance for any help!
asked Dec 04 '09 at 11:49 AM in Default
To get the version you can use the serverproperty function:
answered Dec 04 '09 at 12:06 PM
I know you said you can't use bulk insert, but can you please clarify? At our company, bcp is not allowed (and the bulk insert task using SSIS), but I can use the
Would it be possible for you to run a
answered Dec 04 '09 at 12:43 PM
First, are you confined to reading this with pure T-SQL?
If you are not, you may want to look at using another tool to either import the rows or even do all the data cleansing entirely and put it in normalized tables. I am a huge fan of Python for working with SQL or doing basic data cleaning before importing it, and of course Perl is also very good for that. SSIS is another good option. You can set it up with only a line delimiter and import each entire line into SQL server.
If you are confined to using pure T-SQL you can look at OpenDataSource, with some limitations. It can be very flexible and easily set to read entire lines without looking for any column breaks.
Finally, Simple Talk had a good overview of a variety of text import techniques you may find useful at Importing Text-based data: Workbench that talks about several options.
answered Dec 04 '09 at 03:04 PM