CSV or Excel daily data (1 Viewer)

[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.

 

[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.

 

[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.