Excel, Text, Region Codes Dates, International; Chicken Vs. Egg 1

[lameid]

This one has me a little dumbfounded as I do not see a clear way through it.

The International company I work for uses an Accounting system that allows 'reports' to be exported to Excel. The company almost universally uses spreadsheets to communicate anything. Only summary accounting information is available globally but I need a segment of detail information globally (lets call it Electricity bills for the sake of conversation). I have the unenviable task of aggregating the detail data.

This was not too bad until...

1) Accounting System REALLY exports to Tab delimted file with XLS extension insted of Excel.
2) report formatted with date as m/d/y (because I am in the US I guess)
3) User with region setting of d/m/y opens file and saves it so it is now really an Excel file. Dates are saved with the numerical date value sometimes and left as text in other instances (some dates are dates interpretted from text m/d/y as d/m/y format while others are left as text in m/d/y format).

This leaves me with some date values with the month and year flipped from the original / US format and others as text in the original / US format.

Since the response I got from the other party when I brought up a region problem was more or less "I don't get it," I am relatively certain that I can not get an un-saved in "Excel" (tab delimted) format version reliably. Making things more difficult, we are proabably 10 hours a part which means every communication and response takes a business day.

If I could the second pary exported from accounting in d/m/y format and then open in Excel it would fix my problem if and only if, Excel can be trusted to always convert the text to dates.

Any thoughts on how to make Excel more reliable on date conversion (not leaving text behind) or other work arounds would be very welcome, I have a few coming to mind but none of them are elegant or simple.

 

[kjv1611]

Is it possible to change the way the accounting system exports the data? I mean specify the date format right there? Then, for the data storage purposes, make sure you have an enterprise-wide standard for dates and such? Surely across the same organization, you can stick to one standard? I realize it's probably not in your power to do or say anything about it, but that's the best solution, I think. Then if someone wanted to see a date in a certain format, they could change that format in individual reports on an as needed/desired basis.

Otherwise, it sounds like you'll have to have a multi-tiered approach, I believe. So, if it were me, I suppose I'd have to work on one step at a time, working on it until you get a full solution that works. Some formulas, some VBA perhaps?

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

Accounting System REALLY exports to Tab delimted file with XLS extension insted of Excel.

You mean that you can open this file in Notepad and see the data just fine?

If that's the case then get your IT Dept to change the file extension to something other than .xl*.

Better yet, have them write the date in UN-AMBIGUOUS internation format -- yyyy-mm-dd.

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

yyyy-mm-dd

I think I was too annoyed to get to the obvious.
Thanks for that one.

If that's the case then get your IT Dept to change the file extension to something other than .xl*.

For all the bureaucracy around here, you would think it was a government organiztion. The only IT I know how to get to is the IT that supports end-user installs / client and network... If you ask them about anything else it falls into "that is not our job category". Making things more interesting they do not even know how to refer to the other IT groups. Shameful.

But I am just a contractor here, why would I want to know how data moves between systems so I can reconcile my feeds down to the BEST source information? When I came full circle on referrals on that one, I gave up all hope of really getting anything really fixed.

They use that tab file system to get the data into Excel. Stupid, considering the issue I just found. There is also the case where a field sometimes begins with a double quote, (I have not given up on that one).

Honestly I should be directly accessing the databases and update a common system to do this thing the right way... I am so far past right and reasonable it is stupid. My mantra... I am paid by the hour.

 

[SkipVought]

Honestly I should be directly accessing the databases ...

There's yer answer! Probably not possible.

The issue here is that IT made TWO dredfully inexcusible errors: 1) passing dates internationally, in an ambiguous format, and 2) labeling a TEXT file as and EXCEL file.

When Excel opens a TEXT file, all kinds of assumptions are made regarding date strings: in your company's case, the WRONG assumptions, and date text is actually CONVERTED.

1. How would 2009/01/13 be output to the text file?
2. How would 2009/01/10 be output to the text file?

3. When the other user opens this in Excel, what would happen to these 2 dates?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

These data problems must be getting to me... I had to come back for the star instead of getting it the first time through...

Thanks again.

 

[lameid]

As part of the IT service here, tek-tip e-mails get delayed so I missed my cross post with you skip until now...

My understanding, although not 100% is that the end user sets up the template with formatting... So they can specify the format as y/m/d. Since it is really text, I would think Excel should either understand it correctly or leave as text. In either case, Access will understand how to import to the strong typed field if there is a conversion problem. Unless you can tell me I am wrong about Excel, I am going to proceed under these assumptions and wait for something bad to happen.

The other really obnoxious thing is the field name for PO description changes amongst file exports. My assumption here is that on the backside they are somehow running inconsistent queries; perhaps dynamically building a Union query so sometimes data set 1 starts it and sometimes it is not needed so data set 2 is used .

I guess eventually I'll see a way to inject a test into my code without causing to big a problem (currently I clean up the file and then import it). D'oh... add support for find and replace of field names. Sometimes all you have to do is say it or write it down.

 

[SkipVought]

Since it is really text, I would think Excel should either understand it correctly or leave as text.

WRONG ASSUMPTION!!!

Did you read and understand the FAQ I posted earlier.

