Often times when importing data into Vista that originated from another system, there comes a need to edit a .CSV file in Excel. Excel usually does a good job of converting that data into a spreadsheet then converting it back into .CSV files...except for when it doesn't. The biggest offender is numbers with leading zero's that need to be preserved or numbers with commas and dollar signs that Excel thinks is text. PowerQuery to the rescue.
This small dataset needs to be manipulated in Excel before importing into Vista. If I open in Excel by double clicking on the file then I will lose the leading zero's. Rather than opening the .csv in Excel, instead try opening a blank Excel workbook. Then head to the Data menu, Get Data, From File, From Text/CSV. Select the file and choose Transform Data. PowerQuery will attempt to guess the data types just as Excel would, only we can now change that guess in PowerQuery, unlike Excel. Click the icon in the left corner of the header and choose something different. Voila, our data is now ready to load into Excel as intended without any erroneous auto-corrects.
Exporting data from 3rd party systems and websites into Vista is something some of our clients do every day. Reach out if you are interested in learning how to import data into Vista. Or, if you are too busy actually running your business, we would be happy to develop a solution to streamline data imports into Vista for you.
Rather than opening the .csv in Excel, instead try opening a blank Excel workbook. Then head to the Data menu, Get Data, From File, From Text/CSV.
Select the file and choose Transform Data.
PowerQuery will attempt to guess the data types just as Excel would, only we can now change that guess in PowerQuery, unlike Excel.
Click the icon in the left corner of the header and choose something different.
Voila, our data is now ready to load into Excel as intended without any erroneous auto-corrects.
