SWDuncan

Access data import is idiotic

swduncan — Wed, 15 Jul 2009 21:22:20 +0000

So I'm importing a bunch of data into Access from Excel, and for whatever reason today is a day full of strange bugs and bizarre behaviour. So I've resorted to exporting much of the data out of Excel into CSV files...

<rant>

Have you ever noticed these idiotic things about importing data into Access?

If you try to import a CSV file, you'll get an error if the file is open in any other pogram. Why? I can import in other contexts with no problems with open files.
When you open a CSV file in Excel, and then save it, Excel throws up an error box stating that the sheet may contain features that are incompatible with CSV format. I'm not sure how that could be, but one must still answer that they do indeed want to keep the file in the format it is in before Excel will save the file.
Excel (and Word) often decide that you have changed something in a file when you haven't, prompting you to save when you don't need to. CSV files are simple in format - if I didn't add or subtract data, it didn't change.

I know there are several import tools out there, but it bugs me to have to buy another application to get something done that Access/Excel should do more easily.

</rant>

IT not interested in Windows 7? Hardly a surprise

swduncan — Wed, 08 Jul 2009 01:35:47 +0000

You might be expecting an anti-Microsoft rant about how IT folk aren't that keen on upgrading to Windows 7 (hat tip to twitter friend CXI) because of all the trouble they expect. The problem is that you could poll the IT community for almost any OS and you'd probably get the same response.

The problem isn't that new operating systems are so poorly designed - most aren't, even though I suspect Windows probably is - it's that IT isn't paid to improve things. They're paid to avoid unexpected costs and problems, and keep employees from abusing the system and their employer. Unpgrades mean change, change means risk, risk by definition entails the unexpected, and the average professional IT manager is about as risk-averse an animal as you can find. They don't get hurrahs when things are working great, they just get blasted when they aren't. If I worked in this world I'd fight any upgrade because people don't miss what they've never had. That's why IT is called the 'business suppression unit'.

Whether it's Ubuntu, Windows 7, or OSX it doesn't make much difference.

At some point a more enlightened management, combined with a more tech-savvy workforce will demand innovation from IT, and will be willing to sacrifice the handholding and 99.99997% uptime commonly demanded today to get it. Those early pioneers will lose some sleep, but they will be rewarded. Then we will get past the industry of professional IT that IBM and Microsoft built, and see the innovation we see on the web in common business.

More on importing data into Access

swduncan — Tue, 07 Jul 2009 16:37:59 +0000

So you're trying to import data into access, and you're getting an error. "Subscript out of range" is a popular one, but some flavor of "import failed" with zero explanation is also infamous.

Here's the laundry list (in no particular order) of things I do to resolve the problem:

Export the data out of Excel into a csv file first, and then import into Access.
Check the data in Excel for the folloiwng. Sorting can reveal these issues, and search and replace can usually take care of them.:
- Empty cells
- A rare number in a text column or rare text in a number column. Some government agencies are fond of putting "N/A" in place of an empty cell or zero.The also are fond of putting years in as text rather than as a number.
- Empty rows.
Let Access create the table on import, rather than trying to import into an existing table - resolves conversion issues.
Delete the rows below and the columns to the right of, the data. How many? Usually 10-20, but really the intent is just to get rid of any cells containing whitespace.
Use the Trim function in Excel to get rid of space before or after numbers, to prevent their being imported as text.

How do you solve these problems? Have a tough import problem? Let's have a look!

My experience - open vs closed networking

swduncan — Mon, 06 Jul 2009 16:36:15 +0000

One of the recurring themes on almost any networking discussion group is whether open or closed networking is really more effective. There are lots of debates already stored out there, so I won't go through them here. Instead, I'll explain why I'm reversing on a decision I made last fall.

Last fall I decided to dump most of my 4050 contacts on Linkedin, and focus on those people with whom I had a genuine connection. I did it because some of the features of Linkedin, namely the network updates area, were becoming somewhat useless as they were inundated by updates from folks who I ddin't know very well. In addition, I no longer had the pressing need for a large network from a research perspective, and it seemed like a good time to experiment.

Well, the experiment is over. I'd spent lots of time on open networking, and now I've tried closed.

Open wins.

For several reasons:

Linkedin is not just about documenting your network. It's also a personal advertising system. More connections = more exposure.
Network updates only from my closest connections = boring. What can I say? These people are mature, responsible folks who have solid careers and are busy. They're not changing jobs every 15 minutes, or hurling sheep at each other - this isn't Facebook.
I need people I don't know more often or at least as often as the people I do know. With a closed network it's much harder to find them.
I found Linkedin less useful. No matter what I went looking for, I was less likely to find it. I was exposed to fewer questions, so I didn't answer as many. I felt more cutoff from the world.

