x

file import from SQL WITHOUT using any BULK

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!

Joe B

more ▼

asked Dec 04, 2009 at 11:49 AM in Default

user-695 (google) gravatar image

user-695 (google)
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

To get the version you can use the serverproperty function: select serverproperty('ProductVersion'). If it returns 9.something it's 2005 and 2008 starts with 10.

more ▼

answered Dec 04, 2009 at 12:06 PM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 33

Works great! I should check answered but then I probably won't get anymore looks. But big thanks David!

Joe B
Dec 04, 2009 at 02:57 PM user-695 (google)
(comments are locked)
10|1200 characters needed characters left

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 BULK INSERT SQL statement.

Would it be possible for you to run a BULK INSERT statement using an OLE DB command object or something similar?

more ▼

answered Dec 04, 2009 at 12:43 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

I'm not a system admin and I'm really not sure how they do it but anything that has BULK in it returns "file is not accessible" We've adopted a "wait 30 minutes if you can't give us a folder with bulk access rights, or 7 seconds if you do...." approach. Fortunately a lot of our clients have gone ahead and set that up but there are some of the large clients where they won't. Thanks for the response, I DO appreciate any and all suggestions.

Joe B
Dec 04, 2009 at 02:50 PM user-695 (google)

DUHhhhh, to answer your question, BULK INSERT is what I'm trying to use. Also tried OPENROWSET and bcp. Not accessible error. What's frustrating is that it DID work and there is an awful lot of data manipulation that relies on having this source data. Sorry about that I was just ranting to a colleague and it sort of spilled over into my first comment.

jb
Dec 04, 2009 at 02:55 PM user-695 (google)
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 04, 2009 at 03:04 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x66

asked: Dec 04, 2009 at 11:49 AM

Seen: 1821 times

Last Updated: Dec 04, 2009 at 11:49 AM