Asset Management - CSV Extract formatting issue

[Adam Toms]

Hi all,

There is a need for us to obtain a list of all mobile assets from our Asset Management Database. We need to check this data against what our mobile comms provider Vodafone has against our mobile asset estate.

I'm aware of formatting issues when pulling an extract of data which includes Vodafone sim cards which are 20 digits in length, when an extract is pulled from the Asset Management database. The last digits of the sim are automatically changed to zeros when the file is opened in Excel. This appears to be due to character limit formatting issue in Excel. 

If a SQL Report is built and then run as a report is run you get the option to to download this as a PDF. When the data is translated into a PDF the formatting issue doesn't occur which confirms this is an Excel issue. But due to limitation of the number of fields you can pull through using the PDF file format this doesn't allow us to obtain all the data we need.

Has anybody been able to overcome this error. I'm aware of the quotation fix in Excel, but the problem is you then have to go back into the database grab the correct number and edit every single line, which defeats the purpose of the extract.

If it was possible to preload the CSV before the data is inputted and then downloaded with quotation, which looking at our extract from our supplier Vodafone, seems to be how they've worked around this issue. Would it be possible to request this an enhancement? 

Any other workarounds/ advice would be gratefully received. 

Kind Regards

Adam

[Steve Giller]

Without wishing to sound facetious:

8 minutes ago, Adam Toms said:

The last digits of the sim are automatically changed to zeros when the file is opened in Excel

Don't open it in Excel?

More practically, can you import the csv, rather than open it, and set all of the fields to be text - that should prevent any formatting of the values by Excel.

[Adam Toms]

Hi @Steve Giller,

Unfortunately we need to pull the information out of the Hornbill Asset Manager. When pulling an extracting the data from Hornbill Asset Manager you do not get a choice in the front end of which format to download the extract from. It auto downloads this data into a .CSV file from the front, and when you change the format, to number text, or custom in the sim card field, the last digits are changed to zeros no matter which option you select. So you can no longer at that point correct the issue with the data that was downloaded.

Our billing team wish to compare extracts of mobile asset data from Spreadsheet A (Supplier) and Spreadsheet B (from Hornbill)

This query was raised by our billing team. They need to compare the spreadsheet provided by Vodafone which has this workaround applied using quotations, and an extract Who just has front end access only access to Hornbill Asset Manager. I was initially taken a back by this. But I can confirm I can replicate the issue.

I decide to try and workaround the issue by building a SQL report as I knew that would provide options to be able to ascertain, whether this was an Excel issue or not. As mentioned we have tried PDF and we can see that when filtered on my asset the sim card number and mobile number are presented without the formatting issue. However PDF has a limit on the number of rows that can be extracted.

So I'm stuck in terms of finding an appropriate resolution to this. Other than saying use Spreadsheet A, and input each sim number from Spreadsheet A into Hornbill Asset Manager Front end, and look at the details in the GUI. But this is far from ideal.

Many Thanks

Adam

 

[Steve Giller]

@Adam Toms My apologies, I may not have been fully clear.

Excel is not really a csv viewer, it arbitrarily imposes its own formatting on the data by (very poorly in many peoples' opinions) presuming what you want to see.

If you import the csv into Excel you should be able to specify the data type for each column (In Excel 365 this is a real pain, to be fair) but setting them to "text" (or "Do not Detect Type" in 365) it should take the data as-is and not change it.

As you've mentioned, this is an Excel problem - the solution is to force Excel not to mess with your data, which is easier said than done but possible, or to use an alternative tool for viewing the csv.

 

[Adam Toms]

Thanks @Steve Giller. Removing all file formats from the SQL report and going into the output file formats and set .XLSX as the only file format to be downloaded from the SQL report has allowed me to get the data I need.

It's a shame it can't be obtained from the asset management front end, but at least I now have a way forward. Thanks again.