Need some eyes on a SP I'm working on

Question

Need some eyes on a SP I'm working on

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

sqldriver SSChampion Points: 10440 More actions · Answer 1

Jeff Moden (6/25/2013)
I'm using a Schema.ini file because when I load these files without one, the data becomes garbled.
"Load these files" how and where? I'm asking because one of the biggest problems you're having seems to be with the creation of the Schema.Ini file for each file. If you're using something like BULK INSERT, the Schema.Ini file isn't necessary. You can just change to a "raw" type file setting.
Also, you didn't answer my question. Why can't we move "all" the files at once and send a single email with the list of files moved?

Sorry, when I referred to "these" files, I meant the test files they've been sending. There will only be one file to move at a time when their process is finalized. So one file, every Monday (morning hopefully).

I tried with bulk insert, and the data did not pipe out to columns correctly. I'm using a Schema.ini because it was more straight forward to create for me than a .fmt file.

The files they're sending are being created overseas, so I haven't gotten a straight answer about how they're being created. They're supposed to arrive to me Unicode, tab-delimited, but there's something weird about them, and the extension is just being changed to .txt from whatever it is originally. It took three weeks for them to stop sending me UTF-8 files (I know, code page 65001).

So, I ended up using openrowset with a Schema.ini that runs essentially like this, but within a SP:

select * into sample.dbo.[eriktest]

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Text;Database=\\server\Sample\;

FORMATFILE=\\server\Sample\Schema.ini',

'SELECT * FROM [file.txt]')

Eventually it will be going to a real table when they've finalized the layout.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Ah. Understood.

If the data isn't sensitive, could you attach one of the test files so we can see if there's a better way? If it is sensitive, could you doctor up a version that I could try for you? It would also be handy if you posted the CREATE TABLE script for the final destination table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 3

I don't even have what the final version of these files will be yet. So, no table creation data either. I'd hesitate to waste any of your time on "evolving" test data.

This is a result of some SAP compliance, so about all that's clear is that they're totally annihilating the old layout and format. There are probably a few more hour long phone calls trying to get them to explain any mapping of new columns to old columns in my near future, as things change.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

Understood but you have at least one file that made you think you need a Schema.ini file. If we solve that, then it'll be one less thing to worry about when the real data shows up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 5

I would be okay with messaging you a DB link to some dummy-ish data, but I don't think I'd want any of it out in the wild. Let me know if that works for you.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

erikd (6/25/2013)
I would be okay with messaging you a DB link to some dummy-ish data, but I don't think I'd want any of it out in the wild. Let me know if that works for you.

Understood. That'll work. If I can hammer through it, then I'll post a solution with some totally bogus test data and bogus test table, if that's alright.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 7

sqldriver

SSChampion

Points: 10440

June 27, 2013 at 7:30 am

#1627961

Totally fine with me. Link is sent.

Thanks