Problems with Flat File...

Hi,

We have a flat file that in sql 2000 we had to use a VB program to massage the data in a flat file and spit it back out again in a readable format to then import into our database.  In 2005, the process no longer works and I was hoping SQL would be able to read the file as is without having to run it through a rince cycle...

However, my efforts so far have been futile.

When I start the import wizard and select flat file source... the first warning message it gives me after I select the file is "Columns are not defined for this connection manager."

It defaults to 1252 (ANSI - Latin I) and delimited with <none> as text qualifier and [cr][lf] as header row delimiter.  If I then go into the "columns" section and select comma as my column delimeter, it still has all the data in Column 0.

So, I go back to the general tab and change the text qualifier to " because it has quotations around every data point.  If I click on the "columns" section again it then yells at me saying:  "The preview sample contains embedded text qualifiers (").  The flat file parser does not support embedding text qualifiers in data.  Parsing columns that contain data with text qualifiers will fail at run time."  Then the column delimeter text box is greyed out.
Data is still all in column 0 however the quotation marks around the first data point are gone and it now looks like 00100036t3"," .....

I'm totally stumped here.  Nothing I do seems to get this thing to recognize the columns in the data file.  

I'm not sure of where this file is originating from.... so I cannot give a history as to that... I can only show you what the file looks like:

The first row has this:
BOF BPN COMPLETE 20100401 004702 0000060 00000000 V4082

Second row starts the data:
"00100363","","26895","A","12142001","12162010","First Name","Last Name","http://www.myweb.com","2L","MA","USA","0003","!End"
"00100363","","26895","A","12142001","12162010","First Name","Last Name","http://www.myweb.com","2L","MA","USA","0003","!End"

Anybody seen anything like this before?  Any thoughts?  The file is huge so the only view I am getting to what the data looks like is from the preview in sql server.

Thanks.


Roxanne25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
You will have usis SSIS to do a conditional split to create 2 separate files.  You can then import them separately.

>>Anybody seen anything like this before?<<
We receive hundreds of files like these every day.  It this case it is just a simple Header and detail rows.  We have some that have dozens of different record types within the same text file.
0
Roxanne25Author Commented:
So, will SSIS then know how to parse the file correctly without me having to do a bunch of trial and error formatting things?
0
Anthony PerkinsCommented:
I could not say for sure, without seeing the whole file.  What I can tell you is that for performance reasons SSIS is far more strict than DTS.  So if you are missing any delimiter, it will fail.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Roxanne25Author Commented:
Well the issue here isn't performance... I cannot even get it to the point where it starts to run the import.  My problem is in the formatting step and getting it to recognize the data file that I'm providing.  IE:  the delimeters, the text qualifiers and the error messages that I'm receiving in the import wizard.
0
Anthony PerkinsCommented:
It looks pretty straight forward to me:

Format: Delimited
Text Qualifier: Double Quotes
Header rows to skip: 1

Row Delimiter = {CR}{LF}
Column Delimiter = Comma {,}
0
Roxanne25Author Commented:
.... Well if it was simple then I wouldn't have said what I said in my original post ... that isn't working.
0
Roxanne25Author Commented:
I mucked around with it again and I got a little farther...
I changed the header row delimiter to {LF} and to skip row 1 and put the text qualifier to "

When I click on the columns tab I'm now seeing lots of columns but its not doing the row terminator right... and I'm still getting this error:

"The preview sample contains embedded text qualifiers (").  The flat file parser does not support embedding text qualifiers in data.  Parsing columns that contain data with text qualifiers will fail at run time."  

Its basically returning about 246 columns and then column 247 has like an entire record in it complete with commas and all...

So, it seems its not picking up the row terminator properly.. I've tried changing it around but it just always says it can't find it.  

the !end that they have at the end of every record seems a bit wierd to me as well...
it shows up like this:

"data","data","data","data","!end"
"data","data","data","data","!end"
...etc
0
Anthony PerkinsCommented:
Roxanne25,

>>Well if it was simple then I wouldn't have said what I said in my original post ... <<
Please bare with me.  I am not in front of your computer, I do not have access to your data.  All I can go by is what you have posted.
I took the time to copy your sample to a file and 2 minutes later had both rows imported. That is why I said it was simple.  Given the facts as stated that is simple.

>>that isn't working.<<
I am sorry, but this comment is meaningless.

So you have a choice, upload some sample data that does "not work" and we can see if there is any formatting problem that would prevent SSIS from importing it.

Again, let me re-state, SSIS is far more strict than DTS, if you are missing a single text qualifier, you are SOL.
0
Roxanne25Author Commented:
See if what I posted in my last message gives any thoughts on where to go from here... and I dunno if it makes a difference or not but I'm just trying to do this from the import data wizard in management studio...  I have not attempted to do this from visual studio in an SSIS package.
0
Roxanne25Author Commented:
I got it to recognize the formatting... but I cannot get it into a table.... grrrrrrr....

It says:

- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 27" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task: The "output column "Column 27" (118)" failed because truncation occurred, and the truncation row disposition on "output column "Column 27" (118)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)

I have made all the fields in the table varchar(max) just to see if it would go... and I still get this error... I guess that means its not reading the rows correctly still?
0
Anthony PerkinsCommented:
As I said SSIS is very picky.  So is there any chance you can post some sample data that fails?
0
Roxanne25Author Commented:
I have nothing to open the file with to provide a sample as its too large and its data that cannot be shared.
0
Anthony PerkinsCommented:
Then I am afraid, I cannot help you any further.  I trust someone can step up to the plate.
0
Roxanne25Author Commented:
Ok... I'm banging my head against the wall... attached is a file snipit... has about 1000 records in it.  It should have 240 columns.  

Everytime I get to the import stage it errors out saying:

Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 27" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task: The "output column "Column 27" (118)" failed because truncation occurred, and the truncation row disposition on "output column "Column 27" (118)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)

When I look at the data in notepad and in the preview it looks just fine.  I can't figure out what the hell would be causing the truncation error when I have all 240 fields set to varchar(max).
0
Roxanne25Author Commented:
Ok, I was able to get it fixed... the problem was in the "advanced" tab of the import wizard...

Even though I had set the data types in the table I was trying to import into the wizard itself was still coming up with its own datatypes and lengths for the fields... so, it was setting everything to a length of 50 ...

After messing around with it and sorting out the errors that it had with each particular field, I was able to get the file to load. :)  

It varied between 2 errors:  1 for data would be truncated which was it trying to set the field to a length of 50 and the other was where it was trying to say the field was numeric when it needed to be string.

Thanks for trying to help :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roxanne25Author Commented:
This was not the solution to my problem ultimately but this solution did help with my initial problem of the header row.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.