I ordered a software product last year from this company in the US. Great company, really smart people there, really good software. 

So imagine my surprise when i recieved the product that had the first line of the address as "Jan-48".  Well.. i thought to myself, i live at Unit 1 number 48, i had entered it as 1/48 so somewhere along the line their software translated it into a date. Luckily the postman in my area knows me and knew who to deliver it too. I let the company know, they said they'd let their ordering department know.

Three weeks ago i ordered another product. Again, the same problem with the address. Again i sent a letter to them. This is their reply.

The explanation is a little lengthy, but when we send our orders for fulfillment, the information is sent in an Excel file.  Excel sees your "1/482" and wants to convert it to a date.
You can avoid this by entering your street address on one line: 1/48 YourStreetHere St.

 I don't know what boggles me more, that they use Excel, or that they don't know how to format cells in Excel so that they have the "text" type.

 I pray that this is not a technology company. This is forgiveable for businesses that sell low-tech things, collectables, and the like. If I was ordering model railroad gear I might expect this. God knows that the company I order saxophone reeds from is actually worse from what I've seen of their back end process but they haven't screwed up an order yet and they have a hard to find product at a good price and a good turnaround time and I'll keep doing business with them because I know they are a small operation that does one simple thing well: keep wind instrument players well stocked with the reed of their choice. 

Now if you were ordering software they actually make or something like that then this is really scary.

 

medialint:

Now if you were ordering software they actually make or something like that.

 

Yep, i was ordering software they make.

You could prefix your address with an apostrophe... 

Spikeles:

 I don't know what boggles me more, that they use Excel, or that they don't know how to format cells in Excel so that they have the "text" type.

 

Of course, if the system is fully automated, (e.g Excel loads list of addresses from CSV, runs macro that jiggles them around and prints address labels), then having to manually break into the proccess and reformat the cells would be a major PITA (and would result in a cell containing "17533" in your example, because you have to set the cell to plain text, then type/paste the data).

mallard:

then having to manually break into the proccess and reformat the cells would be a major PITA

 

If they are using a macro then they could easily use the same macro to set the cell data type as well. 

It's actually possible to set the type while loading from CSV.

Random832:

It's actually possible to set the type while loading from CSV.

plz send teh codez.

Kederaji:

Random832:

It's actually possible to set the type while loading from CSV.

plz send teh codez.

It's the third page of the open text file wizard, just record a macro while you're doing it. The relevant bit is the "FieldInfo" parameter of the "OpenText" function.

Random832:

Kederaji:

Random832:

It's actually possible to set the type while loading from CSV.

plz send teh codez.

It's the third page of the open text file wizard, just record a macro while you're doing it. The relevant bit is the "FieldInfo" parameter of the "OpenText" function.

 

plz send codez 2me also

kthxbye

Spikeles:

The explanation is a little lengthy, but when we send our orders for fulfillment, the information is sent in an Excel file.  Excel sees your "1/482" and wants to convert it to a date.
You can avoid this by entering your street address on one line: 1/48 YourStreetHere St.

 I don't know what boggles me more, that they use Excel, or that they don't know how to format cells in Excel so that they have the "text" type.

 

You aren't the first, there was a big story about this last year sometime.  I don't have a handy reference, but the gist is that lots of scientists doing bioinformatics research were horrified to discover that Excel was mangling their data because the names of the genes looked like misformatted dates.   And yeh, they presumably were equally horrified to discover they could have chosen the right option when importing the data and avoided all the trouble...

LordOfThePigs:

You could prefix your address with an apostrophe... 

 

If you're going to do that, might as well go the whole hog...

 

Name:  Bobby Tables

Address: January 48th

Zip:  AT&H0

 

  ;-)

The real wtf is that they actually gave you that much information. 

Really, your customers don't care why your system botched it up, and they certainly don't want to have to work around it.  Just find a way to fix it on your own so it's right for next time.

Protip: The same people that run the office, taking calls, fulfilling orders, handling AR, etc are not the same people that write, QA, test, nor support the software. Even in a small business, such tasks are usually outsourced or delegated to Office Mom or who shouldn't be expected to be a whiz at Excel. 

They're using CSV files. CSV import in Excel is heavily broken. I once tried to make a CSV where a cell read "01234". I found no way - it always came out as 1234. Manually formatting it as "00000" of course helped, but is not ideal.

And yes, in theory one CAN give Excel hints about how the CSV is formatted - but not when the CSV was double clicked in the explorer. In that specific case, formatting the column as "00000" was found to be less work than opening it with the wizard and specifying the right parameters.

But well, it's Excel. And its CSV support has many more WTFs in it... like, depending on whether you save from VBA or from the menu, it saves different CSV format... example:

A1 = "foo
A2 = "bar,baz;haha"hihi
B1 = "hello<CR><LF>world

Normally, Excel exports it like this:

foo;"bar,baz;haha""hihi"<CR><LF>
"hello<LF>world";<CR><LF>

However, when saved from VBA, it writes:

foo,"bar,baz;haha""hihi"<CR><LF>
"hello<CR><LF>world";<CR><LF>

Outside VBA, Excel can't read the latter version of the file... and from within VBA, it will fail to read the former version (no matter what parameters one specifies, the <LF> vs <CR><LF> inside the cell throws it off).

OperatorBastardusInfernalis:

Normally, Excel exports it like this:

foo;"bar,baz;haha""hihi"<CR><LF>
"hello<LF>world";<CR><LF>

However, when saved from VBA, it writes:

foo,"bar,baz;haha""hihi"<CR><LF>
"hello<CR><LF>world";<CR><LF>

 

 

 

If you are putting literal line feeds in a flat file you should expect it to break Excel or anything else ...

Eh? The problem is when he has multiple text lines _inside a single shell of a spreadsheet_ - which works fine, but excel converts them two different ways when using the CSV format.

medialint:

If you are putting literal line feeds in a flat file you should expect it to break Excel or anything else ...

 

No, he's talking about using VBA to control Excel and tell it to export the data in the spreadsheet as a CSV, he's not writing the CSV himself in VBA.

I'd say the bug's pretty typical of Microsoft, though. Left hand, meet right. Right, left. They long ago exceeded whatever the Chandrasekhar Limit of Metcalfe's Law is and collapsed into a business singularity, an organization so dense not even vaporware can escape.