Cut-off Fields
Closed this issue · 6 comments
Problem Example:
Paratransit Services
Service Description:
Arranges transportation to and from eligible Medicaid covered medical services to anyone with a valid DSHS ProviderOne ID card, who has no other form of transportation. The Medicaid Broker will arrange for the lowest cost, most suitable option for the *cl *
Issue: is this due to field limit length in Libre Office, Excel, or not full field exported form Access. Needs Investigation
Copy-paste to excel is the issue.
Export from MS Access fixes this issue, but potential encoding errors? When exporting as *.xls text fields begin with an apostrophe, exporting as csv include boxes for non-printing characters like a return.
Using Access to export works, Excel import introduces encoding errors (xOO0d).
Suggested Idea:
Combine VBScripts
- Resfresh data, save and close :http://stackoverflow.com/questions/16932104/how-can-i-use-vbscript-to-effectively-refresh-the-external-data-of-several-excel
- Regex to replace non-printable characters :http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
- UTF-8 :https://gallery.technet.microsoft.com/office/XLS-to-CSV-Add-in-58c1941b or https://jaimonmathew.wordpress.com/2011/08/23/excel_addin_to_work_with_unicode_csv/ or.
- Export sheets to individual .csv files
Script to upload to Drive
Script to split out individual .csv sheets
OR
Do the above with Libre Calc
Automated export of Access queries, automatic import into a xlsm file, and automatic export of data sheets -- oh my!
All that is left is to test.
Unfortunatley the sheets continue to have the same encoding errors. After spending all day attempting work-arounds I have to admit that I have been defeated by Excel.
I tried exporting data using the Access tables with Calc and they look like they are encoded properly.
Next step is to rebuild transformation sheet using Calc.
The other step is to export each sheet to individual .xls files. Then use --convert-to script to export .csv files.
Fixed! Output is handled properly by exporting .xls with Gnumeric ssconvert. Script in place to export, split rename, and move files.
Last step:
- create macro to open, refresh, and save xls data.
- improve validation for urls.