Voice of the DBA

Tag: ETL

Loading a Text File with a Line Feed

Loading text files is a skill that probably every DBA needs. I know that the import wizard is available, but there are times that you might want to automate this without using SSIS. In those cases, it’s nice to know how to load data from a programmatic standpoint.

I had the need to do this recently with a text file that looked normal. When I opened it in my editor, it looked like a normal text file, one column of data.

I thought this would be easy to load, so I created a simple table:

CREATE TABLE MyTable ( teststring VARCHAR(100))

I then ran a simple BULK INSERT command.

BULK insert MyTable
     from ‘C:\SampleFiles\input.txt’

And I received this:

Msg 4863, Level 16, State 1, Line 3
Bulk load data conversion error (truncation) for row 1, column 1 (mychar).

That’s not good. I suspected this was because of the format of the file, so I added a row terminator.

BULK insert MyTable
     from ‘C:\SampleFiles\input.txt’
with ( ROWTERMINATOR = ‘\r’)

That didn’t help. I suspected this was because of the terminators for some reason. I also tried the newline (\n) terminator, and both, but nothing worked.

Since I was worried about formatting, I decided to look at the file. My first choice here is XVI32, and when I opened the file, I could see that only a line feed (0x0A) was used.

However, I wasn’t sure how to get this in my code.

I tried CHAR(), and that didn’t work.

I could look to edit the code with XVI32, but that seems odd. However, let’s try that.

I replaced the \r with 0x0A and then deleted the r. Once I saved this, and reloaded into SSMS (do not normalize to CRLF), I could run this.

I suppose I could also do this with the ALT key, and a number pad, though I couldn’t get that to work on my laptop. I need to try that on my desktop, but it’s not a great way to code. Easy to forget that characters are in the code.

I tried searching a bit and found that SQLDenis had a solution. He used dynamic SQL, but with a little formatting, the code is still easy to read, and this works fine.

DECLARE @cmd VARCHAR(8000)
SELECT @cmd = ‘BULK insert Mytable
                from ”C:\SampleFiles\input.txt”
                with ( ROWTERMINATOR = ”’ + Char(10) + ”’)’
EXEC(@cmd)

I executed this and loaded my file just fine.

It’s not often you might need to do this, but it’s a handy little trick for those files that might be formatted from other OSes.

February 3, 2016
When Should We Be Cleaning Data?

I was reading Grant Fritchey’s update from the Business Analyst Conference and noticed this quote: ” There were lots of sessions on the need to clean data, which implies that we need to do a better job at data collection in order to support this wild new world.” Grant was talking about the fact that many of the sessions seemed to imply that it’s mostly the processes around data that cause issues with data, not the people.

However is that really what we should do? Do we want to do more and more cleansing at the point where people enter data? I’m not so sure that’s the case. The more that I deal with various applications and forms, the less I want to see too many fields and values required. Certainly there are pieces of data that are necessary for action, but I often find there are lots of additional fields that analysts want, but are more of a burden than a necessity.

Most of us as data professionals design tables to handle the needs of an application. We put fields in tables that we expect to fill with data, based on a specification. However the real world is messy, and often the data we want to collect and store isn’t always available. The question is, do we force failures in the application or do we handle missing data?

I don’t want to eliminate DRI, or go to an EAV models for lots of data. However I do think that we need to ensure we allow defaults and work with developers to allow data in our systems that might not be complete now, but perhaps will be in the future. We should work with analysts to help them build reports that can handle defaults or missing fields. We can work with developers to allow applications to request updates to data later and then design ETL that can efficiently fill in the updated information.

Applications and software need to be flexible to work with problematic data. We have the ability, as data professionals, to help our clients still find meaning in data that might not be as complete as we’d prefer, or they’d prefer. However we can still find valuable information in what data they have..

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

June 8, 2015
Opening Up Data

Tim O’Reilly has been an advocate of open data access and standards for some time, especially from governments. He’s pushed for more interoperability and certainly more accessability from all sorts of groups. He gave an interview earlier this year to LinuxVoice where he talked about a variety of things, but data was foremost on his mind.

There are some good thoughts, but I was pleased to see him looking for more software to adapt how it works with data rather than asking data to match the application. An interesting thought he had was in the area of control systems. Does every device or sensor need a separate application and way of interacting or should we have some guiding design principles that let similar applications work in similar ways with different data? That almost sounds like good data modeling and normalization principles in action, backing a data driven application.

I also liked his acknowledgment of the fact that so much of our data isn’t very portable. Between social networks and proprietary storage, it becomes hard to move data around. The pattern of downloading data, perhaps editing, perhaps not, and then uploading elsewhere works great with ETL tools, but it’s cumbersome for many users and applications to deal with. Building ways for us to interact with disparate data, allowing for queries to remote sources, sometimes transforming and copying data, all of this needs to be easier to implement and integrate inside software.

In some ways, I think the 3.0 model of our Internet interaction will take place around data. I think SSIS will continue to be one of the most valuable tools in SQL Server (along with lots of demand for work), but it still needs improvement and enhancement to catch up to other ETL tools. I really hope Microsoft believes this and continues to invest in the tool.

I also think that the data professionals that really stand out in the next decade will be those that learn to make the choices about when to use R, JSON, XML, HADOOP, or whatever non-RDBMS tool to meet a need. But also when not to use these tools. The better data professionals will make good decisions about when to query data, and when to move it to another system.

It’s an exciting time to work with data as the opportunities and rewards continue to expand and grow. I look forward to what the future will bring us.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

May 12, 2015
ETL

The phases of ETL

Today we have an editorial that was originally published on Aug 31, 2006 as Steve is traveling at DevConnections.

I’ve followed the development, release, and subsequent news on SQL Server 2005 for over two years now. It’s been an interesting journey and I’ve had the chance to see some evolutions in the product over time as well as observe the reaction to different subsystems.

At the Colorado Code Camp, where I got roped into speaking, I was asked what I thought was the part of SQL Server 2005 that had the most impact. I’m not a heavy user of the product, but since I gather, edit, and follow the news I answered that Integration Services was one of the most written about, blogged about, newsworthy systems. It seemed more people were really excited about SSIS than anything else, despite the fact that I think the Service Broker and other technologies are “cooler” to me.

So when I saw some people knocking SSIS, as well as this blog post by Jamie Thomson, I decided this would make a good poll.

Is SSIS a professional data integration tool?

I’m not a BI guy, and not deep into the ETL world like many of you out there. However it does seem to me that while it may not offer all the features of tools costing thousands of dollars, it does a great deal of the things I need in a professional ETL tool.

Most of us don’t deal with terabyte databases. We don’t roll up data from 30 separate applications into one humongous data warehouse and then spin off a dozen cubes for different departments.

I’ve done some of that on a smaller scale and I thought DTS was up to the task most of the time. Sure the error handling wasn’t great and there were times we had to restart things and do some manual cleanup or script editing, but it worked very well for most of my needs. From what I’ve seen of SSIS, it’s more robust and does an even better job of being a professional tool I can use.

As to whether it’s professional enough or enterprise ready, I’d like to know what you think.

November 4, 2011