January 31, 2020 at 8:49 am
it looks like this data is coming from an excel spreadsheet that is casting everything as text, hence the scientific numbers and the bad data
maybe worth looking at adding a persisted computed column on the table
"alter table x add case when isnumeric(A) then convert (a, float) else NULL END as MynewColumn PERSISITED"
my syntax might be a bit wrong - not had coffee yet
MVDBA
January 31, 2020 at 2:09 pm
Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more. It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column. I exported it to csv and then imported it to SQL server and that solved my problem. I was then able to remove the prefix.
Thanks all for trying to help me with this.
January 31, 2020 at 2:16 pm
Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more. It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column. I exported it to csv and then imported it to SQL server and that solved my problem. I was then able to remove the prefix.
Thanks all for trying to help me with this.
sigh... my heart dies a little bit when we ignore the option of getting a provider of data to make it useable , I know they are often "paying customers", but the cost of dev time in building import solutions (or maintaining them) can outweigh the money the customer pays.
GIGO - garbage in, garbage out - first rule of importing files
MVDBA
January 31, 2020 at 3:04 pm
sigh... my heart dies a little bit when we ignore the option of getting a provider of data to make it useable , I know they are often "paying customers", but the cost of dev time in building import solutions (or maintaining them) can outweigh the money the customer pays.
GIGO - garbage in, garbage out - first rule of importing files
I'm right with you on this. The fear of somehow offending the data provider by asking them to make what is often quite a small change seems to be present in many organisations. It frustrates the **** out of me.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 31, 2020 at 3:36 pm
Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more. It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column. I exported it to csv and then imported it to SQL server and that solved my problem. I was then able to remove the prefix.
Thanks all for trying to help me with this.
So the data provider is sending you the Excel spreadsheet where the data is actually good and it's your export from Excel to a CSV that's messing it up? We need to show you how to import the data directly from the spreadsheet as character based data. Either that or how to convert the offending column to actual text for the export.
--Jeff Moden
January 31, 2020 at 3:39 pm
MVDBA (Mike Vessey) wrote:sigh... my heart dies a little bit when we ignore the option of getting a provider of data to make it useable , I know they are often "paying customers", but the cost of dev time in building import solutions (or maintaining them) can outweigh the money the customer pays.
GIGO - garbage in, garbage out - first rule of importing files
I'm right with you on this. The fear of somehow offending the data provider by asking them to make what is often quite a small change seems to be present in many organisations. It frustrates the **** out of me.
From what the OP stated, I'm thinking there's no problem with the original data. I'm thinking it's actually a PEBCAC problem with how the OP is exporting the data from the Excel spreadsheet to the CSV file.
--Jeff Moden
January 31, 2020 at 3:40 pm
JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset
MVDBA
January 31, 2020 at 3:42 pm
GrassHopper wrote:Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more. It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column. I exported it to csv and then imported it to SQL server and that solved my problem. I was then able to remove the prefix.
Thanks all for trying to help me with this.
So the data provider is sending you the Excel spreadsheet where the data is actually good and it's your export from Excel to a CSV that's messing it up? We need to show you how to import the data directly from the spreadsheet as character based data. Either that or how to convert the offending column to actual text for the export.
By the time it gets into Excel, it may already be too late – because of Excel being 'helpful'. Trying to fix Excel data issues is sometimes as easy as unscrambling an egg.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 31, 2020 at 3:47 pm
JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset
Jeff's blood pressure just went up a notch 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 31, 2020 at 3:52 pm
PEBCAC problem with how the OP is exporting the data from the Excel spreadsheet to the CSV file.
PBCAC - I have to use that phrase daily - multiple times - still none of my project managers have figured it out
"it's a pbcac issue I might need 5 days to fix it"- I love pbcac!!! 🙂
and jeff, stop being mean.. grasshopper can you post an attachement of maybe the first few rows of a typical import in the format that they send it to you... no modifications or format changes.
one of us blundering idiots might give you a nice script that helps out (phil you aren't included in the idiot bit)
MVDBA
January 31, 2020 at 3:56 pm
MVDBA (Mike Vessey) wrote:JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset
Jeff's blood pressure just went up a notch 🙂
wait until Joe gets in on this
I want to see how high we can get that pressure up - in the mean time , lets help this fella out
MVDBA
January 31, 2020 at 4:10 pm
Phil Parkin wrote:MVDBA (Mike Vessey) wrote:JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset
Jeff's blood pressure just went up a notch 🙂
wait until Joe gets in on this
I want to see how high we can get that pressure up - in the mean time , lets help this fella out
If OP used punched cards he wouldn't be having all this trouble.
January 31, 2020 at 4:18 pm
MVDBA (Mike Vessey) wrote:Phil Parkin wrote:MVDBA (Mike Vessey) wrote:JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset
Jeff's blood pressure just went up a notch 🙂
wait until Joe gets in on this
I want to see how high we can get that pressure up - in the mean time , lets help this fella out
If OP used punched cards he wouldn't be having all this trouble.
It's ok - jeff reads punchcards like he is a blind man reading brail- he can do it. it's Kimball tags he fails with
MVDBA
February 4, 2020 at 2:25 am
Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more. It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column. I exported it to csv and then imported it to SQL server and that solved my problem. I was then able to remove the prefix.
Thanks all for trying to help me with this.
There's a way to read spreadsheets directly from T-SQL. It will require the use of an additional "provider" from Microsoft (lovingly referred to as the "Ace Driver") and the use of OPENROWSET. If you're allowed to install the driver and use OPENROWSET, let me know because I believe we can pull off a small miracle for you without you having to open spreadsheets and export them.
--Jeff Moden
February 4, 2020 at 2:41 am
x wrote:MVDBA (Mike Vessey) wrote:Phil Parkin wrote:MVDBA (Mike Vessey) wrote:JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset
Jeff's blood pressure just went up a notch 🙂
wait until Joe gets in on this
I want to see how high we can get that pressure up - in the mean time , lets help this fella out
If OP used punched cards he wouldn't be having all this trouble.
It's ok - jeff reads punchcards like he is a blind man reading brail- he can do it. it's Kimball tags he fails with
Heh... and I've found a lucrative side business... I sell the chad from the punchcards to the Broward County Canvassing Board in Florida. 😀
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply