CSV or Excel daily data

[access2010]

As of January we will be receiving daily data on about 100 items, which can be in a in a CSV or Excel format.

Our intention is to store this information for comparison purposes and historical comparison in a MsAccess database.

Should we receive the data in a CSV or Excel format?
If the data is received in an Excel format, should we do the comparison calculations in Excel or in MsAccess?

Your suggestions will be appreciated.

Nicole

 

[DHookom]

I would import the files (Excel preferred for me) into Access. What you do wit it after importing depends on what you mean by “comparison calculations“.

 

[CJ_London]

I on the other hand prefer csv - with excel, data types can be inconsistent, whereas csv tend to be more explicit

Agree need to clarify what ‘comparison calculations’ means

 

[DHookom]

I seem to get bitten by CSV with embedded quotes. Excel does not enforce data types in columns so importing into a template table may be required.

 

[Pat Hartman]

I really wish that every application used the consistent csv format used by Access. Sadly, nobody pays attention and so I'm with Duane. I can't begin to tell you the crap I get that people call csv.

 

[plog]

.csv - Less opportunity for crap. Weird headers, merged cells, data on multiple tabs, formulas. Ideally they'd send you a test file, you'd review and approve it and then they send you the same format thereafter.

I'd do the comparison in whatever software you are more comfortable. You're on an Access forum so it's going to skew for it. But if you're better versed in Excel, use that.

 

[Pat Hartman]

Ideally they'd send you a test file, you'd review and approve it and then they send you the same format thereafter.

This sounds like a canned export rather than a user created file. You'd think that csv exports created by software would be usable. They are not unless the programmer understood the basic definition of the csv. Excel exports created programmatically have far fewer errors. Although, I did get one interesting one. Do you have any idea how Access reacts when you try to import a spreadsheet where one of the column names starts with a space? Not pretty. When I called the creator to complain, their response was - "Excel doesn't care so we're not fixing it". I ended up having to use OLE automation to open the file, fix the bad column name and THEN import the data. Believe it or not, their .csv files were worse because they had no record separators. The files were imported as ONE row with a million columns due to the missing crlf's to separate records. They said that was "standard" also. I guess that's what you get when you hire off-shore labor for $5 per hour. AND all their exported file names had multiple dots in them. Parts of Access wouldn't recognize the file names as valid so I had to fix those prior to importing also. The client creating the files I had to process - the state of Connecticut. In bridge, we would call their IT staff "double -dummy hopeless". That means that you can't make the right play even if you are looking at all four hands.

 

[CJ_London]

Guess it's all down to experience - I only have one current example - an excel bank statement from a foreign bank. Even excel can't open it without comment

admittedly it comes as an xls rather than xlsx but it is the only file they will provide. So I have to open it then save as a .csv. If I save as a .xlsx, it still won't import due to some foreign characters in the data. As a csv, the characters are 'regularised'. - I think it is due the currency symbols having different ascii/unicode characters depending on the source language. You probably don't get it in the US since the $ sign is pretty much universal.

Do you have any idea how Access reacts when you try to import a spreadsheet where one of the column names starts with a space?

I've had that situation in the past, the easy solution is to import without headers - you will then get auto generated column names of F0, F1, F2 etc

 

[Edgar_]

Should we receive the data in a CSV or Excel format?

Suggestion: Request both CSV and Excel files, then test them to determine which is easier to work with.

Ideally, build the export mechanism yourself for better consistency, but if you can't, then test the two to make an informed decision.

If the data is received in an Excel format, should we do the comparison calculations in Excel or in MsAccess?

If the comparison calculations are the same each time, then Access. If unreasonably variable, then Excel.

 

[The_Doc_Man]

@access2010 - I think I'm with Edgar_ on this one. Request that you get samples of the files in BOTH formats and attempt to import both files (one at a time of course) to see if either one gives you trouble. If one gives you trouble, request to always get the other format from that point forward. We can give honest and well-founded opinions on either side of this issue, but the truth is that we can't choose with any more accuracy than flipping a coin. You, on the other hand, will have the opportunity to actually TEST which format actually works better. Sometimes, the best theory in the world still isn't good as an actual experiment.

 

[DickyP]

I agree with getting both for testing. My choice is CSV because with Access import specs you have a good chance of getting the data the way you want it.

HOWEVER (and it's a big however) find out how the CSV files are generated. If they are being generated in Excel adds are you are getting two levels of potential problems with the seemingly random way Excel builds CSV files. And potentially even worse if the providers are creating the Excel files from CSV!!

And lastly don't accept anything until you have tested the data - I can't emphasise this enough.