I know that the open vs closed debate will probably rage forever, and this blogpost won't shift the balance, but for those who are teetering my vote is teeter more toward open.

Avoiding errors when importing data into Access.

swduncan — Thu, 18 Jun 2009 11:47:10 +0000

The other day I was importing some data into Access from an Excel file. I got a "Subscript out of range error". This is a pretty infamous error that Access throws up quite a bit when importing data, and there are numerous causes.

Some are pretty logical, like having text data in a column you've designated as a number, or having invalid column names that you're trying to turn into field names.

Sometimes however, it just seems like it's astrological, related to the weather, or what I had for breakfast.

But I just figured out a trick. It turns out I was trying to import data into an existing table. I'd created the table, and defined fields how I wanted them and made sure the spreadsheet had fields that matched. However when I tried to import I got an error.

I struggled with the usual nonsense - checking for invalid entries, deleting innocently-empty looking rows and columns, and looked for trailing whitespace. No luck.

So for kicks I tried to import the file and instead of importing it into a new table, I just had Access create a new table. It worked. Hmmm.

Checking out the difference between the two tables it seems that some of the fields I'd designated as integers in the table I'd created were actually imported as float values by Access. I changed the fields in my table, did another import, and it worked. Hurray!

Yes, There Will Be Carbon Default Swaps

swduncan — Thu, 11 Jun 2009 11:36:57 +0000

My father titled a recent poem on his blog Soon They’ll Be Carbon Default Swaps I don't know if he was being serious or not, but there will be.

Just as you can buy derivatives (the Wall St. term for making a bet) that a given month or week will have more or less heating or cooling degree days, or you can buy an instrument that pays off when company defaults on its debts, you will be able to hedge on a company's ability to meet carbon limits. Heck, you will probably be able to buy options on carbon credits, and you will probably see carbon credit arbitrage between different markets - after all, different cultures place different values on clean air, right?

I'm telling you people, black is the new gold. We're creating a new currency, and it's not going to behave as expected.

The Death Of Social Media Marketing

swduncan — Wed, 10 Jun 2009 11:55:23 +0000

Social media is a popular subject in marketing, and has been for a while now. As we see usage of Facebook, Linkedin, Twitter and similar sites rise, people can't help but see opportunities for advertising and marketing in general.

In many ways it would seem to be ideal. We're trying to build relationships, after all, and these sytems embrace relationships - actually they depend on them. They are measurable in many ways, viral, or potentially so, and are cheap.

The problem is that their success is their downfall. As they become more popular their use is increasing limited in business environments.

As the economy crumbled and people feared for their jobs social networking has become more popular to build an insurance policy against a layoff. Folks who have ignored networking for years are suddenly getting interested. Of course in tough times companies work harder to eliminate waste, and activities like social networking are often viewed as waste. It's silly, as networking can be very powerful, but we're dealing with perception here, not reality. Apparently when a company networks it's powerful, when an employee networks it's waste.

The ironic part is that the same companies that are banning these sites are probably starting social media campaigns. They hope to woo customer personnel to join their networks, read their tweets, and generally be good pals, all to the benefit of the bottom line while at the same time they're denying their own employees the ability to do the very thing they ask of others.

How long do you think this will last?

Social Media Has 18 Months...

I predict that within 18 months the use of social media on company networks will be banned by most of American business, and the participation by business people during working hours (and thus the 80-90% of the marketing value of social media) will collapse.

When this happens, it will change. It will become far less focused on companies, and lot more focused on individuals. As people lose the ability to administer their networks on company time or with company resources, they will also lose the urge to use their networks to company benefit. If you're busily trying to keep your Linkedin page up to date at 10pm, are you going to worry first about how you're representing your company, or yourself? When you're sitting at your desk you're far more likely to keep the company's interests in mind.

Similarly, when your participation in these sites comes purely at your own expense, are you going to follow your vendor's or customer's pages, or stick to family & friends?

I hope I'm wrong.

Insisting on security risks - revisted

swduncan — Tue, 02 Jun 2009 16:23:59 +0000

A while back I wrote a post about companies insisting we put our accounts at risk by forcing us to answer silly questions that would serve as a backup in case we lose a password. You know, like what our favorite color is, where we were born, and other commonly available items.

