Importing text file via 2005 Import Wizard

Question

Post reply

Importing text file via 2005 Import Wizard

Deb Anderson

SSCrazy

Points: 2116

March 30, 2007 at 1:19 pm

#123265

My text file looks something like this ... only bigger:

H,00DI0,A703D-CHAPMAN,20070328,0000144305,253.02,0,0,,

D,0.12,510010,HI900,,,NC5 AKA_DBA Records,,,,

D,102.90,510010,HI900,,,NC5 Civil Records

D,145.00,500959,HI840,,,NC5 CDV Standards

D,5.00,500959,HI840,,,NC5 CDV's

When I import it via 2005 Import Wizard, I get a 10 column 3 row table that looks like this: (same results using Integrations services Bulk Insert task)

H	00DI0	A703D-CHAPMAN	20070328	0000144305	253.02	0	0	NULL	NULL
D	0.12	510010	HI900	NULL	NULL	NC5 AKA_DBA Records D	102.90	510010	HI900,,,NC5 Civil Records
D	145.00	500959	HI840	NULL	NULL	NC5 CDV Standard D	5.00	500959	HI840,,,NC5 CDV's

When I import it via 2000 Enterprise Mgr Import Data, I get a 10 column 5 row table that looks like this: (which is correct)

H	00DI0	A703D-CHAPMAN	20070328	0000144305	253.02	0	0	NULL	NULL
D	0.12	510010	HI900	NULL	NULL	NC5 AKA_DBA Records	NULL	NULL	NULL
D	102.90	510010	HI900	NULL	NULL	NC5 Civil Records	NULL	NULL	NULL
D	145.00	500959	HI840	NULL	NULL	NC5 CDV Standard	NULL	NULL	NULL
D	5.00	500959	HI840	NULL	NULL	NC5 CDV's	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Does anyone have an explanation and a work around (other than modifying the file)? I am running 2005 sp2.

I appreciate the input.

😎

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQL51F1 SSCommitted Points: 1959 More actions · Answer 1

Sorry - I cannot explain why one brings in 3 rows and one brings in 5. The only thing I can do is sympathise as I was doing exactly the same thing last week with data unloaded from DB2.

Since I needed it in 2005, going through DTS wasn't an option - but I tried this anyway and it just made me say "stuff it" and go back to my original plan - via T-SQL.

Since the flat file I was importing was fixed width with no delimiters, this made it easier in one respect but more difficult in another as DB2 was sending integers out into a 10-char-wide field with an additional flag after that to indicate that the field was an integer.

I thought of BCP but how to get a 10+1 char field down to an integer was going to be the sticking point.

The easiest and quickest way I found to do this (as a starting point before going for utmost efficiency) was to use 2 temp tables and the OPENROWSET command and making use of a format file. The first one at the same row length as the data coming in and the 2nd with the columns matching that of the actual table structure but all char fields. The format file would be for a single column the width of the row so that the data can be brought in - then you can do the data manipulation once you get it there.

Once you get it working, you can then play around with it to skip the single-column temp table (and subsequent use of tempDB) and import into similarly-structured temp table to manipulate data to insert into real table.

Maybe not the best option but it works!

(going to change my script around to use BULK INSERT rather than OPENROWSET in an effort to import in batches and to try and reduce logging).

A lack of planning on your part does not constitute an emergency on mine.

Mary Mathias SSC Enthusiast Points: 188 More actions · Answer 2

SQL Server 2005 does not recognize a carriage return/line feed anymore for delimited text files.

Only the delimited text files...fixed width functions is not affected.

I can send the trouble ticket info later this week if you wish, but the jist of it is that, per Microsoft, you must use a legacy SQL 2000 DTS package (and modify it to suit your needs) to import these types of files without errors.

Our answer was to just stand up one SQL Server 2000 box and use it to import our clients' files. A rather rude situation in an all SQL Server 2005 shop...

You can try this if you wish...here is a similar test file that I sent to Microsoft...First line is the headers...

testCol1, testCol2, testCol3, testCol4, testCol5

test1, test2, test3, test4, test5

test1, test2, test3

test1, test2, test3, test4

test1, test2, test3, test4, test5

save it as a .txt file and play with it in SSIS or the SQL Server 2005 wizards. Yeah...it's a Feature....

MJM

Deb Anderson SSCrazy Points: 2116 More actions · Answer 3

NEAT! Yes, I'd like to see the info from your ticket.

Thanks for both responses. I guess we'll be keeping 2000 around for awhile then.

😎

SQL51F1 SSCommitted Points: 1959 More actions · Answer 4

I thought I'd better add this bit on....

I discovered yesterday that after the mucking around that I put into loading the data (I was using a non-XML "solution") a bloke I work with went off and resolved the issue using a customised XML format file and the job works much more efficiently using BCP.

So... as for using a wizard, I can't help you but it is easily achievable using text & XML format files with BCP. Bulk Import and OPENROWSET methods.

A lack of planning on your part does not constitute an emergency on mine.

Mike Voissem SSC-Addicted Points: 435 More actions · Answer 5

I had a similar experience, and when I saved the package from the wizard, and changed the source file input to be ragged right, it worked fine. This was from a file that was downloaded from our mainframe and had variable length fields.

This may or may not help to resolve. I am interested in the customized XML format file and how that works, if you could expand on that some more Matt.

Thanx,

Mike

SQL51F1 SSCommitted Points: 1959 More actions · Answer 6

Well... I am far from being an authority on the subject but people on this list have helped me and it's time to return the favour (partly )

The blessing that I had with the data from DB2 was that it was all fixed length - the issue was with the fields that are defined in DB2 as INTEGERs and then in SQL Server as SQLINT & SQLSMALLINT (see the lower half of the XML format file below).

When DB2 extracted the data, the data can be brought out as a flat file with all char fields of fixed lengths BUT integers are extracted as a 10-character field with an additional character to indicate that the field is an integer. The bloke who adapted the XML below extracted the integer fields from DB2 as straight 10-char fields with leading zeros thereby eliminating the additional placeholder in the data file (something I couldn't do as I've not logged into DB2 for about 5-6 years now and no longer have access).

As you would be aware, SQL Server sizing for an INT is 4 bytes and, no matter how you work it, a 10-char field cannot automagically be transformed into a 4-byte field. Same with the 5-char field (for postcode) becoming a 2-byte SMALLINT (I think that's right on the sizing....).

The simplest way to look at the XML is that the top load of gobbledegook, or rather, the "GI" half of the GIGO acronym that I use as a DBA (GIGO = Garbage In Garbage Out) indicates the place-holders for the input file. The lower half ("GO") is where it's going. BCP seemingly handles the conversion of leading-zero char fields to be integers & smallints itself without any mucking around from you.

Personally though, when working with straight DB2 unloads that contain the additional fields to indicate integer fields or nullable columns, I've found that the normal flat format file works better so that you can filter out the extra information that is not needed nor wanted.

I can include the (very basic) code for importing using a flat format file to a temp table and then performing data conversion to INSERT into actual table. Nothing fantastic here in the code but it may save someone a few minutes....

<?xml version="1.0" ?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

A lack of planning on your part does not constitute an emergency on mine.