Roxanne25
asked on
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",
"00100363","","26895","A",
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.
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",
"00100363","","26895","A",
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.
ASKER
So, will SSIS then know how to parse the file correctly without me having to do a bunch of trial and error formatting things?
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
.... Well if it was simple then I wouldn't have said what I said in my original post ... that isn't working.
ASKER
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
"data","data","data","data
...etc
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
"data","data","data","data
...etc
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.
>>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.
ASKER
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.
ASKER
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?
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?
As I said SSIS is very picky. So is there any chance you can post some sample data that fails?
ASKER
I have nothing to open the file with to provide a sample as its too large and its data that cannot be shared.
Then I am afraid, I cannot help you any further. I trust someone can step up to the plate.
ASKER
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was not the solution to my problem ultimately but this solution did help with my initial problem of the header row.
>>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.