eConnect: Leading 0's Getting Dropped

Hello:

In Microsoft Dynamics GP 2013, an eConnect app is being used to pull data from SQL and export the data to Excel.  The result, in one of the columns in Excel, is that leading 0's are dropped.

The 0's appear in SQL.  But, they are not seen in the Excel spreadsheet.

Does anyone have any ideas, on this?

Thanks!

TBSupport
LVL 1
TBSupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Surone1Commented:
have you set the cell formatting to text in that collumn?
TBSupportAuthor Commented:
Hi Surone1:

In Excel or in SQL?

By the way, below is the SQL query.  The final select statement has "InvNum".  That's the column having the issue:

 
use [master]
if object_id('tempdb.dbo.#database') is not null
    drop TABLE #database
create TABLE #database(id INT identity primary key, name sysname)
if object_id('tempdb.dbo.#Payments') is not null
    drop TABLE #Payments
 
create TABLE #Payments(ID INT identity primary key,VendorID char(15), InvNum char(255),  PayDate datetime, PymntAmt numeric(18,4), PtmntNum char(21), VENDORCLSID char(15), COID char(5))
set nocount on
 
insert into #database(name)
 
select name
from sys.databases
where name like  '%ZK%' or name like 'SF' or name like 'ZOEPA'
--'ZTEST%'--HERE THE FILTERING RULE FOR THE Z DATABASES!
 
and source_database_id is null
 
order by name
 
--Select *
--from #database
 
declare @id INT, @cnt INT, @sql NVARCHAR(max), @currentDb sysname;
 
select @id = 1, @cnt = max(id)
from #database
 
while @id <= @cnt
 
BEGIN
 
    select @currentDb = name
    from #database
    where id = @id
 
    set @sql = 'select pm.VENDORID, pm.APTODCNM, pm.DOCDATE, pm.APPLDAMT, pm.APFRDCNM, v.VNDCLSID  from '+ @currentDb + '.dbo.PM30300 pm join '+ @currentDb + '.dbo.PM00200 v on v.VENDORID = pm.VENDORID'
 
    --exec (@sql);
    insert into #Payments(VendorID,InvNum,PayDate,PymntAmt,PtmntNum,VENDORCLSID)
                exec(@sql)
                update #Payments set COID = @currentDb where COID is null
    set @id = @id + 1;
 
END
 
select ID,VendorID, InvNum,PayDate,PymntAmt,PtmntNum from #Payments
where VENDORCLSID like '%SERVCHAN'
and  [PayDATE] between 'GBL_DOCUMENTSTARTDATE' and 'GBL_DOCUMENTENDDATE'
--AND [PayDate] between '04-01-2015' and '05-01-2015'

Thanks!

TBSupport
Surone1Commented:
in excel just right click on the cells, choose format cells and select text, however changing the type of the column in the SQL query using convert or cast might also have the desired effect.  
the point being that a number does not need leading 0's
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

TBSupportAuthor Commented:
Hello:

We'd like for the leading 0's to be exported and maintained "automatically" in Excel.  Is there a way to have that happen, without having to manually format the column in the spreadsheet?

TBSupport

Thanks!

TBSupport
Surone1Commented:
i'm not sure and no sql server expert, but in that case it might help to use CAST or CONVERT  in the query.

SELECT  CAST(InvNum AS varchar)  .....
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

Surone1 is correct, this is an Excel formatting issue, not a SQL or eConnect issue.

I assume you are exporting from SQL to a CSV file?

The only way I'm aware of to retain the leading zeroes is to carefully open the CSV file as a Text file.  It's a terrible tedious process for a typical user, but I don't know of a better way.

http://excel.tips.net/T002588_Handling_Leading_Zeros_in_CSV_Files.html

And saving the file with a TXT extension is safer than CSV, as users will double click on a CSV and it will open in Excel automatically, removing the zeroes.  

If you find a better way, I'd love to hear about it.  It's a hassle for my customers as well when they want to edit a CSV data file prior to importing it into GP.

The other alternative I can think of would be to export the data to a native XLS / XLSX file, but that's a hassle with its own issues, and I wouldn't recommend it.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP
Dynamics GP Certified IT Professional

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
HI There:

It exports as an xlsx file.

So, there's no way around the issue, huh?

TBSupport
Victoria YudinOwner / Dynamics GP ConsultantCommented:
Steve is right, this is an issue with the Excel file when being auto created thinking that the value is a number because it does not have anything other than numbers in it. I don't believe in this case that it is possible to pass anything other than the data to Excel, so then Excel determines on its own what datatype each field/column is. This will also happen if you export to Excel from SmartList in GP. Any leading zeros will be dropped. No great fix to offer here, but a few thoughts/ideas:

Change the invoice numbering to (a) not have leading zeros or (b) have a letter or a few in front.

If you can create a refreshable Excel file instead of having it be auto-generated every time, then you can format the columns any way you want, set the file to save the formatting and it will then correctly show the leading zeros.
Surone1Commented:
just a thought. is it possible to add qoutes around the number in the query and thus make excel treat it as a string at import?

Iwan
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi Iwan,

I tried adding an apostrophe in front of the number ('0001234) and adding quotes ("0001234"), but neither worked properly when I opened the CSV.

The apostrophe showed up in Excel as '0001234, and the one with quotes still lost the leading zeroes.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP
Dynamics GP Certified IT Professional
Surone1Commented:
too bad..
thanks for trying it out anyway,

Iwan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.