VERY IMPORTANT!!!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Yes the FAQ is consistent with my understanding, I also asked them to format the export as yyyy/mm/dd.

If Excel sees it as a date, it should see it as not in terms of the region code....

My region code m/d/y...

Test.xls text file (I feel dirty)

2009/08/11
2009/07/05
2009/01/04

This opens in excel as

8/11/2009
7/5/2009
1/4/2009

Even if I had a stray 2009/01/04, Access should take one look at it and change it to 1/4/2009 as that is the final target, at least for now.

The protection here is the four digit year at the begining... I realize I really didn't post that in my last post.

If I get..
09/08/11 - I am sunk. That will be August 9, 2011 in the locality it is being opened first (d/m/y).

 

[SkipVought]

This opensDISPLAYS in excel as...

It OPENS as, for instance, text string "2009/08/11"

Excel ASSUMES that it is a date and CONVERTS to 40036, DISPLAYING it in regional format as either 8/11/2009 or 11/8/2009.

Even if I had a stray 2009/01/04, Access should take one look at it and change it to 1/4/2009 as that is the final target, at least for now.

What is "stray" about 2009/01/04???

Excel is changing NOTHING to 1/4/2009. Excel converts "2009/01/04" to 39817 and DISPLAYS a date in some default format.

Where did Access come from? I thot you were in Excel.

So if the text file has dates in yyyy/mm/dd form, what is the problem?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I get the data in Excel. I am collecting the data in Access because Excel is not an appropriate platform to track multiple data sources and make varied reports.

1) Accounting System REALLY exports to Tab delimted file with XLS extension insted of Excel.
2) report formatted with date as m/d/y (because I am in the US I guess)
3) User with region setting of d/m/y opens file and saves it so it is now really an Excel file. Dates are saved with the numerical date value sometimes and left as text in other instances (some dates are dates interpretted from text m/d/y as d/m/y format while others are left as text in m/d/y format).

The change I am proposing is that the person running the report change the template to export to yyyy/mm/dd. If when they save to Excel, some cells contain text like yyyy/mm/dd instead of the appropriate number, I don't care because Access will interpret it as a date. If it is a date, I don't care what the format is because a date is a date and it will import.

I may or not get a timely response as it seems like the person is out of the office quite a bit until the 29th or a whole week from today. But my tinkering with my region settings shows correct conversion to date using the yyyy/mm/dd text source. Now let's just hope it isn't an issue to get it in that format to start with.

All other options are out of reach.

 

[SkipVought]

The change I am proposing is that the person running the report change the template to export to yyyy/mm/dd.

That's the key!

Everythig else is moot.

Why not IMPORT the text file directly into Access. No need for Excel as an interim, although I disagree with you, when you state that, "Excel is not an appropriate platform to track multiple data sources and make varied reports."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Why not IMPORT the text file directly into Access. No need for Excel as an interim, although I disagree with you, when you state that, "Excel is not an appropriate platform to track multiple data sources and make varied reports."

Since the response I got from the other party when I brought up a region problem was more or less "I don't get it," I am relatively certain that I can not get an un-saved in "Excel" (tab delimted) format version reliably.

 

[lameid]

You can do a lot with Excel, but I would flip burgers long before I'd try to deal with this mess in it... About eight months ago, I was asking, "what are the chances I can get a SQL server?"... Still in Access.

For this particular table, I am sitting at around 385k records today. Since this company has not embraced Excel 2007, Excel 2003 is disqualified by record count alone. Then the joins vs vlookup issue... I'll stick with a query engine everytime and dump to Excel when I have to. I hate reading Excel formulas and copying them down. Access is just much faster at developing all those things and queries do not eat space like pivot tables do (conversely data normalization is not necessary in Excel so you can avoid repeating some data). Also queries give you a central construct for what you are doing where as in Excel you have to spend a lot more time following what is going on. Are there any one off formulas? Is it inentional? Those are questions I never ask in Access. Granted if you are the one doing the work you can assume best practices where used until you find out the village idiot logged onto the network...

 

[lameid]

Follow up for anyone wanting to fix within Excel, the date happened to begin in k2 in my seet so here is a formula to fix...

=IF(ISNUMBER(K2),DATE(YEAR(K2),DAY(K2),MONTH(K2)),DATEVALUE(K2))

That should return the date where the region codes swapped the month and day and convert text to date values... It should work whether the corruption occurs from a m/d/y region code or a d/m/y region code so long as your region code matches the text/original export otherwise datevalue will just corrupt the value itself.

 

[SkipVought]

seems the FALSE argument ought to be...
[tt]
DATEVALUE(RIGHT(K2,4)&MID(K2,FIND("/",K2),FIND("/",K2,FIND("/",K2)+1)-FIND("/",K2))&"/"&LEFT(K2,FIND("/",K2)-1))
[/tt]
Just DateValue won't cut it.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

The Alpaha and Omega data have the same format / region code. So any values not converted to a date (still Text or false part) are in the correct format / order that Datevalue expects per the system region setting... So it works fine.

Although, I aggree in some scenarios it may be necessary to directly manipulate the text. The manipulation would vary for the starting region meaning if the starting text data is in d/m/y format and it was saved on a m/d/y system, the text will have to be rearranged differently in the example to work with datevalue in order to get the month and day in the correct places.