Thanks to Bruce Schneier's post on Secret Questions I've learned about some research people have done on the subject. Yep, it's just as stupid an idea as I originally thought, and they point out something I hadn't noticed - people often forget the answers they give. Who has the same favorite color or movie forever?

Since I wrote my post on the subject, I've taken to using a very long password to these questions, which are becoming increasingly popular - even with companies that should know better.

Switched to Drupal

swduncan — Wed, 20 May 2009 22:48:20 +0000

It's done! This morning before I left for work I put the new swduncan.com online. Of course there are many theme tweaks (if not a whole new theme) that need to be taken care of, and a few other oddities. For the most part it went quite smoothly. the WordPress Import module worked well and took care of the vast majority of the work. I copied the directories containing images and other files over, and they seem to be working ok.

So far life under Drupal is good!

An interesting Linkedin question

swduncan — Wed, 20 May 2009 01:16:27 +0000

An ex-colleague of mine asked an interesting question today:

What are your observations regarding AEM member companies' 1) attitude toward social media 2) adoption rate of social media 3) 'wiifm' based interest in social media?

This was interesting to me because when I listen to Total Picture Radio, or read many blogs, it's easy to get the idea that only a few gnomes living under rocks have yet to get onto Linkedin. Everyone else has been indoctrinated that social media is a Must Do, therefore the adoption rate is exponential, and of course 'What's in it for me?" is a punch-line.

The people on TPR and on most blogs are enlightened, web-savvy folks who've been using social media since its birth. It's second nature, and we've all been using this stuff for so long it's easy to brand the folks who haven't as backward.

The problem is that the ratio of us to 'normal' people is still 1:50,000 or so if you consider the whole population. Normal people look at us as, well, hobbyists. Over enthusiastic technophiles. Maybe even cult members - which is easy to understand when you listen to the most vocal zealots for social media. Normal folks don't quite get social media yet. They hear about a lot of not-quite-tangible benefits, but hard evidence that being on social media is a must is not easy to come by. I even know a Gen Y'er who didn't really know what Linkedin was, even though Facebook was old territory.

It is easy to see how social media carries risks. We are drowning in stories of teenagers posting stupid things and getting in trouble so bizarre our legal system doesn't quite know how to handle it. It's easy to see this stuff as scary. With no clear upside, it's easy to see the people who are deep into it as a bit reckless.

So, while the questions asked above might seem a little silly, I don't think they are. Most of the working world still doesn't see social media as a Must Do, and that's a fact that all of us enlightened web savvy folks need to keep in mind. For every dot-com startup or tech company there are hundreds of small manufacturing and service companies. Internet use is widely restricted, and many social media sites are blocked.

In answering the questions I will first state that AEM (my employer) has nothing to do with my replies, although they have a social media effort underway. I will also restate (as I do in the 'About' page) that these are my views, not my employer's.

The attitude about Linkedin seems to have shifted from 'What's Linkedin?' to 'Yeah, I heard about that. You aren't actually on that, are you?' to 'Yeah, I probably need to get on that one of these days.' Maybe it's more accepted lately. In the current economy, everyone will flock to anything that might give them an edge in the job market.

I see the adoption rate as generally inversely proportional to age and level within the company. Older folks higher on the ladder arguably have less need for social media, and they certainly have more to lose. They do have a lot to gain, but most of them didn't get where they are by being stupid, and social media isn't yet something all smart people do.

Overall though, the adoption rate is higher now than it was before. I see people joining Linkedin now who never would have a year ago, and many seem to be paying attention and doing it with their eyes open. I see less dead accounts than I used to, but part of that is less usage of Linkedin on my part (I don't do as much research as I used to) and that I shrank my network a while back.

The 'What's in it for me?' attitude is there, but it's there in smaller quantities than you might expect. Unless you are a spammer it takes a lot of work to be a visible pain on the internet, and it takes a lot of effort to get a network large enough to really cause annoyance. Most WIIFM people aren't so industrious, and even so once they make it clear they're in it only for themselves, they'll see their network shrink faster than it grew. The good news is that for anyone who wants to make an extra buck or two coaching people, these folks will be easy customers who probably won't require much after-sale support.

In my experience, the benefits from blogging and social media have been totally unexpected and unpredictable. Along with persistent effort, social media and networking take a lot of faith. The lazier folks just don't stick with it.

This also makes social media in general a hard sell for some people. They want to know what they're going to get out of it - that's only logical, right? The idea of putting personal info on the web seems crazy, and therefore the payoff must be immediate and huge. When they hear that it isn't, they balk. It's not hard to see it from their point of view, but it can be hard to get them past that.

Did I answer the questions?