Stripping out double quotes (") in bulk insert

Question

Post reply

Stripping out double quotes (") in bulk insert

Viewing 15 posts - 16 through 30 (of 58 total)

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

Emmanouil Karaiskakis Ten Centuries Points: 1013 More actions · Answer 1

hehehehehehe, I like your thinking mate 🙂

Yes I am actually,I used it recently on a different program.

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

Ok, thanks for the info. I'll see what I can do tonight after work.

--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)

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

Hi guys,

Have I missed something? Is using DTS or SSIS out of the question? :ermm:

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

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

Certainly not... but I don't use DTS or SSIS for several reasons... wanna give it a try for us? Because I don't use it, I wouldn't know where to start with either of those two especially converting the word "NULL " to an actual null.

I'll take a slightly different approach tonight...

--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)

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 5

Ok, cool.... A simple data transformation task between the source file and the destination table would remove the trailing spaces in the fields.

In English, the DTS package (attached as .zip file as .dts files are not acceptable uploads :crazy: ) transfers the data from the source file to the target table, substitutes NULL for 'NULL' and empty cell values.

To set up the DTS package on your server:

1. Save the File

1.0 Save the .dts file to a directory accessible by the SQL Server.

2. Open the package

2.1 In Enterprise Mangler, right-click on Data Transformation Services.

2.2 Click on Open Package.

2.3 Navigate to saved .dts file.

2.4 Click Open

2.5 Click on ssc_example_002

2.6 Click OK.

This will open the package in DTS designer. I have annotated the package with the step by step instructions required. You can then save the package to your server by:

3. Save the package to the server

3.1 Click on Package on the Menu bar.

3.2 Click on Save as...

3.3 Give the package a useful package name.

3.4 Change the location to SQL Server.

3.5 Choose the server to save it to (don't forget security info if required)

You should then be able to run the package; you can use the green play button in the package designer to run it.

If you get any trouble with it just give us a shout.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

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

Very cool... with your great instructions, I'll just bet I learn something new! Thanks, Adrian...

I'll bet the OP will like it, as well!

I'll still take a brute force whack at it tonight... Gotta do that for myself, anyway... maybe I've been wrong about DTS.

--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)

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 7

I suppose it depends on the individual; for some reason I have a penchant for DTS and SSIS. Although I also enjoy seeing if I can get a T-SQL based solution for scripts that I write in my DTS ActiveX tasks....

Maybe I need to get out more?! 😛

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Emmanouil Karaiskakis Ten Centuries Points: 1013 More actions · Answer 8

Hey guys,

I don't know what to say? Thanks a lot for all the support!!! I'm overwhelmed!!!

Just to let you know, I won't be using DTS/SSIS packages for this scenario.

One of the reasons is that I have already started a procedure which can currently import comma delimited, fixed length, and tab delimited files using Bulk insert and Format files!

Great instructions though...! Bravo!!! 😉

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 9

Hi,

I noticed that on your example of your Format file you have 59 columns defined whereas the file you are importing only has 56 columns. The last line in your format file should therefore be:

56 SQLCHAR 0 10 "\r" 56 P2_EndDateAtAddress ...

I have attached a zip file that contains 3 files - a sample input from the data supplied, a format file containing the information for the 56 columns(watch out for my collation, you may need to change this) and a T-SQL script file. The T-SQL recreates the JobTable table and utilises BULK INSERT with the format file to insert the data in to the table. There are then UPDATE statements (that I took from the previous DTS package) to update 'NULL' and empty string values to actual NULL values.

Hope this helps,

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 10

I obviously need more coffee... Adrian, who was that last post of yours directed to?

--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)

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 11

Emmanouil Karaiskakis (1/24/2008)
Hey guys,
I don't know what to say? Thanks a lot for all the support!!! I'm overwhelmed!!!
Just to let you know, I won't be using DTS/SSIS packages for this scenario.
One of the reasons is that I have already started a procedure which can currently import comma delimited, fixed length, and tab delimited files using Bulk insert and Format files!
Great instructions though...! Bravo!!! 😉

So, does that mean you're all set or do you still need help on this?

--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)

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 12

Aplogies, that was for Emmanouil.

More coffee? he he he... Being in the UK and being of a slightly insomniac disposition means I get a head start! 😀

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Emmanouil Karaiskakis Ten Centuries Points: 1013 More actions · Answer 13

Hi,

That was way more stuff than I need hehe! You really work hard in here! Very impressed.

I only needed a format file sample. The format file is very similar to my comma delimited format file...!

Does that mean that the bulk insert ignores all spaces between the right of the last letter and the comma?

The reason I am asking is that, as said earlier, the structure of this crappy data is like this:

imagine that zero is space (the forum get rid of spaces lol)

field1000000000000,field2000000000000,NULL,field3000000000000,field4000000000000

Will the format file you gave me deal with all those spaces?

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 14

The format file should ignore any spaces that are over the specified length; sadly I don't think that it'll strip out spaces such as (using the 0s for spaces again and a string length of six) :

Samuel000 = Samuel but Jane00000 would be Jane00

You're table definition has the columns as CHAR which would pad the result with spaces anyway. If you change the CHAR columns to VARCHAR this would automatically rid all of the extra spaces on import.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Emmanouil Karaiskakis Ten Centuries Points: 1013 More actions · Answer 15

there must be something wrong with the data then because it doesnt work...hehe

nevertheless I'll work it out. thank you for all this information. I really appreciate it 🙂