|
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
(comments are locked)
|
|
To get the version you can use the serverproperty function: Works great! I should check answered but then I probably won't get anymore looks. But big thanks David! Joe B
Dec 04 '09 at 02:57 PM
user-695 (google)
(comments are locked)
|
|
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 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 '09 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 '09 at 02:55 PM
user-695 (google)
(comments are locked)
|
|
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.
(comments are locked)
|
