More "ACE" driver frustrations. Large Integers loading as exponentials
-
Ok... well I just made a topic and I never appeared, that's really really annoying...
I'm going to cut this shorter this time, sorry.
I have excel document, one column can contain large integers. All columns are being loaded as WSTR(255), by using the headers to force the data type, however, the large integers are sometimes being converted to exponentials. For example, 20160803872 becomes 2.0160803872e+011. I can't convert in my staging, as the value has lost accuracy so is useless.
I really don't want to make a script source and code every field manually (although I'm pretty sure that's the way it's going to go), as that's going to take more than a day as there's 3 sheets. Does anyone have any ideas, or should I just get another nail for ACE's coffin and I'll get to building a Script Source?
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, March 14, 2017 9:28 AM
What a great Tuesday you're having.
What is the Excel display format for the column containing the large INTs?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin - Tuesday, March 14, 2017 10:21 AM
Thought you might be the one to reply Phil ;). And ha, you don't know the half of it. We invoked DR today as well, as our phone lines have been down for the last few days.
The display format set to number. P.S. the file is external and the process should be automated, so changing the columns display format probably isn't an option.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, March 14, 2017 10:36 AM
I must admit that I feel like I am doing a service to the human race by helping SSIS developers with any 'this should just work' questions about importing from Excel spreadsheets. I don't want others to go through all of what I've been through with the damned things, if all it takes is a couple of sentences of advice.
But you seem to have found something new here & I'm interested, in a self-harming sort of way.
At what stage of the pipeline does the conversion occur? Is it at source (ie, in the WSTR(256) external data source column)? Or later?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Are you sure that SSIS is the problem? Excel routinely changes the number formatting as you open a file. The earlier example didn't actually look like you "lost any accuracy". The 2 notations are 100% compatible with NO loss.
https://superuser.com/questions/1083454/editing-csv-file-in-excel-always-converts-number-column-to-scientific-notation
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? - Matt Miller (#4) - Tuesday, March 14, 2017 8:28 PM
It does look like in my post, sorry. i was making the topic again and most of missed a number off. The number should be 201608038723. 2.0160803872e+011 would become 201608038720. @Phil. Will get back to you shortly.
Edit: Looking at the dataviewer, the numbers are converted to exponents at the Data Source.. The flow directly after the source shows the values such as 2.0160803872e+011. If you'd like, I can "bastardise" some of the data and upload it along with a a dimmed down dtsx.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, March 15, 2017 2:52 AM
Yes, please do. I'd like to have a go at solving this one.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin - Wednesday, March 15, 2017 5:42 AM
Will do. I'll get something to you after lunch (which is now 🙂 )
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hi Phil,
Hopefully this is everything you need. please do tell me if you need anything else. You should just need to change the parameter to the location of the file (ensure you keep the file name), and change the source for the SQL Server.
I've had to upload the .dtsx as a .txt file as .dtsx isn't a permitted file type, so just change the extension back. 🙂
Many thanks again.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, March 15, 2017 7:17 AM
Loaded up your test code and boom, it works for me, no issue with exponentials!
I am using SSDT 2016 / SQL Server 2016 & did get a 'package has been upgraded' message when I loaded it. Maybe the message should have said 'package has been upgraded and fixed'.
I do get an error with the final SELECT INTO bit:
Error: 0xC002F210 at Drop & Create Final, Execute SQL Task: Executing the query "BEGIN TRY
DROP TABLE FinalExpo;
END TRY
BEGIN..." failed with the following error: "Error converting data type nvarchar to numeric.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Don't think that's relevant, though, as all looks well on the sample table.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Should of stated actually, but i'll double check, the problem was in the ClaimNumber column, with claims in the YOA 2015 and 2016. Assume they all looked fine?
If so, I am using Visual Studio 2012, however, I wonder if it's to do with the ACE driver's themselves then? What version do you have installed? I have the 2010 drivers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, March 15, 2017 8:39 AM
I guessed that ClaimNumber was the problem for you & yes, looks fine for all the sample data you provided.
I am using Microsoft.ACE.OLEDB.12.0, though I also have Microsoft.ACE.OLEDB.16.0 installed (you never know whether it might be 'interfering' somehow).The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hmmm, frustrating that it doesn't replicate at your end then. Out of interest how are the dates coming through? Mine come through as INTs, however, your conversion error implies they're not, so they're coming through as dates?
On a seperate note, it just dawned on me that a script task probably isn't going to work for me either. The problem being that I have to import that column as a String (as some aren't large integers). That means, regardless of what I do, the script task is also going to bring through the value from Excel in the format 2.0161001822e+011. I'll give it a test later, but I think ACE might actually defeat me here. I shall endeavour though!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, March 15, 2017 9:13 AM
The dates appear to be coming through as strings, retaining the format they have in the spreadsheet
So it looks like they just 'pass-through' from start to finish as strings. I cannot understand why they are being converted to INTs for you, given that you are reading the column headings & have IMEX=1 in your conn string. If you do a 'preview' in the Excel source editor, do they appear as INTs there?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
So, strangely... it's now working fine. I just went to run my original process (which I haven't changed since making my post yesterday) to get a screenshot for you, and perfect load. Dates are coming through as Dates, and I'm not losing the INTs.
Now, I know I'm not going crazy, as I just finished building a Script Task, and I was still having a problem. The above exponents are were even copied out of my SSMS from the data load. I'm honestly, baffled. I mean, I'm happy that it's working as I want now, but I'm actually really annoyed that I've spent the best part of my last day trying to get around a problem that has now "disappeared".
This does, however, give me concerns, as if it's happened once, and disappeared with out any reason, I don't know if it's going to magically reappear. I'm going to move along anyway for now, and see what happens. Unfortunately, I get the feeling that this is going to come round later in the year and bite my ass when ACE remembers it can load dates as INTs, and BIGINTs and exponentials (and so my ETL falls over).
So yeah... it's fixed... somehow... I'm going to go to the pub and drink...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply